Need help combining tables

[Markh51]

Hi I have 3 tables:

ITEM
BID
BUY

This is what I have:

SELECT Item.itemID, Item.itemDescription
FROM ITEM
INNER JOIN Bid ON item.itemID = bid.itemID
INNER JOIN Buy ON item.lotID = buy.lotID
WHERE "my conditions"
ORDER "my order"

The problem here is, it will only return records when a entry exists in all the above tables with the same itemID, but the thing is, that the entry will exist in the table ITEM and EITHER BID or BUY. It cannot exist in both BID and BUY only one or the other.

So how do I join the tables so it will return records when the item exists in ITEM and EITHER BID or BUY ???

I have tried using a union statement but keep getting errors about "operator must have an equal number of expressions in their target lists".

Thanks in advance.

 

[Markh51]

sorry the SQL statement above is supposed to be:

SELECT Item.itemID, Item.itemDescription
FROM ITEM
INNER JOIN Bid ON item.itemID = bid.itemID
INNER JOIN Buy ON item.itemID = buy.itemID
WHERE "my conditions"
ORDER "my order"

Oooops

 

[zhavic]

For example this way:

SELECT Item.itemID, Item.itemDescription
FROM ITEM
LEFT JOIN Bid ON item.itemID = bid.itemID
LEFT JOIN Buy ON item.itemID = buy.itemID
WHERE ( bid.itemID IS NOT NULL OR buy.itemID IS NOT NULL )
AND "my conditions"
ORDER "my order"

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.