Mysql: Returning Multiple Columns from an In-Line Subquery

MySQL: Returning multiple columns from an in-line subquery

Move the inline SQL to be a inner join query. So you'd have something like...

SELECT  DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth, COUNT(OrderID) AS TotalOrders, SUM(OrderTotal) AS TotalAmount,  Z.OrderCustomerFK, Z.CustomerName, z.OrderTotal as CustomerTotal   
  FROM Orders     
  INNER JOIN (SELECT DATE_FORMAT(OrderDate,'%M %Y') as Mon, OrderCustomerFK, CustomerName, SUM(OrderTotal) as OrderTotal 
                FROM Orders 
               GROUP BY  DATE_FORMAT(OrderDate,'%M %Y'), OrderCustomerFK, CustomerName ORDER BY SUM(OrderTotal) DESC LIMIT 1) Z
          ON Z.Mon = DATE_FORMAT(OrderDate,'%M %Y')
    GROUP BY DATE_FORMAT(OrderDate,'%m%y'), Z.OrderCustomerFK, Z.CustomerName
    ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC

MySQL WHERE IN subquery with multiple columns

You can JOIN classdays with your subquery but without CONCAT_WS, and use an IN condition in the ON clause:

SELECT classdays.*
FROM classdays
JOIN (
  SELECT 
    tclasses.cla_ID,
    tclasses.cla_nextclass as next1,
    tclasses_1.cla_nextclass as next2
  FROM tclasses
  LEFT JOIN tclasses AS tclasses_1
    ON tclasses.cla_nextclass = tclasses_1.cla_ID
  WHERE tclasses.cla_ID = 1234
) c ON classdays.classday_classid IN (c.cla_ID, c.next1, c.next2)

You might need SELECT DISTINCT classdays.*, if the subquery can return more than one row, or wehen the list c.cla_ID, c.next1, c.next2 can contain duplicates.

What Barmar mentioned with UNION would be

SELECT classdays.*
FROM classdays
WHERE classdays.classday_classid IN (
  SELECT tclasses.cla_ID
  FROM tclasses 
  WHERE tclasses.cla_ID = 1234

  UNION

  SELECT tclasses.cla_nextclass
  FROM tclasses
  WHERE tclasses.cla_ID = 1234

  UNION 

  SELECT tclasses_1.cla_nextclass
  FROM tclasses
  JOIN tclasses AS tclasses_1 ON tclasses.cla_nextclass = tclasses_1.cla_ID
  WHERE tclasses.cla_ID = 1234
)

Note that you don't need a JOIN for the first two UNION parts. And for last part you can use INNER JOIN instead of LEFT JOIN.

Returning multiple columns with subquery and 'where in'

You need SELF JOIN :

select sk1.Sku , sk1.SkuId, skc.qty
from Sku sk inner join 
     SkuCombo skc
     on skc.SkuId = sk.SkuId inner join 
     Sku sk1
     on sk1.SkuId = skc.SkuComboId;

MySQL sub query with multiple columns

Try this:

Select users.* 
  from users 
    join ids 
   on users.id IN(ids.id1, ids.id2)
   and ids.location = 1;

Selecting multiple columns from previous row in MySQL

Something like this will return the id of the 'previous' row.

 SELECT x.*
      , MAX(y.id) prev_id
   FROM example x
   LEFT
   JOIN example y
     ON y.id < x.id 
    AND y.b = 1
  GROUP
     BY x.id;

I'll leave the business of returning the rest of the data associated with this row as an exercise for the reader.

Solution to subquery returns more than 1 row error

= can be used when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN:

select * 
from table
where id IN (multiple row query);

For example:

SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students)   --Subquery returns only 1 value

SELECT *
FROM Students
WHERE Marks IN 
      (SELECT Marks 
       FROM Students 
       ORDER BY Marks DESC
       LIMIT 10)                       --Subquery returns 10 values

Selecting multiple columns/fields in MySQL subquery

Yes, you can do this. The knack you need is the concept that there are two ways of getting tables out of the table server. One way is ..

FROM TABLE A

The other way is

FROM (SELECT col as name1, col2 as name2 FROM ...) B

Notice that the select clause and the parentheses around it are a table, a virtual table.

So, using your second code example (I am guessing at the columns you are hoping to retrieve here):

SELECT a.attr, b.id, b.trans, b.lang
FROM attribute a
JOIN (
 SELECT at.id AS id, at.translation AS trans, at.language AS lang, a.attribute
 FROM attributeTranslation at
) b ON (a.id = b.attribute AND b.lang = 1)

Notice that your real table attribute is the first table in this join, and that this virtual table I've called b is the second table.

This technique comes in especially handy when the virtual table is a summary table of some kind. e.g.

SELECT a.attr, b.id, b.trans, b.lang, c.langcount
FROM attribute a
JOIN (
 SELECT at.id AS id, at.translation AS trans, at.language AS lang, at.attribute
 FROM attributeTranslation at
) b ON (a.id = b.attribute AND b.lang = 1)
JOIN (
 SELECT count(*) AS langcount,  at.attribute
 FROM attributeTranslation at
 GROUP BY at.attribute
) c ON (a.id = c.attribute)

See how that goes? You've generated a virtual table c containing two columns, joined it to the other two, used one of the columns for the ON clause, and returned the other as a column in your result set.

How to get multiple columns on subquery or group by

Use a correlated subquery with LIMIT 1 in the WHERE clause:

SELECT product, brand, market
FROM Product_Brand_Market pbm
WHERE (pbm.brand, pbm.market) = (
    SELECT pbm1.brand, pbm1.market
    FROM Product_Brand_Market pbm1
    WHERE pbm1.product = pbm.product
    ORDER BY pbm1.price ASC
    LIMIT 1
)

This will return only one row per product, even if there are two or many of them with the same lowest price.

Demo: http://rextester.com/UIC44628

Update:

To get all products even if they have no entries in the Product_Brand_Market table, you will need a LEFT JOIN. Note that the condition should be moved to the ON clause.

SELECT p.id as product, pbm.brand, pbm.market
FROM Product p
LEFT JOIN Product_Brand_Market pbm
  ON   pbm.product = p.id
  AND (pbm.brand, pbm.market) = (
    SELECT pbm1.brand, pbm1.market
    FROM Product_Brand_Market pbm1
    WHERE pbm1.product = pbm.product
    ORDER BY pbm1.price ASC
    LIMIT 1
  );

Demo: http://rextester.com/MGXN36725

The follwing query might make a better use of your PK for the JOIN:

SELECT p.id as product, pbm.brand, pbm.market
FROM Product p
LEFT JOIN Product_Brand_Market pbm
  ON (pbm.product, pbm.market, pbm.brand) = (
    SELECT pbm1.product, pbm1.market, pbm1.brand
    FROM Product_Brand_Market pbm1
    WHERE pbm1.product = p.id
    ORDER BY pbm1.price ASC
    LIMIT 1
  );

An index on Product_Brand_Market(product, price) should also help to improve the performance of the subquery.

---

Related Topics