CODEWITHSUNDEEP
phpmysqlsqlselect
Sam
Sam

Reputation: 51

Subquery returning more than 1 row

Hi what I'm trying to do is obtain some customer details from one table and also all of their items in another table through one query. The current issue with my subquery is that it will return more than 1 row and cause an error. Is there a way around this or should I use 2 separate queries?

I'd like to use PDO fetchAll() on the query to return their first and last name and a subarray containing all of the items.

So the results would be accessible by $result['First Name'], $result['First Name'], $result['product'][0], $result['product'][1], $result['product'][3] etc.

Current statement:

SELECT `First Name`, `Last Name`, 

(SELECT `items`.`Product Name` from items 
inner join customers on `customers`.`Customer No` = `items`.`Customer No` 
WHERE `customers`.`Customer No` = '6') 

AS product from customers where `Customer No` = '6'

Upvotes: 1

Views: 1504

Answers (3)

mrmryb
mrmryb

Reputation: 1509

I don't think it's possible to return an array within one row, but what you can do is use group_concat to join the values in to a string which you can then explode later on:

SELECT `customers`.`First Name`, `customers`.`Last Name`,
GROUP_CONCAT(`items`.`Product Name` SEPARATOR '$^$') AS `Products` 
FROM customers 
JOIN items 
ON `customers`.`Customer No` = `items`.`Customer No` 
WHERE `customers`.`Customer No` = 6 
GROUP BY `Customer No`

$products=explode("$^$",$result[0]['products']);

The default separator for group_concat is , which can be quite dangerous when using explode so we use SEPARATOR '$^$' to add a bunch of random characters that are not likely to turn up instead.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108480

If you want to return just one row, you can add a LIMIT 1. But I don't think that's what you want, though.

If you want to return all items associated with a customer, you can use a LEFT JOIN, like this:

SELECT c.`First Name`
     , c.`Last Name`
     , i.`Product Name` AS product
  FROM customers c
  LEFT
  JOIN items i
    ON i.`Customer No` = c.`Customer No`
 WHERE c.`Customer No` = '6'

NOTE: the LEFT JOIN will return all rows from customers, along with any matching rows from items. If there is a customer that does not have any rows in the items tale, the product column will be NULL. If you want to exclude customers that don't have any items (if you don't want to get a row back), then remove the LEFT keyword. The absence of the LEFT keyword is equivalent to specifying the unnecessary keyword INNER).

To get a single row back from the customers table, along with just one related item, then your approach using a subquery in the SELECT list can be effective.

But you don't need to do a JOIN operation in that subquery, you can reference values from the outer query (make it a correlated subquery). The key here though is adding a LIMIT 1 to the subquery to ensure that it returns no more than 1 row.

SELECT `First Name`
     , `Last Name`
     , ( SELECT `items`.`Product Name` 
           FROM `items`
          WHERE `items`.`Customer No` = `customers`.`Customer No`
          ORDER BY `items`.`Product Name` 
          LIMIT 1
       ) AS product  
  FROM customers 
 WHERE `Customer No` = '6'

The ORDER BY isn't necessary, but it makes the resultset deterministic. That is, absent the ORDER BY, MySQL is free to return any one of the rows that satisfy the predicate. And subsequent runs could return a different result. There's nothing wrong with that, but it can be unexpected if you aren't expecting it.

Upvotes: 1

John Woo
John Woo

Reputation: 263843

try joining them,

SELECT  a.`First Name`, 
        a.`Last Name`, 
        b.`Product Name`
FROM    customers a
        INNER JOIN items b
            ON a.`Customer No` = b.`Customer No` 
WHERE   a.`Customer No` = '6'

This query will return only customers that has atleast one match on table item due to the type of join define: INNER JOIN. However, if you still want records of customer to show/retrieve whether it has a matching value or not on table Items, change INNER JOIN into LEFT JOIN (an OUTER join)

To learn more about joins, please visit the article below,

Upvotes: 1

Related Questions