HOW DO I JOIN THREE TABLES

[Markh51]

I am having a little trouble in understanding how I can join three tables together, when I am forming my SQL query.

Can any one help ?

Mark

 

[hmckillop]

You would need to more specific ie give brief table details, but you can join three tables as shown below

SELECT tab1.*
FROM Table1 tab1
INNER JOIN table2 tab2 ON tab2.PK = tab1.FK
INNER JOIN table3 tab3 on tab3.PK = tab2.FK

 

[Markh51]

I have three tables bid , buy and lot.

Does each of the tables have to have something in common to be able to join them ??? if so they all share "lotID".

How do I make it return the data as though I was just reading from one table, the reason I ask is because, when I am displaying the table values, how do I know which one to put (i.e. bid.lotID or buy.lotID or lotID)

I hope this helps as I pretty confused my-self.

Cheers,
Mark

 

[hmckillop]

Try

SELECT *
FROM Buy
INNER JOIN bid ON buy.lotID = bid.lotID
INNER JOIN lot ON lot.lotID = bid.lotID

This will return all info. from all tables.
If you want specific fields from specific tables change the * to be <tablename>.<fieldname> i.e. if you have 2 fields in buy called field1 and field2, and one field in lot caleld lotfield1 that you want returned change it to
buy.field1, buy.field2, lot.lotfield1

 

[Markh51]

I can't seem to get the above to work, how did you come up with to use &quot;Buy&quot; in the FROM clause ????

I think I need to join BID and BUY onto LOT.

Thanks.

 

[hmckillop]

You can switch the tables around

SELECT *
FROM LOT
INNER JOIN bid ON Lot.lotID = Bid.lotID
INNER JOIN buy ON Lot.lotID = Buy.lotID

But regardless of this minor amendment, the query in the earlier posting should have worked.

Are all the fields which are common called the same in each table (may seem obvious but better check)
i.e is there a field LotID on each of the three tables?

 

[JamesLean]

Markh51,
hmckillop has given you basic examples of how to join tables together. If you are still stuck or this is not working then you need to give us more detail about your specific problem:

- What tables have you got?
- What columns has each table got?
- How are the tabels related?
- What is the end result you want to see?

Also, seeing some example data is often useful.

On a more general point, you might also like to look around for some basic SQL tutorials to help you get started.

--James

 

[Markh51]

Ok, I have it working so far but if I use the following Query:

SELECT Bid.userID
FROM Bid, Buy
WHERE Bid.lotID='9999' OR Buy.lotID='9999'

it retuns loads of results
-----------------------------------------------------------
but if i use:

SELECT userID
FROM Bid
WHERE lotID='9999'

AND:

SELECT userID
FROM Buy
WHERE lotID='9999'

it returns the correct results.

But I need to be able to combine the above two query's into one. But then I have to make sure that it doesn't only return results when the SAME LotID is present in both tables (it may be present in one or the other)
-----------------------------------------------------------

 

[JamesLean]

It looks like you need a UNION query, not a join:

SELECT userid
FROM bid
WHERE lotid = '9999'
UNION
SELECT userid
FROM buy
WHERE lotid = '9999'

--James

 

[Markh51]

Works like a charm, thanks James.

thanks to everyone who helped.

Mark