August 20, 2013 at 8:40 pm
I have three tables
Table A
ID NAME
1 NameA
Table B
ID Coutry
1 County A
1 Country B
Table C
ID Family
1 Family A
1 Family B
1 Family C
I use a outer join
I get the following result set
1 County A Family A
1 County A Family B
1 County A Family C
1 County B Family A
1 County B Family B
1 County B Family C
But I want to get the following
1 Country A Family A
1 Country B Family B
1 NULL Family C
Which query should I use?
Thanks
August 20, 2013 at 8:59 pm
your question isn't quite specific!
What tables you are outer joining on? Based on your output it doesn't look like you are using Table A at all.
August 20, 2013 at 9:10 pm
Here is the SQL Script.. Basicailly the return rows is YxZ , Y= rows of Table B , Z = rows of Table C.
But I want the returned rows is max(Y,Z), and those empty value will be NULL value.
SELECT TOP (100) PERCENT dbo.TableA.ID, dbo.TableB.Country, dbo.TableC.Family
FROM dbo.TableA LEFT OUTER JOIN
dbo.TableB ON dbo.TableA.ID = dbo.TableB.ID FULL OUTER JOIN
dbo.TableC ON dbo.TableB.ID = dbo.TableC.ID
ORDER BY dbo.TableB.Country
August 20, 2013 at 9:41 pm
Are there any foreign keys in the tables? Are there more columns by chance?
If you wanted a cartesian product then you have it based on the results listed (select *
from b cross join c)
If the family had a foreign key for example then this might help:
create table b
(id int
,country varchar(50));
insert into b
values (1, 'CountryA');
insert into b
values (2, 'CountryB');
create table c
(id int
,family varchar(50)
,b_ID int);
insert into c
values (1, 'FamilyA', 1);
insert into c
values (2, 'FamilyB', 2);
insert into c
values (3, 'FamilyC', null);
select *
from c
left outer join b on c.b_id = b.id
August 20, 2013 at 11:15 pm
It is a sample date..
All tableB and tableC only show for data of ID "1".
Country A has not relationship with Family A.
Yes, ID is foreign key for tableB and table C.
The example is a sample data..
All tableB and tableC only show for data of ID "1".
Country A has not relationship with Family A.
It can ID "1" has country "A","B", and family "A","B", and "C".
so, what need to show is
ID Country Family
1 A A
1 B B
1 NULL C
which showing all the country and family in result where for those extra row with empty replace with NULL
August 21, 2013 at 7:36 am
Your question is very unclear. We would like to help but you have to first provide enough details for us to be able to help. The best thing you can to do help is to post a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply