Two Columns in Subquery in Where Clause

Two columns in subquery in where clause

You can use more than one column for an IN condition:

SELECT s.period, s.year, s.amount 
FROM salaries s
where (s.year, s.period) in (select year, period from periods)

But Gordon's not exists solution is probably faster.

SQL WHERE.. IN clause multiple columns

You can make a derived table from the subquery, and join table1 to this derived table:

select * from table1 LEFT JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL

Postgres where clause over two columns from subquery

Refactor the subquery to a join.

Say you have

SELECT a, b FROM t1 WHERE (x,y) IN (SELECT x1, y1 FROM t2 WHERE ...)

which won't work. You rewrite to

SELECT a, b
FROM t1
INNER JOIN (
   -- subquery here:
   SELECT x1, y1
   FROM t2
   WHERE ...
) AS some_alias
WHERE t1.x = some_alias.x1
  AND t1.y = some_alias.y1;

Note, in PostgreSQL you shouldn't use a CTE (WITH query) for this.

You can abbreviate

WHERE t1.x = some_alias.x1
  AND t1.y = some_alias.y1;

to

WHERE (x, y) = (x1, y1)

though.

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.

MySQL WHERE multiple-column IN subquery

I Read you are not agree with JOIN, but just another way to do it.. See join with friends if it is useful to you..

SELECT `timeline`.`action`, `timeline`.`data`, `timeline`.`tlupdate`,
            u1.`id` AS ufrom_id, u1.`username` AS ufrom_username, u1.`firstname` AS ufrom_firstname, u1.`lastname` AS ufrom_lastname, u1.`picture` AS ufrom_picture,
            u2.`id` AS uto_id, u2.`username` AS uto_username, u2.`firstname` AS uto_firstname, u2.`lastname` AS uto_lastname, u2.`picture` AS uto_picture,
            m.`id` AS m_id, m.`name` AS m_name, m.`alternative_name` AS m_altname, m.`tiny_img` AS m_tiny, m.`normal_img` AS m_normal
    FROM `timeline`
    JOIN `users` u1 ON u1.`id` = `timeline`.`user_id_from`
    JOIN `users` u2 ON u2.`id` = `timeline`.`user_id_to`
    JOIN `friends` f on f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`
    JOIN `movies` m ON m.`id` = `timeline`.`movie_id`;

Update:

As you are using inner join You can this too to avoid the condition on complete resultSet.

JOIN `friends` f on ((f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`) and f.idol_id = ?)

Either you can use DISTINCT or use GROUP BY to get unique result.

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;

SELECT NOT IN with multiple columns in subquery

I would recommend not exists:

SELECT *
FROM glsltransaction t
INNER JOIN cocustomer c ON c.customerid = t.acctid
WHERE 
    ??.sltrxstate = 4
    AND ??.araccttype = 1
    AND NOT EXISTS (
        SELECT 1
        FROM arapplyitem a
        WHERE ??.sltrxid IN (a.ardoccrid, a.ardocdbid)
    )

Note that I changed the table aliases to things that are more meaningful. I would strongly recommend prefixing the column names with the table they belong to, so the query is unambiguous - in absence of any indication, I represented this as ?? in the query.

IN sometimes optimize poorly. There are situations where two subqueries are more efficient:

SELECT *
FROM glsltransaction t
INNER JOIN cocustomer c ON c.customerid = t.acctid
WHERE 
    ??.sltrxstate = 4
    AND ??.araccttype = 1
    AND NOT EXISTS (
        SELECT 1
        FROM arapplyitem a
        WHERE ??.sltrxid = a.ardoccrid
    )
    AND NOT EXISTS (
        SELECT 1
        FROM arapplyitem a
        WHERE ??.sltrxid = a.ardocdbid
    )

Subquery with multiple columns in where clause

I think this is what you intend:

SELECT m.*
FROM message m
WHERE (m.expiration > 'CURRENT_TIMESTAMP') OR
      m.timestamp = (SELECT MAX(timestamp)
                     FROM message m2
                     WHERE m2.interlocutor_id = m.interlocutor_id
                    );

Also, when you say 'CURRENT_TIMESTAMP', do you mean something like datetime('now', 'localtime').

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