INNER JOIN OR LEFT JOIN

I am querying 2 tables to get nonmatching values based on city, zip code, and fullname. Table A has 30,000 records, Table B has 2,000 records.

I tried inner join by assigning ON condition != It produces duplicates of about 3 Values to fill up the 32000 records.

'23997', 'Dr ***', '107 ***', 'Bogota', 'ks', '** bo', '*****2', '1807'
'23997', 'Dr ***', '107 ***', 'Bogota', 'ks', '** bo', '*****2', '1807'

SELECT s.id, s.Fullname, s.Address, s.city, s.Province, s.Zipcode, s.telephone, c.id
FROM scotlist s
JOIN allcustmomers c 
ON c.city != s.City AND c.Zipcode != s.Zipcode AND c.Fullname != s.Fullname
order by s.id DESC;

If I do LEFT JOIN I am sure I will get all records of Table A, but that is not what I need. What am looking for is the unmatching records from table A if they do not match Table B

mix5003

5 years ago

Level 46

i think at least 1 condition should be equals something like

SELECT s.id, s.Fullname, s.Address, s.city, s.Province, s.Zipcode, s.telephone, c.id
FROM scotlist s
JOIN allcustmomers c 
ON c.Fullname = s.Fullname AND (c.city != s.City OR c.Zipcode != s.Zipcode )
order by s.id DESC;

this mean fetch row that has same fullname but city or zip code note match

Ap3twe

5 years ago

Best Answer

Level 5

What I was looking for were records that do not match from Table A compared to Table B. I had to use a Full Outer JOIN. In MySQL, I had to use a UNION.

SELECT *
FROM   scotlist s 
LEFT JOIN allcustmomers c 
ON  s.Fullname = c.Fullname AND s.City = c.City AND s.zipcode = c.zipcode
UNION
SELECT * 
FROM scotlist s
RIGHT JOIN allcustmomers c
ON s.Fullname = c.Fullname AND s.City = c.City AND s.zipcode = c.zipcode
WHERE s.Fullname is NULL AND s.City is NULL AND s.Zipcode is NULL;

Please or to participate in this conversation.