Order Of Tables In Joins
Jul 20, 2005
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
inner join c
on c.id1=a.id1
and c.id2=a.id2
inner join d
on d.id1=a.id1
and d.id2=a.id2
Any help is appreciated.
Thanks
Sri
View 4 Replies
ADVERTISEMENT
Aug 11, 2005
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
View 3 Replies
View Related
Jan 5, 2007
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
View 2 Replies
View Related
Jun 2, 2008
Hi -
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.
Thanks -
View 8 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Sep 29, 2015
Will the order of inner joins and cross apply effect the results of a query?
Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c
[Code] ....
I want to change the position of doing "CROSS APPLY". Will it effects query results?
View 2 Replies
View Related
Jun 19, 2014
Table CYCLE has two columns
id
name
Table TEST has two columns
id
name
Table TESTCYCL has three columns
id
cycle_id (reference to id field in CYCLE table)
test_id (reference to id field in TEST table)
I need SQL to get CYCLE.name and TEST.name. If there is no associated row in the TEST table, I still want the CYCLE.name.
SELECT CYCLE.name, TEST.name
FROM CYCLE
JOIN TESTCYCL ON TESTCYCL.cycle_id = CYCLE.id
JOIN TEST ON TEST.id = TESTCYCLE.test_id
This only returns rows from CYCLE where there is a related row in TEST. I need all rows from CYCLE whether there is a row in TEST or not.
View 8 Replies
View Related
Dec 21, 2005
Imran writes "Hello there
This is Imran, will pray for you if u help me out with this problem
I have 2 tables
Developments
------------
->DevelopmentId - AutoNum
ProjectName
Development_Pictures
--------------------
->Auto
DevelopmentId
PicName
PicType
The two tables are linked by DevelopmentId. Each record in developments can have many related records in Development_Pictures.
Now i want all records from Developments
along with the PicName. The thing is that only to show
picName where the PicType is 'Big'
ProjectName, PicName
Thank You
where the "
View 3 Replies
View Related
May 14, 2007
Hi,
How can I do this using INNER JOIN:
4.From tables RubricReport, RubricReportTemplate, RubricReportDetail and SppTarget, get columns:
a.RubricReport
i.ReportID
ii.LastUpdate
iii.LastUpdateBy
b.RubricReportTemplate
i.IndicatorNumber
ii.Topic
iii.Part
iv.ILCDComponent
c.RubricReportDetail
i.LocalPerf
ii.GoalMet
d.SppTarget
i.Target
5.Matching these columns in the above four tables:
a.RubricReport.ReportID=RubricReportDetail.ReportID
b.RubricReportDetail.IndicatorID=RubricReportTemplate.IndicatorID
c.SppTarget.IndicatorNumber=RubricReportDetail.IndicatorNumber
d.SppTarget.Years=@DataYears
I have looked a lot but couldn't find INNER join for three tables.
View 17 Replies
View Related
Aug 3, 2007
Hi All,
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 !!!!
Thanks in advance
Matt
View 8 Replies
View Related
Jun 14, 2001
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.
View 2 Replies
View Related
Dec 7, 2013
Table 1:
id amount
1 100
2 200
3 300
4 400
Table 2:
id amount
1 100
1 100
2 200
3 300
4 null
Table 3:
id amount
1 null
2 200
2 200
3 300
3 200
4 null
id is common for each tables , how can i get output like this:
Collapse | Copy Code
id t1 t2 t3
1 100 200 null
2 200 200 200
3 300 300 500
4 400 null null
I am stuck with this query .
View 1 Replies
View Related
Feb 4, 2015
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?
View 1 Replies
View Related
Oct 30, 2006
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..
View 3 Replies
View Related
Jul 20, 2005
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
View 6 Replies
View Related
Sep 26, 2006
I have an stp where I want to return a Recordset via a SELECT that joins 3 temp tables...
Here's what the temp tables look like (I am being brief)...
CREATE TABLE #Employees (EmpID INTEGER, EmpName NVARCHAR(40))
CREATE TABLE #PayPeriods (PayPeriodIndex INTEGER, StartDate DATETIME, EndDate DATETIME)
CREATE TABLE #Statistics (EmpID INTEGER, PayPeriodIndex INTEGER, HoursWorked REAL)
The #Employees table is populated for all employees.
The #PayPeriods table is populated for each calandar week of the year (PayPeriodIndex=0 to 51).
The #Statistics table is populated from data within another permanent table.
Some employees do not work all 52 weeks.
Some employees do not work any of the 52 weeks.
So, the #Statistics table doesn't have all possible combinations of Employees and PayPeriods.
I want to return a Recordset for all possible combinations of Employees and PayPeriods...
Here's a SELECT that I have used...
SELECT e.EmpId, e.Name, pp.PayPeriodIndex, ISNULL(s.HoursWorked,0)
FROM #Statistics s
LEFT OUTER JOIN #Employees e....
LEFT OUTER JOIN #PayPeriods pp ....
WHERE s.EmpId = e.EmpId
AND s.PayPeriodIndex = pp.PayPeriodIndex
I have had no luck with this SELECT.
Can anybody help???
TIA
View 4 Replies
View Related
Nov 12, 2015
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).
Is it possible I don't have permissions?
View 2 Replies
View Related
Jan 22, 2014
Assume i have 3 tables
Person(personname,age)
Children(childname,personname)
car(carname,personname)
A persone can have multiple cars
A person can have multiple children
Its not possible to display the results in SQL rows and columns.
If i try to it will give
PersonName Carname Childname
Sachin Audi C1
Sachin Maruti C1
Sachin Audi C2
Sachin Maruti C2
Instead of writing seperate queries the application wants to receive an xml output as follows
<person>
<pname>sachin</pname>
<car>audi</car>
<car>bmw</car>
<cname>c1</cname>
<cname>c2</cname>
<person>
How to get this output ?
View 1 Replies
View Related
Mar 15, 2006
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
View 4 Replies
View Related
Sep 14, 2015
I have 3 tables.
Table 1:
ID Name Description
1 ABc xyz
2 ABC XYZ
Table 2:
RoleID Role
1 Admin
2 QA
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.
View 4 Replies
View Related
Aug 25, 2005
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
View 6 Replies
View Related
Feb 10, 2015
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
[Code] ....
View 1 Replies
View Related
Nov 8, 2005
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
View 7 Replies
View Related
Apr 14, 2008
I'm using VBE 2008 and in the edit table schema box it doesn't allow reordering the columns. Any solutions?
View 1 Replies
View Related
Nov 3, 2000
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.
Any ideas on the principles involved here.
View 1 Replies
View Related
Oct 25, 2001
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
ID
1
2
3
4
5
ECT
PLEASE HELP
THANKS
View 2 Replies
View Related
Feb 7, 2008
Hi,
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....
DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)
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?
Many thanks for any help! :)
View 10 Replies
View Related
Oct 25, 2007
Hi!
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
View 4 Replies
View Related
Aug 6, 2015
CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')
CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')
My task is to find matches between these two tables on "name."
View 1 Replies
View Related
Nov 29, 2007
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.
Thanks!
CB
View 1 Replies
View Related
Aug 15, 2007
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?
Thanks
Mike
View 12 Replies
View Related
Jul 22, 2015
How to Change Order of Column In Database Tables
View 10 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related