September 25, 2008 at 10:12 am
I have three tables and i do a left join in between table 1 and 2 to get all the data from table 1 and now i want to join table three and still get all the data form table 1. How do i do that.
I have done another left join and only matching data came thru.
Appreciate your help!
September 25, 2008 at 11:10 am
Please post what you've tried and some information about the tables.
September 25, 2008 at 12:16 pm
Did you check that only table 1 and 3 are mentioned in the join clause for table 3?
Select
Table1.*
,Table2.*
,Table3.*
From
Table1
Left Outer Join Table2 On
Table1.TableKey = Table2.TableKey
Left Outer Join Table3 On
Table1.TableKey = Table3.TableKey
making sure that table 1 is always on the left side of the = sign in the join clause.
September 25, 2008 at 5:27 pm
Table 1
=========
Order_NO ------- DesCription
------------------------------
Null ------------- ABC
Null ------------- ACC
K12 ------------- ACD
K13 ------------- ADE
K14 ------------- ART
Table 2
=========
Order_NO ---- Call_Ref
------------------------
K12 --------- 102
K13 --------- 103
K14 --------- 104
K15 --------- 105
K16 --------- 106
K17 ---------- 107
Table 3
=========
Call_Ref ----- Time
-------------------
103 --------- 10.30
104 --------- 10.45
105 --------- 11.00
106 --------- 12.00
107 --------- 13.00
What i want is everything from table 1 (only) and rest of the relavent fields against it
EX:--
Order_NO ------- DesCription--------Call_Ref----------Time
---------------------------------------------------------
Null ------------- ABC --------------Null-------------Null
Null ------------- ACC--------------Null-------------Null
K12 ------------- ACD--------------102-------------Null
K13 ------------- ADE--------------103-------------10.30
K14 ------------- ART--------------104-------------10.45
September 25, 2008 at 5:29 pm
upperbognor; Cant do this as Table3 only can be linked to table2.
Sorry i didnt give enough info in the first place...
September 26, 2008 at 6:27 am
This will meet your requirement
select table1.*,table2.call_ref,table3.call_time
from table1
left join
table2 on
table1.order_no = table2.order_no
left join table3
on table2.call_ref = table3.call_ref
September 26, 2008 at 6:57 am
This does not give me all the data from table one! I tried this! Also i tried it with brackets! No luck!
September 26, 2008 at 3:12 pm
Brute force solution... no extra charge.. 😉
create table tlb_one
( order_no varchar(10) null,
description varchar(20) null )
insert tlb_one
select null, 'ABC'
union all
select null, 'ACC'
union all
select 'K12', 'ACD'
union all
select 'K13', 'ADE'
union all
select 'K14', 'ART'
create table tlb_two
( order_no varchar(10) null,
call_ref int null )
insert tlb_two
select 'K12', 102
union all
select 'K13', 103
union all
select 'K14', 104
union all
select 'K15', 105
union all
select 'K6', 106
union all
select 'K17', 107
create table tlb_three
( call_ref int null,
otime decimal(5,2) )
insert tlb_three
select 103, 10.30
union all
select 104, 10.45
union all
select 105, 11.00
union all
select 106, 12.00
union all
select 107, 13.00
select t1.order_no, t1.description, t2.call_ref
into tlb_four
from tlb_one t1,
tlb_two t2
where t1.order_no *= t2.order_no
order by t1.order_no
alter table tlb_four
add otime decimal(5,2)
update tlb_four
set otime = t3.otime
from tlb_four t4,
tlb_three t3
where t4.call_ref *= t3.call_ref
select *
from tlb_four
order_nodescriptioncall_refotime
{null} ABC {null} {null}
{null} ACC {null} {null}
K12 ACD 102 {null}
K13 ADE 103 10.30
K14 ART 104 10.45
September 26, 2008 at 5:46 pm
tbeadle,
Your recommended solution will not work because it is using non-ANSI outer join operators. Those operators have been deprecated in SQL Server 2005.
To the OP - the other solutions posted should work. Table1 is the preserved table and as long as all other tables are referenced as left outer joins, and - you do not reference any columns from the other tables in the where clause - you will get all rows from table1.
As soon as you reference one column from either table2 or table3 in the where clause - the joins will be escalated to inner joins and you won't get all the rows from table1.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 29, 2008 at 7:50 am
Hello,
While it's true the "non-standard' outer joins are depreciated, they do work and will work quite some time into the future. Microsoft depreciated announcements do not mean the code will no longer work, just that at some time in the future they will not support them. Considering the installed base of code using the 'non-standard' code methods, some time in the future will be beyond the time the Falcons win the super bowl...hoot!
I'm not sure I understand your other two paragraphs. We want all rows from table one through out the processing. I do not miss any rows from table one at any point in the processing. I used the table four to build the data, hense the comment 'brute force'.
Please look at the very bottom and it shows a simple select * from table four and shows the correct results. If the code 'will not work', the results indicate they do. I checked the syntax on a 2005 server and they work fine....? So I'm puzzeled.
Perhaps if you can explain your last paragraph the light bulb will go off in my head.
Thanks
Terry
September 29, 2008 at 8:16 am
Duleep Nagahawatte (9/26/2008)
This does not give me all the data from table one! I tried this! Also i tried it with brackets! No luck!
Here's the solution kindly and expertly provided by Gayathri.Varadarajan operating on your own test data:
[font="Courier New"]CREATE TABLE #Table1 (Order_NO CHAR(3), [DesCription] VARCHAR(3))
INSERT INTO #Table1 (Order_NO, DesCription)
SELECT NULL, 'ABC' UNION ALL
SELECT NULL, 'ACC' UNION ALL
SELECT 'K12', 'ACD' UNION ALL
SELECT 'K13', 'ADE' UNION ALL
SELECT 'K14', 'ART'
CREATE TABLE #Table2 (Order_NO CHAR(3), Call_Ref VARCHAR(3))
INSERT INTO #Table2 (Order_NO, Call_Ref)
SELECT 'K12', '102' UNION ALL
SELECT 'K13', '103' UNION ALL
SELECT 'K14', '104' UNION ALL
SELECT 'K15', '105' UNION ALL
SELECT 'K16', '106' UNION ALL
SELECT 'K17', '107'
CREATE TABLE #Table3 (Call_Ref VARCHAR(3), [Time] CHAR(5))
INSERT INTO #Table3 (Call_Ref, [Time])
SELECT '103', '10.30' UNION ALL
SELECT '104', '10.45' UNION ALL
SELECT '105', '11.00' UNION ALL
SELECT '106', '12.00' UNION ALL
SELECT '107', '13.00'
SELECT #Table1.*, #Table2.Call_Ref, #Table3.[Time]
FROM #Table1
LEFT JOIN #Table2 ON #Table1.order_no = #Table2.order_no
LEFT JOIN #Table3 ON #Table2.call_ref = #Table3.call_ref
[/font]
Results:
Order_NO DesCription Call_Ref Time
-------- ----------- -------- -----
NULL ABC NULL NULL
NULL ACC NULL NULL
K12 ACD 102 NULL
K13 ADE 103 10.30
K14 ART 104 10.45
(5 row(s) affected)
Try it and see.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 30, 2008 at 12:24 pm
tbeadle (9/29/2008)
Hello,While it's true the "non-standard' outer joins are depreciated, they do work and will work quite some time into the future. Microsoft depreciated announcements do not mean the code will no longer work, just that at some time in the future they will not support them. Considering the installed base of code using the 'non-standard' code methods, some time in the future will be beyond the time the Falcons win the super bowl...hoot!
I'm not sure I understand your other two paragraphs. We want all rows from table one through out the processing. I do not miss any rows from table one at any point in the processing. I used the table four to build the data, hense the comment 'brute force'.
Please look at the very bottom and it shows a simple select * from table four and shows the correct results. If the code 'will not work', the results indicate they do. I checked the syntax on a 2005 server and they work fine....? So I'm puzzeled.
Perhaps if you can explain your last paragraph the light bulb will go off in my head.
Thanks
Terry
When you tested this in SQL Server 2005 - what was the compatibility level of the database you tested against? I would bet anything that it is in compatibility level 80 which is SQL Server 2000. Try it against a database in 90 compatibility mode and you will get the following error message:
Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
As for the other paragraphs - basically, you cannot have the following:
SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
INNER JOIN table3 ON table3.fkey = table2.key
This forces the relationship to an inner join. So, if you want the outer join you would rewrite the above as:
SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
LEFT JOIN table3 ON table3.fkey = table2.key
But, if you then add to the where clause any filtering on table2 or table3 - the relationship is forced back to an inner join (unless you are only checking for the existence of null).
SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
LEFT JOIN table3 ON table3.fkey = table2.key
WHERE table2.somecolumn = somevalue
The above effectively becomes an inner join and eliminates the rows where there are nulls for either table2 or table3.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 30, 2008 at 1:10 pm
Jeffrey Williams (9/30/2008)
tbeadle (9/29/2008)
As for the other paragraphs - basically, you cannot have the following:SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
INNER JOIN table3 ON table3.fkey = table2.key
This forces the relationship to an inner join. So, if you want the outer join you would rewrite the above as:
SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
LEFT JOIN table3 ON table3.fkey = table2.key
Wouldn't you want to do it like this?:
SELECT *
FROM table1
LEFT JOIN (table2 INNER JOIN table3 ON table3.fkey = table2.Pkey)
ON table2.fkey = table1.Pkey
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 1:20 pm
rbarryyoung (9/30/2008)
Jeffrey Williams (9/30/2008)
tbeadle (9/29/2008)
As for the other paragraphs - basically, you cannot have the following:SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
INNER JOIN table3 ON table3.fkey = table2.key
This forces the relationship to an inner join. So, if you want the outer join you would rewrite the above as:
SELECT ...
FROM table1
LEFT JOIN table2 ON table2.fkey = table1.key
LEFT JOIN table3 ON table3.fkey = table2.key
Wouldn't you want to do it like this?:
SELECT *
FROM table1
LEFT JOIN (table2 INNER JOIN table3 ON table3.fkey = table2.Pkey)
ON table2.fkey = table1.Pkey
Looking back at the sample data provided, nope. Need to have an outer join between table2 and table3.
😎
September 30, 2008 at 1:28 pm
Actually - it's deprecated. Right now. If you try that syntax against a SQL Server 2005 or higher, with compatibility @ 90 or above, you get:
Msg 4147, Level 15, State 1, Line 5
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
So - the non-ANSI INNER join still works for now, but even it is on borrowed time.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply