CODEWITHSUNDEEP
mysql
t1f
t1f

Reputation: 3181

MySQL subquery to get a value from a table based on id from another table

I'm trying to write up a subquery in order to retrieve a client name from the clients table by using a client_id from the orders table.

SELECT client_id, order_id, deadline, state FROM orders GROUP BY order_id

SELECT name FROM clients WHERE id=:client_id

client_id - from orders table

order_id - from orders table

deadline - from orders table

state - from orders table

name - from clients table

grouped by order_id from orders table

Upvotes: 0

Views: 1046

Answers (2)

Leego Yih
Leego Yih

Reputation: 21

JOIN Clause

SELECT o.client_id,
       o.order_id,
       o.deadline,
       o.state,
       c.name
FROM orders o
         join clients c on c.id = o.client_id
GROUP BY o.order_id

GROUP BY

SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause. You'd better use this:

SELECT o.client_id,
       o.order_id,
       o.deadline,
       o.state,
       c.name
FROM orders o
         join clients c on c.id = o.client_id
GROUP BY o.client_id,
         o.order_id,
         o.deadline,
         o.state,
         c.name

Upvotes: 1

Han Parlak
Han Parlak

Reputation: 777

If I understand you correctly, you need to get second table's name value in your result set. Then you have to get values from 2 tables. Using subquery is the one solution:

SELECT o.client_id, o.order_id, o.deadline, o.state, c.name 
FROM orders o, clients c
WHERE (c.id = o.client_id)
GROUP BY o.order_id;

Upvotes: 1

Related Questions