SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jutiyi
Starting Member

9 Posts

Posted - 08/20/2013 :  21:57:57  Show Profile  Reply with Quote
I have three table

Table A
ID NAME
1 NameA

Table B
ID Coutry
1 County A
1 Counry 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 County A Family A
1 County B Family B
1 NULL Family C

Which query should I use?

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/21/2013 :  00:29:32  Show Profile  Reply with Quote
you should use something like

SELECT a.ID,b.County,c.Family
FROM TableA a
LEFT JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq
           FROM TableB
          )b
ON a.ID = b.ID
LEFT JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq
           FROM TableC
          )c
ON c.ID = b.ID
AND c.Seq = b.Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
326 Posts

Posted - 08/21/2013 :  14:37:24  Show Profile  Reply with Quote
The solution visakh16 provided is close but needs one adjustment -- the "ORDER BY ID" in the OVER clauses should be changed to "ORDER BY Country" and "ORDER BY Family" so that the two datasets are guaranteed to lineup alphabetically. Otherwise, SQL doesn't guarantee they will even if they are physically ordered alphabetically in the tables.

That said, although this will get you the result you want, it will not be reliable if the data is anything other than EXACTLY what you provided. In fact, based on the schema, there is no reliable way to do so unless your business rules say that these items should always lineup 1-to-1 alphabetically. Even then, what if one table skips an entry -- say Country goes A, C, D and Family goes A, B, C? Do you want Country C to lineup to Family B? If not, short of string parsing, which is in itself dependent on a specific data format, there is no good way to line up A to A, B to B, C to C and so on.

This is not how you would want to design and build an IT solution as you would want to model your primary keys and foreign keys before constructing queries. Is TableB even related to TableA or is it just related to TableC? In the case of TableB & TableC, either or both tables should also have foreign key relationships that make the relationships to other entities explicit. Even though you asked for a SQL statement, it is useless unless you have the correct data model to begin with.

This should get you started: http://www.databaseanswers.org/tutorial4_data_modelling/index.htm

Edited by - lazerath on 08/21/2013 14:45:35
Go to Top of Page

ShivaKrishna
Starting Member

India
20 Posts

Posted - 08/28/2013 :  11:14:53  Show Profile  Reply with Quote
SELECT a.ID,b.County,c.Family
FROM TableA a
Inner JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq
FROM TableB
)b
ON a.ID = b.ID
Right JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq
FROM TableC
)c
ON c.ID = b.ID
AND c.Seq = b.Seq
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
164 Posts

Posted - 08/29/2013 :  04:30:24  Show Profile  Reply with Quote
This Query give what u expected,

SELECT c.ID,b.country,c.Family
FROM TableA a
Inner JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq
FROM TableB
)b
ON a.ID = b.ID
right JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq
FROM TableC
)c
ON c.ID = b.ID
AND c.Seq = b.Seq


veeranjaneyulu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000