Codd Codd - 5 months ago 8
SQL Question

MySql query on multiple tables with group by

I have 4 tables as described below and I am trying the fetch all lots in bids table while I am getting all the records in auction lots table.

The query that I tried is also given below along with the db structure that is being used.

Auctions (id, name)

id | name
-----------
1 | auction1
2 | auction2


Auction_lots (id, name, auction_id)

id | name | auction_id
---------------------------------
1 | lot1 | 2
2 | lot2 | 2
3 | lot3 | 2
4 | lot4 | 2
5 | lot5 | 2


Bids (id, user_id, auction_id, lot_id)

id | user_id | auction_id | lot_id
--------------------------------------------
1 | 1 | 2 | 5
2 | 1 | 2 | 3
3 | 1 | 2 | 1
4 | 1 | 2 | 2


Users(id, name)

id | name
-----------
1 | user1
2 | user2


I need all auction_lots where user placed bids with auction name, lot name and user details

I tried

select PB.*, U.*, A.name as auc_name, AL.name as lot_name
from auctions as A INNER JOIN
auction_lots as AL
ON A.id = AL.auction_id RIGHT JOIN
phone_bids as PB
ON AL.auction_id = PB.auction_id INNER JOIN
users as U
ON PB.user_id = U.id
WHERE U.id =".$user_id."
GROUP BY lot_name


I just need lot in bids table while getting all lots

Answer

REMOVE RIGHT JOIN AND GROUP BY YOU WILL GET WHAT YOU WANT

select 
        PB.*, U.*, A.name as auc_name, AL.name as lot_name

    from
        auctions as A

    INNER JOIN
        auction_lots as AL
        ON
        A.id = AL.auction_id

   INNER JOIN
        phone_bids as PB
        ON
        AL.auction_id = PB.auction_id

    INNER JOIN
        users as U
        ON
        PB.user_id =U.id
    WHERE
        U.id =".$user_id."