CODEWITHSUNDEEP
phpmysqlsqljoin
Владислав Щербин
Владислав Щербин

Reputation: 83

2 JOINS MYSQL and LIMIT 1

I have 3 tables and want to make a sql query to proper view them.

1.) Zakaz

id | Comment

2.) Tovar

id  | id_clients | zakaz

3.) Users

id | name

They are connected as:

zakaz.id = tovar.zakaz
tovar.id_clients = users.id

I want to see

zakaz.id | users.name

BUT I want to limit only to 1 string per zakaz, so I did:

SELECT zakaz.id, zakaz.comment, tovar.id_clients
    FROM zakaz
    JOIN tovar ON zakaz.id = tovar.zakaz
GROUP BY zakaz.id

and then I want to JOIN users but it doesnt work

SELECT zakaz.id, zakaz.comment, tovar.id_clients, users.name
    FROM zakaz
    JOIN tovar ON zakaz.id = tovar.zakaz
    GROUP BY zakaz.id
JOIN users ON users.id = tovar.id_clients

Upvotes: 2

Views: 134

Answers (1)

John Woo
John Woo

Reputation: 263733

it will display the latest id on Zakaz

SELECT  a.name, c.id
FROM    Users a
            INNER JOIN
            (
                SELECT id_clients, zakaz, MAX(id) maxID
                FROM Tovar
                GROUP BY id_clients, zakaz
            ) d 
                ON d.id_clients = a.id
            INNER JOIN Zakaz c
                ON d.zakaz = c.id

Upvotes: 3

Related Questions