CODEWITHSUNDEEP
mysqlsql
Sander
Sander

Reputation: 1401

Joining multiple rows

I have a problem with joining 2 tables together, as follows

Table 'jobs'
id int(20) auto_increment PK
name varchar(200)

Table 'logs'
id int(20) auto_increment PK
job_id int(20) FK(events.id)
event varchar(200)

I want all unique ID's that have a log entry for logs.event = 'initialized' AND logs.event = 'failed'

How can I do this with MySQL?

I've tried this query, but it won't work (Query succeeded, 0 results, while there are results that match in the DB)

SELECT
  a.id,
  a.name
FROM
jobs as a
RIGHT OUTER JOIN logs b ON b.job_id = a.id
WHERE
b.event = 'initialized'
AND
b.event = 'failed'

Upvotes: 1

Views: 127

Answers (5)

Taryn East
Taryn East

Reputation: 27757

Try this instead

SELECT DISTINCT
  a.id, a.name
FROM jobs as a
INNER JOIN logs b ON b.job_id = a.id
INNER JOIN logs c ON c.job_id = a.id
WHERE
  b.event = 'initialized'
  AND
  c.event = 'failed'

Basically I'm assuming that any single log can't be both "initialised" AND "failed" at the same time - so you have to join twice and test two separate log entries... ?

Edit: update with suggestions in comments.

Upvotes: 1

niktrs
niktrs

Reputation: 10056

Assuming that you want to check for both events you should inner join JOBS table twice with LOGS (one join per event). I use inner join because you want both event's to have occured.

SELECT
  a.id,
  a.name
FROM
jobs as a
 INNER JOIN logs b ON b.job_id = a.id
 INNER JOIN logs c ON c.job_id = a.id
WHERE
b.event = 'initialized'
AND
c.event = 'failed'

Upvotes: 1

Abhay
Abhay

Reputation: 6645

I will not attempt to give you a solution because I think there is nothing wrong with your query and should give the results if you say there are matching rows in your DB.

But a few points though:

  1. Are you sure you want all Job IDs that have a log entry for BOTH "initialized" and "failed" events? Perhaps you are looking for all Job IDs that have a log entry for either "initialized" or "failed" events. If the latter is what you need, try using OR instead of AND in your WHERE clause.

  2. According to your table structure logs.job_id is a FK for events.id. But in your query you are doing a join between logs.job_id and jobs.id. Is your FK incorrect or logs.job_id does not job Ids but event Ids?

On another note, why are you using a RIGHT OUTER JOIN? Does INNER JOIN not work for retrieving the same results?

Upvotes: 1

StevieG
StevieG

Reputation: 8729

Try this:

SELECT
a.id,
a.name
FROM jobs a
INNER JOIN logs b on b.jobid = a.id
WHERE
b.event = 'initialized'
AND a.id in (SELECT distinct jobid from logs WHERE event = 'failed')

Upvotes: 3

Tudor Constantin
Tudor Constantin

Reputation: 26871

SELECT
  a.id,
  a.name
FROM
jobs a
INNER JOIN logs b ON  b.job_id = a.id AND b.event = 'initialized' AND b.event = 'failed'
GROUP BY a.id

Upvotes: 0

Related Questions