CODEWITHSUNDEEP
phpsql
Sam J.
Sam J.

Reputation: 353

Select from two tables PHP

This should be a very simple code, but I can't figure it out. Basically I want to JOIN two tables. One of the tables has the name of the user and the other table has all the orders that that user submitted. I want a list of all the users, ORDERED by the user who has submitted the most orders. Is this possible using JOIN? 

SELECT COUNT(table1.Orders)
        , table2.Name 
    FROM table1 
        LEFT JOIN table2 ON table1.IDName = table1.IDName 
ORDER BY COUNT(table1.Orders)

This is the code I have right now. Thanks for taking a look at this, I look forward to hearing your responses.

Upvotes: 0

Views: 91

Answers (2)

Will Marcouiller
Will Marcouiller

Reputation: 24132

My best guess from the top of my head would be that you try to join both tables on the same table and field, which doesn't make sense, if I understand correctly. How could you join results from two tables if you only use only field from a single table? Why write: table1.IDName = table1.IDName? I believe you shall write table2.IDName = table1.IDName.

Instead of:

SELECT COUNT(table1.Orders)
        , table2.Name 
    FROM table1 
        LEFT JOIN table2 ON tabel1.IDName = table1.IDName 
    ORDER BY COUNT(table1.Orders)

You shall write:

SELECT COUNT(table1.Orders)
        , table2.Name 
    FROM table1 
        LEFT JOIN table2 ON tabel2.IDName = table1.IDName 
    ORDER BY COUNT(table1.Orders)

Using the GROUP BY clause as suggested by Jocelyn is also a key point to verify.

Upvotes: 1

Jocelyn
Jocelyn

Reputation: 11393

You probably need to use GROUP BY and sort by decreasing order. Your JOIN is wrong, I replaced table1.IDName with table2.IDName:

SELECT COUNT(table1.Orders) AS nb, table2.Name
FROM table1
LEFT JOIN table2 ON table1.IDName = table2.IDName
GROUP BY table.Name
ORDER BY nb DESC

Upvotes: 2

Related Questions