Mysql query, select 2 databases with 4 tables + nested SELECT?

Question

This is my current query

$sel = "SELECT 
  db1t1.userid, db1t1.customer_id, db2t1.customers_id, db2t1.orders_id, db2t2.products_price
 FROM 
  database1.table1 db1t1
  LEFT JOIN database2.table1 db2t1 ON
   db1t1.customer_id = db2t1.customers_id
  LEFT JOIN database2.table2 db2t2 ON
   db2t1.orders_id = db2t2.orders_id 
 WHERE db1t1.userid IN(
  SELECT 
   l.userid, l.username, r.username, r.cus_id 
  FROM 
   database1.table3 l
   LEFT JOIN database2.table4 r ON
    l.username = r.username
  WHERE r.cus_id = '1234'
  )";

Error message:

Operand should contain 1 column(s)

Answer 1

The error occurred because that you returned a result with multiple columns to an IN clause.

Try this:

SELECT 
    `db1t1`.`userid`, `db1t1`.`customer_id`, `db2t1`.`customers_id`,
    `db2t1`.`orders_id`, `db2t2`.`products_price`
FROM `database1`.`table1` AS `db1t1`
    LEFT JOIN `database2`.`table1` AS `db2t1` 
     USING (`customers_id`)
    LEFT JOIN `database2`.`table2` AS `db2t2`
     USING (`orders_id`)  
WHERE `db1t1`.`userid` IN (
    SELECT `l`.`userid`
    FROM `database1`.`table3` AS `l`
     LEFT JOIN `database2`.`table4` AS `r`
      USING (`username`)
    WHERE `r`.`cus_id` = 1234
)

What are you trying to achieve ? Maybe we can find a better solution.

Also, I think that you should do an INNER JOIN instead of a LEFT JOIN in the subquery.