December 27, 2008 at 8:55 pm
Hello
I have two tables a Master and Child. I want to return a recordset that has all of the Master records and any related child values.
Sample Data:
Master Table
'id' 'field1'
1 Test1
2 Test2
3 Test3
Child Table
'id' 'masterID' 'field1'
1 2 Child1
The following code will only return one record:
SELECT master.id, master.field1, child.field1
FROM master INNER JOIN child on master.id = child.masterID
WHERE child.id = 1Resulting recordset:
2,Test2, Child1
What I need to achieve is:
1,Test1,NULL
2,Test2,Child1
3,Test3,NULL
I hope that makes sense. I am not even sure if it is possible.
I have tried using various OUTER JOIN combinations but the result is the same.
Regards
James
December 27, 2008 at 9:49 pm
interfac (12/27/2008)
I have tried using various OUTER JOIN combinations but the result is the same.
its coz of the where condition..
I tried something and i got desired result...
=================================
create table tab1
(
id int,
field1 varchar(100)
)
create table tab2
(
id int,
masterid int,
feild1 varchar(100)
)
insert into tab1
select 1, 'test1'
union all
select 2, 'test2'
union all
select 3, 'test3'
insert into tab2
select 1,2,'child1'
SELECT tab1.id, tab1.field1, tab2.feild1
FROM tab1 left outer JOIN tab2 on tab1.id = tab2.masterID
output
--------------------------------
1test1NULL
2test2child1
3test3NULL
December 27, 2008 at 11:35 pm
Thanks Pradeep
That does work, but I need the WHERE as it is to show the Child records for a User (for example All Products and Products ordered).
Regards
James
December 28, 2008 at 12:15 am
interfac (12/27/2008)
That does work, but I need the WHERE as it is to show the Child records for a User (for example All Products and Products ordered).
What I understand from your last post is that you have three tables, products, ProductsOrdered and Users
OR
You have two tables Products and ProductsOrdered with a column in either of these tables (USER ID)
In either cases you can definately filter records based on UserID....
However, in the example that i wrote, If i write WHERE tab2.masterid=1, it wont pick up records with master as it would defeat the purpose of using left outer join which does return NULL for unmatched records in the tab2(placed on right side of =) and will filter all records from the resultset containing NULL in tab2.Masterid
May be more information (table structures and desired output) from u would help us finding the right solution.
This link should help you in framing your question.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 29, 2008 at 6:22 am
But the WHERE clause in that query will more or less always force an INNER JOIN. If you're filtering by the Child records, then that's what you're filtering by. The only way to change it would be to add an OR clause to get all the Child records that match the query or those that are NULL while still using a LEFT JOIN. That might cause performance to suffer though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 29, 2008 at 8:54 am
interfac (12/27/2008)
...but I need the WHERE
If you need that WHERE clause condition, you'll have to do it like Grant says to handle the case where there is no matching child record (so child.id in resultset would be NULL):
SELECT master.id, master.field1, child.field1
FROM master
LEFT OUTER JOIN child ON master.id = child.masterID
WHERE (child.id = 1 OR child.id IS NULL)
or put the condition as part of the join itself:
SELECT master.id, master.field1, child.field1
FROM master
LEFT OUTER JOIN child ON master.id = child.masterID AND child.id = 1
December 29, 2008 at 10:28 am
SELECT master.id, master.field1, child.field1
FROM master Left outer JOIN child on master.id = child.masterID
December 29, 2008 at 10:33 am
emailtoDeepa (12/29/2008)
SELECT master.id, master.field1, child.field1FROM master Left outer JOIN child on master.id = child.masterID
But that is just the same OUTER JOIN that the OP wrote in the first post. What about how to deal with the WHERE clause?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 29, 2008 at 2:57 pm
That code returns the correct results that I was looking for!
Regards
James
December 30, 2008 at 5:53 am
interfac (12/29/2008)
That code returns the correct results that I was looking for!Regards
James
Then I'm really confused. If you drop the WHERE clause, your initial query should have worked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 30, 2008 at 3:12 pm
This is the master data:
idfield1
2Risk Reporting
5Risk Scoring
6Compliance
7Risks
8In the next release ...
This the child data:
masterID field1
5 1
2 1
5 7
8 7
8 1
SELECT master.id, master.field1, child.field1
FROM master INNER JOIN child on master.id = child.masterID
WHERE child.id = 1
This only returns the Master records where there is a matching child record.
5 Risk Scoring 1
2 Risk Reporting 1
8 In the next release ...1
With this code:
SELECT master.id, master.field1, child.field1
FROM master
LEFT OUTER JOIN child ON master.id = child.masterID AND child.id = 1
The following is returned:
2 Risk Reporting1
5 Risk Scoring 1
6 Compliance NULL
7 Risks NULL
8 In the next release ...1
The other suggestion also returns the correct results:
SELECT master.id, master.field1, child.field1
FROM master
LEFT OUTER JOIN child ON master.id = child.masterID
WHERE (child.id = 1 OR child.id IS NULL)
Regards
James
December 30, 2008 at 3:27 pm
interfac (12/30/2008)
With more testing I have found that none of the code samples return what I require.
SELECT master.id, master.field1, child.field1FROM master INNER JOIN child on master.id = child.masterID
WHERE child.id = 1
This only returns the Master records where there is a matching child record.
Here is the master data:
idfield1
2Risk Reporting
5Risk Scoring
6Compliance
7Risks
8In the next release ...
Here is the child data:
masterIDfield1
Okay, so now you need to read the Best Practices article linked to in my signature. Post the create and insert statements and expected results as outlined in that article. This will help us help you get the required results you are looking for.
You have been given several different queries that should have given you what you asked for - so, it definitely is not clear to anyone what it is you really are looking to have returned.
And, since none of the code examples returns what it is you require - please post exactly what it is you do require so nobody here is guessing.
Edit: You can disregard this post. Looks like the OP edited his original post after I started replying to this message. If I read the new post correctly - the outer joins are in fact returning what he is looking for.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply