Emulate an Intersect in MySQL w/ multiple tables and Join
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)
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.
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;
-
3:20
-
3:10
Implementing follower system using a many to many field through a model
-
3:00
-
2:40
IBM Watson Assistant Importing error “should NOT be shorter than 1 characters.”
-
2:30
How can I extract values from a dataframe or filter based on some criteria in Python?
-
2:20
Opencart 3.0.3.2 - Grabbing Multidimensional Array on Ajax + Twig
-
2:10
-
2:00
-
1:50
- Adding methods to es6 child class 19176 visits
- search for elements in a list 18228 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 15656 visits
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 14191 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 13726 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 11372 visits
- Conflict: Multiple assets emit to the same filename 8429 visits
