CODEWITHSUNDEEP
mysqlsql
natas
natas

Reputation: 456

Joining and ordering data from different tables

This is my DB structure:

table: events
    id
    title
    category_id

table: categories
    id
    name

table: promoters
    id
    name
    avatar

table: dates
    id
    startdate
    enddate

Every Event can have one Category and many promoters, dates.

then for dates and promoters I use two pivot tables:

table: event_promoter
    id
    event_id
    promoter_id

table: event_date
    id
    event_id
    date_id

I need to retrieve all events with promoters and categories fields ordered by dates (taking only dates > today date). Looking for query help.

UPDATE EXAMPLE

Two events:

Table Events
id  | title                  | category_id
1   | StackOverflow Meeting  | 1
2   | Easter Party           | 2

Table Categories
id  | name  
1   | Festival
2   | Professional

Table Promoters
id  | name       | Avatar  
1   | Joe Smith  | file1.jpg
2   | Frank White| file2.jpg
3   | Mike Red   | file3.jpg

Table Dates
id  | start      | end  
1   | 01-24-2018 | 01-27-2018
2   | 04-24-2018 | 04-27-2018
3   | 04-18-2018 | 04-19-2018

table: event_promoter
    id  | event_id   |  promoter_id
    1   | 1          |  1
    2   | 1          |  3
    3   | 2          |  2

table: event_date
    id  | event_id   |  date_id
    1   | 1          |  1
    2   | 1          |  2
    3   | 2          |  3

My query need to have first "easter party" because of the date(id:3), then in my events list I've:

('Easter Party', 'StackOverflow Meeting')

with all associated relational fields.

Upvotes: 0

Views: 49

Answers (1)

Dionei Miodutzki
Dionei Miodutzki

Reputation: 657

First, you will need to reference the category on the events table, by adding a category_id column in the events table. Also, you dont need an id column in event_promoter and in event_dates. Remove that column and make the two remaining columns the primary key.

Then, the simplest way of doing it will by a inner join:

select events.id, events.title, 
categories.name as category, 
GROUP_CONCAT(dates.startdate separator ',') as startdate, 
GROUP_CONCAT(dates.enddate separator ',') as enddate, 
GROUP_CONCAT(promoters.name separator ',') as promoter, 
GROUP_CONCAT(promoters.avatar separator ',') as avatar
from events
inner join categories on events.category_id = categories.id
inner join event_dates on events.id = event_dates.event_id
inner join dates on event_dates.date_id = dates.id 
inner join event_promoter on events.id = event_promoter.event_id 
inner join promoters on event_promoter.promoter_id = promoters.id
where dates.startdate > now()
group by events.id
order by dates.startdate

Upvotes: 1

Related Questions