Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to joi
n "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a
FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.33
0487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question
, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "a
Xb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can
be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model
(described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need*
it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG