I am writing a download process in which i have a condition where i
need to join four tables. Each table have lot of data say around
300000 recs.
my question is when i am doing the joins on the columns is there any
specific order i need to follow.
for example
My original query looks like this
select a.col1,a.col2
from a
inner join b
on a.id1=b.id1
and a.id2=b.id2
inner join c
on c.id1=b.id1
and c.id2=b.id2
inner join d
on d.id1=c.id1
and d.id2=c.id2
If i change the query like below... does it make any difference
select a.col1,a.col2
from a
inner join b
on b.id1=a.id1
and b.id2=a.id2
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
i have below sql with several joins. Under sql 2000 should it be assumed that sql will create the best plan for however you set the joins or is there an order when using left joins to follow
select p.account_id, p.sex, p.other_id_number, ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name, pe.create_timestamp as enc_date, loc.location_name, l.ngn_status, l.sign_off_person, l.sign_off_date, ltrim(rtrim(upper(pr.first_name))) + ' ' + ltrim(rtrim(upper(pr.last_name))) as signoff_name, ltrim(rtrim(upper(prv.first_name))) + ' ' + ltrim(rtrim(upper(prv.last_name))) as prov_name, r.req_accession, l.ufo_num, r.spec_rcv_date_time, r.date_time_reported, r.test_desc, x.abnorm_flags, x.obs_id, x.result_desc, x.ref_range, x.units, x.observ_value, c.comment_text from patient p inner join patient_ p1 on p1.account_id = p.account_id inner join patient_encounter pe on pe.account_id = p.account_id inner join location loc on loc.location_key = pe.location_id inner join lab_nor l on l.enc_id = pe.enc_id inner join provider prv on prv.provider_id = l.ordering_provider left outer join profile pr on pr.user_id = l.sign_off_person inner join p_lab_results_obr r on r.ngn_order_num = l.order_num inner join i_lab_results_obx x on x.unique_obr_num = r.unique_obr_num left outer join i_lab_results_comm c on c.unique_obr_num = r.unique_obr_num and c.obr_seq_num = r.seq_num and c.obx_seq_num = x.obx_seq_num
I have a general question about the order of joins. I have noticed that in some of the queries I am looking at, there are lots of LEFT and RIGHT Joins one after the other. My question is which table is considered the LEFT or RIGHT or original table if there are 9 or so joins after it?
For example, if a query says select ORD.orderID, ORD.CustomerID, EMP.employeeID, SOX.AugitID FROM Dbo.employees EMP INNER JOIN dbo.orders ORD On emp.employeeid=ORD.employeeid LEFT OUTER JOIN dbo.Customers CUST ON CUST.CustomerID=ORD.CustomerID RIGHT OUTER JOIN SOX.Audit.ID ON ORD.OrderID = SOX.Audit.ID
the query INNER JOINS Employee and Orders table, but for the LEFT and RIGHT joins, is the Original FROM dbo.Employees EMP always considered the original table from which a LEFT JOIN is done, and if so is the RIGHT OUTER JOIN, the SOX table, taking the JOIN on the original FROM Table FROM Dbo.employees EMP, or from the table preceding the RIGHT OUTER JOIN, the Customers table. This is a simple example, but these queries have one table after another for about 9 - 15 joins (RIGHT, LEFT, etc.). Whatever applies to the simple query above, I will take to the more complex ones.
Does the order in which Joins are peformed (from left to right) matterwhether inner or outer when it comes to performance. Assuming that allindexes are the same but the size of the tables is different?For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3,TABLE4, TABLE5.For example:SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1INNER JOIN TABLE3 on Condition2LEFT JOIN TABLE4 on Condition3INNER JOIN TABLE5 on Condition4.Does SQL optimize the query and finds the best way to inner join or itstarts doing the JOINS from left to right?Thank you for your your feedback.Gent
I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables
Main table
Label table
textbox table The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .
MAIN Table
pk Moduleid ItemName itemtype
36 372 test1 4
37 372 test2 4
38 372 test3 4
39 372 test4 6
40 372 test5 4
label
pk Main_fk labeltext
4 36 labeltext1
5 37 labeltext2
6 38 labeltext3
7 40 labeltext4
Textbox
pk Main_fk textboxtext
1 39 textbox1
I did infact manage to perform a join on these these tables.
Select * From tb_Main
inner join tb_Label
on tb_Main.pk = tb_Label.main_fk
where moduleID = @moduleID
Select * From tb_Main
inner join tb_textbox
on tb_Main.pk = tb_textbox.main_fk
where moduleID = @moduleID
The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.
Is what im asking possible? I would appreciate if some exmaples are posted
I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!
Is it possible to utilize more than two tables in a single outer join? I have one table that I want every row and 18 others where I only want an entry if one is present meeting the conditions of "1.customerid = 2.Customerid" etc. I haven't run across this before and would appreciate any help.
I have a general question concerning joins. Below is a table scenario:
SELECT * FROM TABLE_A T0 INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column] LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]
Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?
I had a SP to generate a recordset for a MIS report. It had 11 temp tables , basically taking data from real tables , performing some aggregation and counts and passin on the records to a temp table which gave the result as desired ..ofcourse with some group by clauses...
Now i replaced these temp tables (most of them..left with just 2), and used derieved tables instead in the final query..and joins which will execute with each query iteration..something of the sort
select col1,co2 ,
(select count(id) from sometable x group by sayaccount where x.id = temp.id) ,
(select sum(id) from (select count(id) from sometable group by sayaccount) DERIVED_Tab),
......
from #finaltemp temp
group by col1.....
(earlier the count was stored in 1 temp table, then sum one n stored in other).
the idea was to reduce the execution time...but i din achieve it...not with just a single user running the report i.e , rather it marginally increased. my thinking was that i'll be avoiding the locks on tempdb by reducing the number of temp tables....pls tell me if im goin wrong...i still have the option of using table datatype if thats feasible..
Hi, all:This is probably a simple problem, but, as an SQL newbie, I'm having alittle trouble understanding multi-joins and subqueries.I have the following tables and columns:MemberTable-----MemberID (primary key)MemberNameAddressCountryFoodsTable------FoodID (primary key)FoodNameMusicTable-----MusicID (primary key)MusicNameMoviesTable-----MoviesID (primary key)MoviesName....and their linking tables...Members2FoodsTable-----MemberID (foreign key)FoodsID (foreign key)Members2MoviesTable-----MemberID (foreign key)MoviesID (foreign key)....and so forth.Now what I'm trying to do is retrieve a specific MemberID, his address info(from the Members table), and get a listing of his favorite Movies, Foods,Music, etc. I know I probably need to JOIN tables, but where do I JOIN thetables? Do I have to JOIN the various Music/Foods/Movies tables or is itthe Music/Members2Music and Foods/Members2Foods, etc. tables? And I assumeI would probably need to perform a subquery somewhere?I realize I'll need to first filter the Members, Members2Music,Members2Foods, etc. tables by the MemberID, and afterwards, retrieve alisting of the actual Music/Foods/Movies names. I'm just confused how to dothat. (By the way, I have a total of 10 other tables or in addition toMusic, Foods, etc. so it's a lot of table JOINing.)If someone could please help me out with the actual SQL coding, I'd reallyappreciate it!Thanks for the help!J
I have to write a report using SSRS using a cube somebody (no longer here) created. I know SSRS and SQL very well, but have never worked with cubes.
I want to make sure the cube was properly constructed (in terms of table/join relationships). I cannot find the tables/joins. I looked in SSMS under Data Source Views and everything else I could think of (or google).
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN workingon a three table query.Assumptions:-- I have events in the Event table.-- Each event CAN have one Transaction, but it's not guaranteed-- Each transaction, ir present, will have one or more Amount recordsThis would be the pseudo-query without any special joins:-----------------------------------------SELECTa.Name,SUM( c.amount ) as TotalFROMEvent a,Transaction b,Amounts cWHEREa.EventID = b.EventIDANDb.TransID = c.TransID-----------------------------------------This is fine if there is a Transaction for the Event. But, if there'sno transaction for an event, no record is pulled of course.What I need is for a record to come back for each event regardless ofthe presence of a Transaction. If there's no transaction, then the"Total" column should be 0.How would I get an OUTER JOIN to work on this so that each Event gets arecord?TIA-BEP
Table 3: ID RoleID Time 1 1 09:14 2 1 09:15 1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden
This query works perfectly and orders by just as I need
Code: Select '1st' As [Type], #Uno.ID #Uno.Address, #Uno.shippingInfo FROM #Uno
[Code] ....
However, when I use it in a Union All so I can pull data from 2 diff tables, the order by statement no longer works. How can I order by data in 2 tables?
Code: Select '1st' As [Type], #Uno.ID #Uno.Address, #Uno.shippingInfo FROM #Uno
Greetings,I just wanna know if anyone can tell me how to get all user definedtables in parent-then-child manner. I mean all the parents should belisted first and then childs.I dont think there is any direct way to do this, but i am not able toform any sort of query to achieve this.Any help will be greatly appreciated.TIA
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
I have a problem with the order of the numeric ID in several of my tables in one database. Basically every quarter, using link table i update the figures in my table using basic cut and paste. However this will not work now as the table has become out of order due to the ID'S not beeing in numeric order.example ID 1 7 23 24 15 16 2 3 8 34
I am desperate to get these collums back into order so i can paste my data in how do i do this. i want it to be like this
I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....
INSERT INTO @Temp SELECT i.ID, i.Name, Type = 'I' FROM Item i UNION SELECT p.ID, p.Name, Type = 'P'FROM Package p
SELECT * FROM @Temp ORDER BY Name ASC
I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what from ( select date3, date2, date1, who, what from shows union select date3, date2, date1, who, what from shares union select date3, date2, date1, who, what from soiree ) a order by date3, date2, date1
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers.
The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made...
So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)?
I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct.
Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152".
FYI, I've lost the debate that such auditing is better handled by the application, not the database server...
Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation?
I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB