JOIN - 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 2005 Forums

Transact-SQL (2005)

JOIN

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-26 : 20:49:19

Hello,

I have 4 tables related as follows:

[A] > Aid (PK), Bid(FK), Uid (FK), ...
[B] > Bid (PK), Aid(FK), Uid (FK), ...
[C] > Cid (PK), Aid(FK), Uid (FK), ...
[U] > Uid (PK)

U ----- A
|------ B ----- U
|------ C ----- U

I need, given an Aid, to create a table with all Us that relate to A directly or indirectly through B or C.
Of course I will need all ID columns on that table ...

For A and U I have the following:

SELECT a.AId, u.*
FROM A a
INNER JOIN U u
ON a.AId = u.UId
WHERE a.AId = @AId

I was trying to add other inner joins for get Us that related with B and C but it does not work.

Could someone, please help me with this?

By the way, isn't LEFT JOIN and LEFT OUTER JOIN the same in SQL 2005?

Thanks,
Miguel

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-26 : 21:47:09

hi Miguel

If you really have the tables designed like that- I don't see how you can add any rows into A, B or C without having foreign key problems- can you post some DDL with example data so we can play? eg

declare @A table (Aid int identity, Bid int, Uid int, description varchar(50))
declare @B table (Bid int identity, Aid int, Uid int, description varchar(50))
declare @C table (Cid int identity, Aid int, Uid int, description varchar(50))
declare @U table (Uid int identity, description varchar(50))

insert into @U (description) select 'U1'
insert into @U (description) select 'U2'
insert into @U (description) select 'U3'
insert into @A (Bid, Uid, description) select ?, 1, 'A1'

I've included a description field to try to make it easier to follow what is going on - but don't feel obliged

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-27 : 06:27:52

Hi,

Ooops, I think you got miss leaded by my diagram because SQL Team forums remove the spaces. Consider the dots as nothing:

U ----- A
...........|------ B ----- U
...........|------ C ----- U

That's my schema. Why will I have problems? It seems ok to me.
I will post anything that needed if still needed.

And about the stored procedure, can I do this:


select Uid from [A] where Aid = @Aid
union
select Uid from [B] where Aid = @Aid
union
select Uid from [C] where Aid = @Aid

Basically, I want to get all U records that are somehow related to A, B and C given an @Aid parameter.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 06:50:16

Listen to rrb.

Please provide sample data in the format he describes, and your expected output.

E 12°55'05.25"
N 56°04'39.16"

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-27 : 07:38:26

Hi,

I had a mistake in table A. I found it now!
Sorry, it was 3 am when I posted the problem.

Anyway, I created the code as you asked me (I think!):


declare @A table (Aid int identity, Uid int, description varchar(50))
declare @B table (Bid int identity, Aid int, Uid int, description varchar(50))
declare @C table (Cid int identity, Aid int, Uid int, description varchar(50))
declare @U table (Uid int identity, description varchar(50))

insert into @U (description) select 'U1'
insert into @U (description) select 'U2'
insert into @U (description) select 'U3'
insert into @U (description) select 'U4'

insert into @A (description, Uid) select 'A1', 1
insert into @A (description, Uid) select 'A2', 2
insert into @A (description, Uid) select 'A3', 2

insert into @B (description, Aid, Uid) select 'B1', 2, 1
insert into @B (description, Aid, Uid) select 'B2', 2, 2
insert into @B (description, Aid, Uid) select 'B3', 1, 2

insert into @C (description, Aid, Uid) select 'C1', 1, 2
insert into @C (description, Aid, Uid) select 'C2', 1, 1
insert into @C (description, Aid, Uid) select 'C3', 2, 2

SELECT * FROM @U
SELECT * FROM @A
SELECT * FROM @B
SELECT * FROM @C

So for a given @Aid = 2 I would get all the records in U that are related to @Aid = 2 through A, B or C tables.

And in my results table I would get the following columns:
Uid, Description (U), Aid, Bid, Cid

Thank You,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-27 : 10:00:00

Hi,

The following seems to be working but with a problem:


select Uid from @A where Aid = @Aid
union
select Uid from @B where Aid = @Aid
union
select Uid from @C where Aid = @Aid

How can I add the AId, BId and CId to the returned tables?

I also need to get the column names Aid, Bid and Cid on the returned table since in my C# code I will need to create a relation to get the U records that are associated with A, B or C.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:05:36

For @Aid = 2, what is the complete and utter expected result for the sample data posted at 09/27/2007 : 07:38:26 ?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:05:52

Like this you mean?


select Uid, AId, NULL AS BId, NULL AS CId from @A where Aid = @Aid
union
select Uid, NULL AS AId, BId, NULL AS CId from @B where Aid = @Aid
union
select Uid, NULL AS AId, NULL AS BId, CId from @C where Aid = @Aid

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:06:49

Actually at that point you could use UNION ALL as the rows will, presumably, have no duplicates, and UNION ALL would then be faster (i.e. avoid the ordering & de-duping step)

Kristen

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-27 : 17:49:02

Hi,

I am still having a few problems since I need to add a new table and I need to get all U columns.

Peso, I am posting all DDL and the expect result as you said:


declare @X table (Xid int identity, description varchar(50))
declare @A table (Aid int identity, Xid int, Uid int, description varchar(50))
declare @B table (Bid int identity, Aid int, Uid int, description varchar(50))
declare @C table (Cid int identity, Aid int, Uid int, description varchar(50))
declare @U table (Uid int identity, description varchar(50))

insert into @X (description) select 'X1'
insert into @X (description) select 'X2'
insert into @A (description, Xid, Uid) select 'A1', 1, 1
insert into @A (description, Xid, Uid) select 'A2', 2, 2
insert into @B (description, Aid, Uid) select 'B1', 2, 1
insert into @B (description, Aid, Uid) select 'B2', 2, 2
insert into @B (description, Aid, Uid) select 'B3', 1, 2
insert into @C (description, Aid, Uid) select 'C1', 1, 2
insert into @C (description, Aid, Uid) select 'C2', 1, 1
insert into @C (description, Aid, Uid) select 'C3', 2, 3
insert into @U (description) select 'U1'
insert into @U (description) select 'U2'

select * from @X
select * from @A
select * from @B
select * from @C
select * from @U

What I am trying to do is:

- Giving a VALUE for @X, get all U records related to X, through A, B, or C
- I need to include all U columns, i.e., Uid and description, and all Keys, i.e., Aid, Bid, Cid, Uid


declare @X table (Xid int identity, description varchar(50))
declare @A table (Aid int identity, Xid int, Uid int, description varchar(50))
declare @B table (Bid int identity, Aid int, Uid int, description varchar(50))
declare @C table (Cid int identity, Aid int, Uid int, description varchar(50))
declare @U table (Uid int identity, description varchar(50))

insert into @X (description) select 'X1'
insert into @X (description) select 'X2'
insert into @A (description, Xid, Uid) select 'A1', 1, 1
insert into @A (description, Xid, Uid) select 'A2', 2, 2

insert into @B (description, Aid, Uid) select 'B1', 2, 1
insert into @B (description, Aid, Uid) select 'B2', 2, 2
insert into @B (description, Aid, Uid) select 'B3', 1, 2

insert into @C (description, Aid, Uid) select 'C1', 1, 2
insert into @C (description, Aid, Uid) select 'C2', 1, 1
insert into @C (description, Aid, Uid) select 'C3', 2, 3
insert into @U (description) select 'U1'
insert into @U (description) select 'U2'

select * from @X
select * from @A
select * from @B
select * from @C
select * from @U

For @Xid = 1 I should get:


Aid    Bid     Cid     Uid    description
1      NULL    NULL    1      U1
NULL   3       NULL    2      U2
NULL   NULL    1       2      U2
NULL   NULL    2       1      U1

Thank You,
Miguel

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 19:49:04

That's basically just the same as I posted above, i.e.:

select AId, NULL AS BId, NULL AS CId, U.Uid, U.description from @X AS X JOIN @A AS A ON X.Xid = A.Xid JOIN @U AS U ON U.Uid = A.Uid where X.Xid = @Xid
union
select NULL AS AId, BId, NULL AS CId, U.Uid, U.description from @B AS B JOIN @A AS A ON A.Aid = B.Aid JOIN @X AS X ON X.Xid = A.Aid JOIN @U AS U ON U.Uid = B.Uid where X.Xid = @Xid
union
select NULL AS AId, NULL AS BId, CId, U.Uid, U.description from @C AS C JOIN @A AS A ON A.Aid = C.Aid JOIN @X AS X ON X.Xid = A.Aid JOIN @U AS U ON U.Uid = C.Uid where X.Xid = @Xid

Kristen

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-27 : 20:20:19

Hi Kristen,

Thank You Very Much!

And thank you Peso and rrb to.

Cheers,
Miguel

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 20:25:08

You can change UNION to UNION ALL in the above, which will be more efficient.

Sorry, mentioned that in earlier post but forgot to include in that example.

Kristen

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-27 : 20:30:53

Hi Kristen,

Yes, I saw that in the earlier post. I used Union All.

Just one question that came up with this post:

If I have to tables, let's say: Articles and Users.
They are related by the FK AuthorId in Articles and PK UserId in Users.

Is it normal to give different names in these cases?
Or should I name UserId in both tables?

This just came up when creating this code.

Thanks,
Miguel

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 21:04:30

"Is it normal to give different names in these cases?
Or should I name UserId in both tables?"

Hmmm ... horses-for-courses I think.

We have column names that are unique within the database. We have a prefix for each column name that is a "nickname" for the table, and prefix the column with that. (Ours is actually in two parts, the "module" and the "table").

So for us we would have an Order Header table and an Order Item table. This are part of our "Sales Order Processing" module.

Table names would be:

SOP_ORDH_OrderHeader
SOP_ORDI_OrderItem

Column names would be

sop_ordh_ID
sop_ordh_Date
sop_ordh_Total

and

sop_ordi_Qty
sop_ordi_PriceEach

and importantly

sop_ordi_sop_ordh_ID

so when we have a JOIN it would look like


SELECT *
FROM SOP_ORDH_OrderHeader
     JOIN SOP_ORDI_OrderItem
         ON sop_ordi_sop_ordh_ID = sop_ordh_ID

and I think that

sop_ordi_sop_ordh_ID = sop_ordh_ID

reduces error because it becomes obvious if I try to join two things that aren't intended to be joined!

Also, if we have a variable (SQL Sprocs or Application or Web Form whatever) we name it using the unique column name as a basis.

So wherever I reference the Order Item Qty I call it sop_ordi_Qty. Then if I decide to change the column name in the future a Find & Replace across all source code will find everything. That doesn't work so well if you call all your PK columns [ID]

Same thing if I am bug hunting on sop_ordi_Qty, or want to change sop_ordi_Qty from TINYINT to BIGINT

and want to check where it is references in the code to see if there are any ramifications to the change.

But that's just the way that we do it, there are other styles and view points.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources