Home > Archive > PHP SQL > March 2005 > SQL query help again
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL query help again
|
|
| John McGough 2005-03-07, 8:57 pm |
| This time i need help with the following:-
SELECT Job.ID FROM Job WHERE (Job.Client=1 AND NOT EXISTS(SELECT Work.JobID
FROM Work GROUP BY Work.JobID))
In this case I want it to return Jobs which have not been started (eg don't
have a comment in the work table) and belong to a certain client.
It should return ID=4 but it doesn't. I'm using MySQL. MINUS does not seem
to be supported, so i've tried using NOT EXISTS but that doesn't return the
correct data.
Job:-
+---+------------+------+
| ID | Description | Client |
+---+------------+------+
| 1 | ... | 1 |
| 2 | ... | 2 |
| 3 | ... | 1 |
| 4 | ... | 1 |
+---+------------+------+
Work:-
+---+-------+--------+---------+----------+
| ID | JobID | UserID | Finished | Comment |
+---+-------+--------+---------+----------+
| 1 | 1 | user1 | 0 | ... |
| 2 | 1 | user1 | 1 | ... |
| 3 | 2 | user2 | 0 | ... |
| 4 | 3 | user1 | 0 | ... |
| 5 | 2 | user2 | 0 | ... |
| 6 | 2 | user1 | 1 | ... |
| 7 | 3 | user1 | 0 | ... |
+---+-------+--------+---------+----------+
| |
| no@emails.thx 2005-03-08, 8:56 am |
| On Mon, 7 Mar 2005 19:54:34 -0000, "John McGough"
<goff@funkster.org.uk> wrote:
>This time i need help with the following:-
>
>SELECT Job.ID FROM Job WHERE (Job.Client=1 AND NOT EXISTS(SELECT Work.JobID
>FROM Work GROUP BY Work.JobID))
>
>In this case I want it to return Jobs which have not been started (eg don't
>have a comment in the work table) and belong to a certain client.
>It should return ID=4 but it doesn't.
>Job:-
>+---+------------+------+
> | ID | Description | Client |
>+---+------------+------+
> | 1 | ... | 1 |
> | 2 | ... | 2 |
> | 3 | ... | 1 |
> | 4 | ... | 1 |
>+---+------------+------+
>
>Work:-
>+---+-------+--------+---------+----------+
> | ID | JobID | UserID | Finished | Comment |
>+---+-------+--------+---------+----------+
> | 1 | 1 | user1 | 0 | ... |
> | 2 | 1 | user1 | 1 | ... |
> | 3 | 2 | user2 | 0 | ... |
> | 4 | 3 | user1 | 0 | ... |
> | 5 | 2 | user2 | 0 | ... |
> | 6 | 2 | user1 | 1 | ... |
> | 7 | 3 | user1 | 0 | ... |
>+---+-------+--------+---------+----------+
>
Try this:
SELECT Jobs.idjobs, Count(Work.jobid) cnt FROM Jobs LEFT JOIN Work ON
Jobs.idjobs=Work.jobid GROUP BY Jobs.idjobs HAVING cnt=0
Chris
| |
| no@emails.thx 2005-03-08, 8:56 am |
| Or... (with your field names):
SELECT Jobs.ID, Count(Work.JobID) cnt FROM Jobs LEFT JOIN Work ON
Jobs.ID=Work.JobID GROUP BY Jobs.ID HAVING cnt=0
Chris
|
|
|
|
|