Inner join returning all rows from first table and only one row from second table although there is-CodePudding

I have two tables, events and tickets and I have to retrieve future events where booking_start_date is later than today. There is also events without tickets.

Events

event_id | name         | event_date
1        | event 1 name | 2020-11-10
2        | event 2 name | 2021-11-15
3        | event 3 name | 2022-12-20
4        | event 4 name | 2023-01-10
5        | event 5 name | 2023-12-22
6        | event 6 name | 2023-11-25

Tickets

event_id | ticket_name | booking_start_date
1        | varied name | 2020-10-04
1        | varied name | 2020-10-04
2        | varied name | 2021-10-09
2        | varied name | 2021-10-09
3        | varied name | 2022-12-05
4        | varied name | 2022-12-10
4        | varied name | 2022-12-10

I have tried to use inner join but i cant it working because some events have multiple tickets and i need only one row. And some events have not tickets at all.

Is this even possible with singe query or do i need second query for events which not have tickets?

CodePudding user response：

SELECT *
FROM events AS e
LEFT JOIN ticket AS t
ON t.event_id = e.event_id
WHERE t.booking_start_date > NOW()
GROUP BY e.event_id

CodePudding user response：

You need to use unique IDs and you need to set relations. Once you have set a relation, you fetch the second table by the INNER JOIN and there you select the foreign key of that table. example: https://www.w3schools.com/sql/sql_join_inner.asp

For your use case you need to adjust your code and write different functions for fetching the right information. You will need multiple queries.