NON Equi Join on more than 2 tables

Question

question

UfuckVT asked Dec 20, '22

NON Equi Join on more than 2 tables

I'm attempting to get only those records from the left table that are not in the right. In the left table, I have about 5000 records. Similarly, the same process must be done on more than three or four tables. I need to find records from the first table that aren't in the other 3-4 tables. They are using the same main or foreign key idea.

My first effort, using two tables, yielded 5,70,000 records from the initial 5k data. I've read other articles like this on scaler topics but couldn't quite get it.

SELECT m.* FROM members m, pinnumber p where p.pinmemberid != m.memberid

My second try caused my-sql browser to hang as well.

SELECT m.* FROM members m
LEFT JOIN pinnumber p ON p.pinmemberid != m.memberid
LEFT JOIN customer c ON m.memberid != c.memberid

My third try is likewise making a lot of time

SELECT * FROM members m
         WHERE 1=1 AND AND not exists ( select 1 from pinnumber p where 1=1 And
         p.pinmemberid = m.memberid AND p.pinproductid LIKE '%Remit%')
         AND not exists ( select 1 from customer c where 1=1 and c.card_name is not null AND m.memberid = c.memberid )

Please advise me on what to do with this. If I need to put non-equi join on this.

mysql sqlserver2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2022-12-20T09:01:30Z

anthony.green answered Dec 20, '22

So you need to set your joins to = and not !=, then you need to filter out where the value is null in the right table.

SELECT m.* FROM members m
LEFT JOIN pinnumber p ON m.memberid = p.pinmemberid
WHERE p.pinmemberid IS NULL

So here match everyone based on their memberid, and then only show me those which do not have a value in the pinnumber table.
You can then expand this further and add in the additional tables you need etc

SELECT m.memberid, ISNULL(p.pinmemberid,'No PIN Number data'), ISNULL (c.memberid, 'No Customer Data')
FROM members m
LEFT JOIN pinnumber p ON m.memberid = p.pinmemberid
LEFT JOIN customer c ON m.memberid = c.memberid
WHERE p.pinmemberid IS NULL 
OR c.memberid IS NULL

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question