CODEWITHSUNDEEP
mysqlsqlgroupwise-maximum
Ewomazino Ukah
Ewomazino Ukah

Reputation: 2366

How to return only one row from the right-most table using mysql join

I have two tables. I want to join them in a way that only one record(the first matched record) in the right table is returned for the record in the left most table. the two tables have a one to many relationship. here are the tables below:

events table :

      -------------------------------------------------------------
      | event_id | event_name |event_details|event_venue|dress_code|
      -------------------------------------------------------------
      | 1        | club 92    |blah blahblah|somewhere  |something |
      | 2        | graduation |blah blahblah|somewhere  |something |
      | 3        | party      |blah blahblah|somewhere  |something |
      --------------------------------------------------------------

tickets table :

      -----------------------------------------------
      |ticket_id | ticket_name  | price | event_id  |
      -----------------------------------------------
      | 1        | first        |   0   |   1       |
      | 2        | second       |   10  |   1       |
      | 3        | third        |   100 |   1       |
      | 4        | fourth       |   10  |   2       |
      | 5        | fifth        |   200 |   2       |
      -----------------------------------------------

is this possible using join ? i'm open to any other ideas

Upvotes: 2

Views: 2889

Answers (4)

You Old Fool
You Old Fool

Reputation: 22941

These are always fun and the problem is generally referred to as group-wise maximum which you can also read more about on dev.mysql.com.

One approach is to use a derived table like this:

SELECT *
FROM events e
LEFT JOIN
(
    SELECT event_id, MIN(ticket_id) AS ticket_id
    FROM tickets
    GROUP BY event_id
) t2 USING (event_id)
LEFT JOIN tickets t USING (ticket_id)

Note that this will return a single result for each event_id whether or not any corresponding rows are found in the tickets table. By contrast, Gordon Linoff's answer will only return events which have at least one corresponding ticket.

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

Or, slower, using only join

SELECT e.*,t.*
  FROM events e
  JOIN tickets t
    ON t.event_id = e.event_id
  LEFT 
  JOIN tickets x
    ON x.event_id = t.event_id
   AND x.ticket_id > t.ticket_id
 WHERE x.ticket_id IS NULL

Upvotes: 0

marchocolate
marchocolate

Reputation: 53

This is possible using TOP 1

SELECT TOP 1 *
FROM Events e
INNER JOIN Tickets t
ON e.event_id = t.event_id
ORDER BY t.ticket_id

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You can't do this just with a join. Here is one method:

select e.*, t.*
from events e join
     tickets t
     on e.event_id = t.event_id
where t.id = (select min(t2.id)
              from tickets t2
              where t2.event_id = t.event_id
             );

Upvotes: 3

Related Questions