Emulate an Intersect in MySQL w/ multiple tables and Join

Question

I need to emulate an Intersect query in MySQL.

    SELECT c.cust_code, cust_fname, cust_lname  
      FROM lgcustomer c JOIN lginvoice i ON c.cust_code = i.cust_code 
      WHERE employee_id = 83649 
    INTERSECT 
    SELECT c.cust_code, cust_fname, cust_lname  
      FROM lgcustomer c JOIN lginvoice i ON c.cust_code = i.cust_code  
      WHERE employee_id = 83677 
    ORDER BY cust_lname, cust_fname;

I have tried to use EXISTS but have not been successful. I think I am joining wrong?

    SELECT C.CUST_CODE, CUST_FNAME, CUST_LNAME
      FROM LGCUSTOMER C JOIN LGINVOICE I ON C.CUST_CODE = I.CUST_CODE
      WHERE EMPLOYEE_ID = 83649
      AND EXISTS (SELECT * 
                 FROM LGCUSTOMER C JOIN LGINVOICE I ON C.CUST_CODE = I.CUST_CODE
                 WHERE EMPLOYEE_ID = 83677)
    ORDER BY CUST_LNAME, CUST_FNAME;

The tables are:

+-------------+
| Customer |
+-------------+
| Cust_code |
| Cust_Fname |
| Cust_Lname |
+-------------+

and
+--------------+
| LGINVOICE |
+--------------+
| Cust_code |
| Employee_ID |
+--------------+

And I need a query to pull all the Customer codes that have an invoice from two unique Employees (83649 and 83677)

Can you clarify what you want to do? Are your question the last paragraph? — bjornasm, Oct 24 '19 at 20:14

score 1 · Accepted Answer · answered Oct 24 '19 at 20:00

1

Hmmm . . . aggregation comes to mind:

SELECT c.cust_code, cust_fname, cust_lname  
FROM lgcustomer c JOIN
     lginvoice i
     ON c.cust_code = i.cust_code 
WHERE employee_id IN ( 83649, 83677 )
GROUP BY c.cust_code, cust_fname, cust_lname 
HAVING COUNT(DISTINCT employee_id) = 2;

This also removes duplicates, as does INTERSECT.

answered Oct 24 '19 at 20:00

Gordon Linoff

1,242,037
58
646
786

Yes this works perfectly! I am still learning SQL, thanks for the help! – Sundale Citizen Oct 24 '19 at 20:28

score 0 · Answer 2 · answered Oct 24 '19 at 20:01

You could emulate a intersect using the inner joij between the two subqueries

select * from  (
  SELECT c.cust_code, cust_fname, cust_lname  
  FROM lgcustomer c 
  JOIN lginvoice i ON c.cust_code = i.cust_code 
  WHERE employee_id = 83649 
) t1 
INNER JOIN  (
  SELECT c.cust_code, cust_fname, cust_lname  
  FROM lgcustomer c 
  JOIN lginvoice i ON c.cust_code = i.cust_code  
  WHERE employee_id = 83677 
) t2 on t1.cust_code = t2.cust_code 
ORDER BY cust_lname, cust_fname;

Emulate an Intersect in MySQL w/ multiple tables and Join

2 Answers2