CODEWITHSUNDEEP
mysqljoin
devinpleuler
devinpleuler

Reputation: 163

Joining only specific rows

I am looking for an SQL query that will return a 1 or a zero depending on a joined row's content.

Two Tables:

Events:

Qualifiers:

Each event can have multiple qualifiers.

The query that I currently have is:

select event_id, if(qualifier_type = 15, 1, 0)
from events 
join qualifiers q using (event_id)
where q.qualifier_type = 15;

In a perfect world, I would nicely normalize these tables and put the different qualifiers in with the events, but this isn't possible at the moment.

Edit: Currently, this query only returns rows that have the association. Instead, I need to return all rows and an extra column specifying if this association exists.

Upvotes: 0

Views: 1525

Answers (3)

M Afifi
M Afifi

Reputation: 4795

Case
    When qualifier_type = 15 then 0
    Else 1
End

More here, http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753675

SELECT e.event_id, COUNT(q.qualifier_type) AS needs_qualtype_15
  FROM events AS e
  LEFT JOIN qualifiers AS q ON q.event_id = e.event_id AND q.qualifier_type = 15
 GROUP BY e.event_id

This gives you a list of all events with an indication of whether the qualifier type 15 is associated with the event.

Another way of writing the same thing is:

SELECT e.event_id, COUNT(q.qualifier_type) AS needs_qualtype_15
  FROM events AS e
  LEFT JOIN
       (SELECT event_id, qualifier_type
          FROM qualifiers WHERE qualifier_type = 15
       ) AS q
    ON q.event_id = e.event_id
 GROUP BY e.event_id;

Both formulations rely on COUNT(expr) only counting non-null values. For the events with a matching qualifier, the COUNT is 1; for the events without, it is 0. The first version compresses the query by including the condition on qualifier type into the ON join condition. The second version is clearer, but more verbose. The chances are that a good optimizer will execute both queries the same way.

Upvotes: 2

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

Not completely clear what you're after, but try this:

SELECT DISTINCT
   event_id, 
   CASE WHEN qualifier_type IS NULL THEN  0 ELSE 1 END AS [ColumnName]
FROM events 
LEFT JOIN qualifiers q 
WHERE q.qualifier_type = 15;

Upvotes: 0

Related Questions