Inner, outer or full join on three tables in MySQL database on linux with apache

Question

I want to have some help creating my query to get information from three different tables sharing information in common. My first table is:

auctions
id title description user_id(who posted it)

My second table is:

bids
id user_id bid auction_id owner_id

My third table is:

users
id username X XX XXX XXXX

...and my SQL is as follows however it's not returning any results:

SELECT auction_bids.user_id AS applicant, auction_bids.*, auctions.title FROM auction_bids, auctions
        WHERE auctions.user_id=".$_SESSION['userid']."  
        INNER JOIN users ON auction_bids.user_id = users.id 
        WHERE auction_bids.owner_id = ".$_SESSION['userid']."

What I need is to capture the auction's title, username who bidded on the auction and the bid. the auction has to have a bid and posted by the user who owns the $_SESSION['userid']. Any help is appreciated.

score 1 · Answer 1 · answered Jun 29 '13 at 08:36

You have two different 'where' statements, which may just need combining;

SELECT auction_bids.user_id AS applicant, auction_bids.*, auctions.title FROM auction_bids, auctions INNER JOIN users ON auction_bids.user_id = users.id WHERE auction_bids.owner_id = ".$_SESSION['userid']." AND auctions.user_id=".$_SESSION['userid']."

However, I'm not sure this is really what you want, as it will return only records where the specific user both 'owns' the item AND has bidded on it (both based on the userid session), rather than displaying all records from different people who have bidded on an item 'owned' by the user.

Something like: ? SELECT auction_bids.user_id AS applicant, auction_bids.*, auctions.title FROM auction_bids, auctions INNER JOIN users ON auction_bids.user_id = users.id, WHERE auction.owner_id = ".$_SESSION['userid']."

Hope this points you in the right direction!

thank you Chris and it is giving me the same error as: Unknown column 'auction_bids.user_id' in 'on clause' — Justin, Jun 29 '13 at 09:08

DevZer0 · Answer 2 · 2013-06-29T09:16:03.940

0

you have 2 where clauses, that is incorrect. I have revised your query based on your requirements.

SELECT auction_bids.user_id AS applicant, auction_bids.*, auctions.title 
FROM auction_bids, auctions
        INNER JOIN users ON auction_bids.owner_id = users.id 
        WHERE auction_bids.owner_id = ".$_SESSION['userid']."
        AND auctions.user_id=auctions_bids.owner_id

edited Jun 29 '13 at 09:16

answered Jun 29 '13 at 08:16

DevZer0

13,433
7
27
51

thanks DevZer0 yet it returned no results. i am new to this JOIN syntaxes – Justin Jun 29 '13 at 08:20
this query should run without error, you will have to make sure your passing it the right data, alternatively you can change the INNER JOIN to a LEFT OUTER JOIN and see if results exists. – DevZer0 Jun 29 '13 at 08:42
i tried that one too and this is the error it throws back: Unknown column 'auction_bids.user_id' in 'on clause' and for sure that column is in there – Justin Jun 29 '13 at 08:54
can u update the question with your table schema. `DESCRIBE TABLE [table]` – DevZer0 Jun 29 '13 at 08:56
it is just like i inserted in the question DevZer0 – Justin Jun 29 '13 at 09:09
i just tried it too and gave me the same error. it seems like query is not clear of ON selection that it caanot decide which one to pull. – Justin Jun 29 '13 at 09:23
it can't be the same error, i renamed the field. please pay more attention to detail – DevZer0 Jun 29 '13 at 09:29
this is what it is telling me: Unknown column 'auctions_bids.owner_id' in 'where clause' – Justin Jun 29 '13 at 09:31
you said on your schema that field exists. please update the question with the output from `DESCRIBE TABLE auctions_bid` – DevZer0 Jun 29 '13 at 09:35
auction_bids has those columns; id, user_id(who applied), bid, auction_id owner_id(that is equal to $_SESSION['userid']) – Justin Jun 29 '13 at 09:40

Inner, outer or full join on three tables in MySQL database on linux with apache

2 Answers2