SQL JOIN query help

On Dec 2, 4:04 pm, wrote:
> On Dec 2, 1:43 pm, daicamad wrote:
>
> > I need to do a query for a view. The query involves 3 tables, let call
> > them A, B, C. There is ItemID column in table A that match either
> > ItemID column on table B or C (but not both, because ItemIDs in table
> > B and C are assigned in a way that they cannot be the same)
>
> > How do I create a query that will JOIN these 3 tables together?
>
> > I start with this, but it doesn't work because I guess it expects
> > A.ItemID = B.ItemID = C.ItemID
>
> > SELECT A.a, A.b, B.Desc, C.Desc FROM [A] INNER JOIN [B] ON A.ItemID =
> > B.ItemID INNER JOIN [C] ON A.ItemID = C.ItemID;
>
> Try this:
>
> SELECT
>   A.a,
>   A.b,
>   COALESCE(B.Desc, C.Desc) Desc
> FROM A LEFT JOIN B ON A.ItemID = B.ItemID
> LEFT JOIN C ON A.ItemID = C.ItemID;

Cool! It works. I didnot know about COALESCE function.

Thanks
 >> Stay informed about: SQL JOIN query help 

"daicamad" wrote in message

>I need to do a query for a view. The query involves 3 tables, let call
> them A, B, C. There is ItemID column in table A that match either
> ItemID column on table B or C (but not both, because ItemIDs in table
> B and C are assigned in a way that they cannot be the same)
>
> How do I create a query that will JOIN these 3 tables together?
>
> I start with this, but it doesn't work because I guess it expects
> A.ItemID = B.ItemID = C.ItemID
>
> SELECT A.a, A.b, B.Desc, C.Desc FROM [A] INNER JOIN [B] ON A.ItemID =
> B.ItemID INNER JOIN [C] ON A.ItemID = C.ItemID;

SELECT <column list>
FROM A
JOIN B
ON A.ItemID = B.ItemID

UNION

SELECT <column list>
FROM A
JOIN C
ON A.ItemID = C.ItemID




Rick Sawtell
 >> Stay informed about: SQL JOIN query help 

On 2-Dec-2008, daicamad wrote:

> I need to do a query for a view. The query involves 3 tables, let call
> them A, B, C. There is ItemID column in table A that match either
> ItemID column on table B or C (but not both, because ItemIDs in table
> B and C are assigned in a way that they cannot be the same)
>
> How do I create a query that will JOIN these 3 tables together?
>
> I start with this, but it doesn't work because I guess it expects
> A.ItemID = B.ItemID = C.ItemID
>
> SELECT A.a, A.b, B.Desc, C.Desc FROM [A] INNER JOIN [B] ON A.ItemID =
> B.ItemID INNER JOIN [C] ON A.ItemID = C.ItemID;

It's a little unclear precisely what you are wanting to return and I have a
strong feeling your table design in not optimal. Nevertheless, this might
be a start if I am understanding you correctly:

SELECT A.a,A.b,COALESCE(B.Desc,C.Desc)
FROM A
LEFT JOIN B ON A.ItemID = B.ItemID
LEFT JOIN C ON A.ItemID = C.ItemID
WHERE NOT(B.ItemID IS NULL
AND C.ItemID IS NULL)

The WHERE clause prevents rows from being returned that are in A but are not
in B or C (not sure if you wanted this or not).

mouser
 >> Stay informed about: SQL JOIN query help