CODEWITHSUNDEEP
sql
Chuck Le Butt
Chuck Le Butt

Reputation: 48798

Distinct user details across different tables

I have an unusual database request that I've never encountered before. Maybe it's very simple, or maybe it's not possible. I'm unsure!

Basically, boiled down to the simplest terms, I have four tables:

I want a list of all users who purchased tickets to specific events. Sounds simple, but consider the following structure:

enter image description here

Note: People can be in the users table and not have purchased any tickets.

Here is what I've attempted with my limited SQL-fu, but obviously it's not going to work:

select distinct * from `users` 
   inner join `ticket_provider_1` 
      on `users`.`id` = `ticket_provider_1`.`user_id` 
   inner join `ticket_provider_2` 
      on `users`.`id` = `ticket_provider_2`.`user_id` 
   inner join `events` as `tp1_events` 
      on `events`.`id` = `ticket_provider_1`.`event_id` 
   inner join `events` as `tp2_events` 
      on `events`.`id` = `ticket_provider_2`.`event_id` 
where `tp1_events`.`show_id` = 22 
    or `tp2_events`.`show_id` = 22 
order by `users`.`id` 
asc limit 10000 offset 0

What's the best way to structure this, and is such a thing even possible in one SQL query?

Upvotes: 1

Views: 26

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The simplest should be UNION 

  SELECT userID
  FROM `ticket_provider_1` t
  JOIN `events` e
    ON  t.event_id = e.id
  WHERE `show_id` = 22 
  UNION 
  SELECT userID
  FROM `ticket_provider_2` t
  JOIN `events` e
    ON  t.event_id = e.id
  WHERE `show_id` = 22

You don't even need DISTINCT because UNION remove duplicates.

If need additional user info like email then

SELECT *
FROM users
WHERE ID IN ( SELECT userID
              FROM `ticket_provider_1` t
              JOIN `events` e
                ON  event_id = e.id
              WHERE `show_id` = 22 
              UNION 
              SELECT userID
              FROM `ticket_provider_2` t
              JOIN `events` e
                ON  t.event_id = e.id
              WHERE `show_id` = 22  )

Upvotes: 1

Related Questions