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.

• 1st query:

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

• 2nd query:

SELECT name FROM clients WHERE id=:client_id

• What would the 2nd query look like as a subquery into the first in order to display final results like the following, adding the name from the clients table based on each client_id from orders table matching id from the clients table:

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

CodePudding user response：

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;

CodePudding user response：

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