I have two tables, meetings and tasks. In the meetings table I have meeting
information and a meeting index called id. In the tasks table I have task
information that is associated with various meetings. In the tasks table I
have a meetingid column that corresponds with the id in the meetings table.
There is also a boolean column called complete which marks the task complete
or not.
What I want to do is display only the meetings for which there are no tasks,
and for which all tasks have been completed. To show the meetings with no
tasks here's what I worked up:
SELECT meetings.* FROM meetings LEFT JOIN tasks ON
meetings.id=tas ks.meetingid WHERE tasks.meetingid IS NULL
But that shows only the meetings for which there are no tasks. Ideally I
would have something like:
SELECT * FROM meetings WHERE id NOT IN (SELECT meetingid FROM tasks WHERE
complete=0)
Which I believe will return a list of meetings for which there are either no
tasks or for which all tasks are complete (complete=1). But it seems that
MySQL 4.1a (which I'm running) doesn't support subqueries as part of an IN.
Is there any way to achieve the same result without using the IN (subquery)?
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw
information and a meeting index called id. In the tasks table I have task
information that is associated with various meetings. In the tasks table I
have a meetingid column that corresponds with the id in the meetings table.
There is also a boolean column called complete which marks the task complete
or not.
What I want to do is display only the meetings for which there are no tasks,
and for which all tasks have been completed. To show the meetings with no
tasks here's what I worked up:
SELECT meetings.* FROM meetings LEFT JOIN tasks ON
meetings.id=tas ks.meetingid WHERE tasks.meetingid IS NULL
But that shows only the meetings for which there are no tasks. Ideally I
would have something like:
SELECT * FROM meetings WHERE id NOT IN (SELECT meetingid FROM tasks WHERE
complete=0)
Which I believe will return a list of meetings for which there are either no
tasks or for which all tasks are complete (complete=1). But it seems that
MySQL 4.1a (which I'm running) doesn't support subqueries as part of an IN.
Is there any way to achieve the same result without using the IN (subquery)?
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw