CODEWITHSUNDEEP
mysql
Martijn
Martijn

Reputation: 155

Order popular items with multiple tables

I have a list A of products where in which I can get the most popular products over time. I also have a list B of products. This list will change every day.

What I want to do is display all the products of list B and order it with the popular products of list A. For example:

List A will contain:

banana
apple
banana
orange
apple
banana

List B will contain:

potato
rice
banana
bread
orange

Now the output has to be:

banana
orange
potato
rice
bread

How do I do this with a single SQL command? Thanks

Upvotes: 1

Views: 45

Answers (1)

rs.
rs.

Reputation: 27457

    SELECT B.Col1, CNT FROM LISTB B
    LEFT OUTER JOIN 
    (SELECT Col1, COUNT(*) CNT 
     FROM LISTA A GROUP BY Col1) A1 ON b.col1 = A1.col1
    ORDER BY CNT DESC

Check this - http://sqlfiddle.com/#!3/34f5f/1

Upvotes: 2

Related Questions