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;
Implementing follower system using a many to many field through a model
IBM Watson Assistant Importing error “should NOT be shorter than 1 characters.”
How can I extract values from a dataframe or filter based on some criteria in Python?
Opencart 3.0.3.2 - Grabbing Multidimensional Array on Ajax + Twig
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)
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
I am trying to connect to my local mysql database (wamp server) from my android device
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