CODEWITHSUNDEEP
mysqlsql
exclusivebiz
exclusivebiz

Reputation: 35

MySQL INNER JOIN query for multiple matches except if

I have a table that looks something like this:

tableid memberid    date
1       +123456     2013-12-04 9:00:00
1       9876        2013-12-04 10:44:00
1       8765        2013-12-04 11:15:00
1       6900        2013-12-04 12:20:00
1       7654        2013-12-04 15:20:00
1       6250        2013-12-04 15:20:00
1       5655        2013-12-04 15:25:00
2       +145879     2013-12-04 15:50:00
2       5668        2013-12-04 16:12:00
2       3654        2013-12-04 17:25:00 
2       6250        2013-12-04 19:02:00
2       3447        2013-12-04 19:33:00

I used the below query to obtain multiple matches:

SELECT *
FROM connector AS c1
INNER JOIN connector AS c2 ON c1.tableid=c2.tableid
INNER JOIN connector AS c3 ON c1.tableid=c3.tableid

WHERE
    (c1.memberid LIKE '+1%')
    AND (c2.memberID LIKE '56%')
    AND (c3.memberID = 6250)
    AND  c2.date BETWEEN '2013-12-04 0:00:00' and '2013-12-04 23:59:59'

This worked initially as the above code output the following:

tableid memberid    date                    tableid     memberid    date                    tableid memberid    date
1       +123456     2013-12-04 9:00:00      1           5655        2013-12-04 15:25:00     1       6250        2013-12-04 15:20:00
2       +145879     2013-12-04 15:50:00     2           5668        2013-12-04 16:12:00     2       6250        2013-12-04 19:02:00

What I need is to omit any groups of tableids that contain a memberid of 6900.

Therefore the output should only be the following:

tableid memberid    date                tableid memberid    date                tableid memberid    date
2       +145879     2013-12-04 15:50:00 2       5668        2013-12-04 16:12:00 2       6250        2013-12-04 19:02:00

As tableid 1 has 6900 in the memberid column.

Would appreciate your help.

Upvotes: 0

Views: 69

Answers (2)

StevieG
StevieG

Reputation: 8709

SELECT *
FROM connector AS c1
INNER JOIN connector AS c2 ON c1.tableid=c2.tableid
INNER JOIN connector AS c3 ON c1.tableid=c3.tableid

WHERE
    (c1.memberid LIKE '+1%')
    AND (c2.memberID LIKE '56%')
    AND (c3.memberID = 6250)
    AND  c2.date BETWEEN '2013-12-04 0:00:00' and '2013-12-04 23:59:59'
    AND NOT EXISTS (SELECT 1 FROM connector c4 WHERE c1.tableid=c4.tableid AND c4.memberid = 6900)

Upvotes: 1

Minoru
Minoru

Reputation: 1730

Just add this to the end of the code:

... AND c1.tableid NOT IN (SELECT tableid FROM connector WHERE memberid = 6900);

Upvotes: 0

Related Questions