Emulate an Intersect in MySQL w/ multiple tables and Join

49
October 25, 2019, at 12:40 PM

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)

Answer 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.

Answer 2

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; 
READ ALSO
Write Pandas DataFrame into a existing MySQL Database Table

Write Pandas DataFrame into a existing MySQL Database Table

I have created a database using phpmyadmin called test that has one table called client_infoThe table in that database is empty (as shown in the attached image)

28
SIGSEV signal recieved printing a result in C with the MySQL API

SIGSEV signal recieved printing a result in C with the MySQL API

After having spent hours on my own trying to solve the problem and asked programming teachers in my university, I'm coming here hoping to find some help

57
Cannot connect to localhost mysql database from mobile - SocketTimeoutException

Cannot connect to localhost mysql database from mobile - SocketTimeoutException

I am trying to connect to my local mysql database (wamp server) from my android device

54
mysqldump not importing all tables in dump file

mysqldump not importing all tables in dump file

Today I faced strange error on mysql databasesI had a replication server suddenly my replication server crashed and thatswhy I installed mysql to new server then I installed mysql

30