SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL

Author

Topic

jutiyi
Starting Member

9 Posts

Posted - 2013-08-20 : 21:57:57

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

52326 Posts

Posted - 2013-08-21 : 00:29:32

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-21 : 14:37:24

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

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 11:14:53

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

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-29 : 04:30:24

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources