ECTED]; [EMAIL PROTECTED]
Subject: Re: Select with join query question
Richard Bolen wrote:
This works! I was then wondering how to get the total number of all
jobs that this condition is true for?
Just include count(distinct j.jobid) in the SELECT list.
--
MySQL General Mailing Lis
-Original Message-
From: Bruce Feist [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 7:42 PM
To: Richard Bolen
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Select with join query question
Richard Bolen wrote:
>This works! I was then wondering how to get the total numbe
Richard Bolen wrote:
This works! I was then wondering how to get the total number of all
jobs that this condition is true for?
Just include count(distinct j.jobid) in the SELECT list.
Bruce
select j.*
FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /*
all selected columns
: Select with join query question
[snip]
> Rich's solution, which I edited out, was a good one. But, if you
> really
> want to do it with a single JOIN, try this:
>
> select j.*
> FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /*
> all selected columns
[snip]
> Rich's solution, which I edited out, was a good one. But, if you really
> want to do it with a single JOIN, try this:
>
> select j.*
> FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid
> GROUP BY /* all selected columns */
> HAVING min(abs(s.status - 1)) > 0
>
> I leave it as an
I was wondering if you were going to come back with that. Your going to
need to using grouping then. Something like this should do it:
SELECT *,SUM(s.status) AS ActiveJob FROM Jobs AS j LEFT JOIN
Submissions AS
s ON j.job_id=s.job_id
WHERE ActiveJob<1 OR ActiveJob IS NULL
GROUP BY j.job_id
I'm
[EMAIL PROTECTED] wrote:
From: "Richard Bolen" <[EMAIL PROTECTED]>
Date: 2003/07/28 Mon PM 01:37:27 CDT
I'm trying to write a select query that involves 2 tables. One table
(Submissions) has a one to many relationship with the other table
(Jobs). I'm trying to find all the records in Jobs that d
>
> From: "Richard Bolen" <[EMAIL PROTECTED]>
> Date: 2003/07/28 Mon PM 01:37:27 CDT
> To: <[EMAIL PROTECTED]>
> Subject: Select with join query question
>
> I'm trying to write a select query that involves 2 tables. One table
> (Submissions) has a one to many relationship with the other table
I think this gets me all the Jobs that have no submissions but I'm
really looking for any job that doesn't have a submission with a status
of 1. That means I need Jobs that don't have submissions plus jobs with
submissions with exclusively non-1 statuses.
The problem is when a job has more than
Something like this should work. You want to do a left join on Jobs so
you don't filter out those without submission matches. The resulting
left join will have a value of NULL for any fields joined from
Submissions that don't have a match in Jobs. Just include at least on
field from Submissions
10 matches
Mail list logo
