CODEWITHSUNDEEP
mysqlsqljoin
Scott Sz
Scott Sz

Reputation: 3998

mysql how to do an inner join with a second inner join to the same table?

I have two tables, one with notes

id, note, client#, flag, employee#, date

The other with employees

id, employee#

I want to find all the notes that have a "N" flag, but DO NOT have a "Y" flag for the same date and client#, but only for the employees that are in the employee table.

For example I start with:

1  mynote   123   Y   abc   1/1/2011
2  mynote   123   N   abc   1/1/2011
3  mynote   124   N   abc   1/1/2011
4  mynote   124   N   ccc   1/1/2011

(in my employees table I have employee abc)

so I started with:

SELECT * from notes a inner join employees b on a.employee = b.employee WHERE a.flag = 'N'

That works, I get what looks like a resonable list of notes for only the employees that are currently in the employees table (for example only abc - records 2 and 3 above).

Now, I need to get back only record 3 since it is an "N" record and has no matching "Y" record for the same date and client#.

I just cant seem to get the extra part figured out to add it on.

Thanks.

Upvotes: 1

Views: 303

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53880

If you move the flag = expression to the ON clause, it becomes more simple:

SELECT * from employees e
inner join notes n1
on n1.employee = e.employee
  AND n1.flag = 'N'
inner join notes n2
ON n2.employee = e.employee
  AND n2.client = n1.client AND n2.date = n1.date AND n2.flag = 'Y'

I also borrowed the improved alias names from dasblinkenlight.

Upvotes: 0

Kurt Du Bois
Kurt Du Bois

Reputation: 7665

Try using a union for this. This way you can work with 2 different queries.

Upvotes: 1

Related Questions