CODEWITHSUNDEEP
phpmysql
Twodogstar
Twodogstar

Reputation: 13

MySQL count and join to return ordered list

I'm having trouble with a join. I've looked at several answers here and just can't seem to apply it to my particular problem.

I have two tables:

A likes table with id, user_IP and item_id columns.

An auctions table with many columns including id, title, subtitle and location. The id column in the auctions table refers to the item_id in the likes table.

I am able to get a sorted list of the likes ordered by the item_id count with the following code:


    SELECT likes.item_id, count(likes.item_id) 
    FROM likes 
    GROUP by likes.item_id
    ORDER by  count(likes.item_id) DESC

What I need is to have the list display all the auctions(title, subtitle and location of each) in order by most liked. I've tried a few things I've found here, such as the code below which gives a syntax error, and I have been unable to produce the output I need. 

    SELECT likes.item_id, count(likes.item_id), pqc9_auctions.*
    JOIN likes 
    WHERE likes.item_id = pqc9_auctions.id 
    GROUP by likes.item_id
    ORDER by  count(likes.item_id) DESC

I think it is most likely very simple but I've been going cross eyed for the last few hours over this. Any help would be greatly appreciated.

Upvotes: 1

Views: 51

Answers (1)

Machavity
Machavity

Reputation: 31644

If you need a simple JOIN just be sure to spell out the relationship

SELECT likes.item_id, count(likes.item_id), auctions.*
FROM auctions 
    INNER JOIN likes ON likes.item_id = auctions.id
GROUP by likes.item_id
ORDER by  count(likes.item_id) DESC

Upvotes: 1

Related Questions