Mike Ross Mike Ross - 6 months ago 8
SQL Question

mysql data from foreign table

sorry for poor heading of the question but I dont know using which method i can get my data from

mysql
thats why i wrote it like this.

Anyway I have 2 tables
event_details
and
event_dates
.
event_details
has fields
id,name,created,modified
. Important table is
event_dates
. Which stores
start_date
and
end_date
of the events.

Sample data is like below

id eventId date
1 72 20160520
2 72 20160521
3 72 20160522
4 73 20160518
5 74 20160519
6 75 20160524
7 75 20160525
8 75 20160526


Here scenario for
startdate
and
enddate
is like this

1.If there is only 1 entry for
eventId
that means event has same
startDate
and
EndDate
.

2.If eventId is in the table for more than 2 times than first entry is
startDate
and last entry is
endDate
.

So i got confused that what method should i use to get result in one table with
startDate
and
endDate
of the event.

I want data in following format

Name startDate endDate
music show 20160520 20160522
racing show 20160518 20160518
pupet show 20160519 20160519
dance show 20160524 20160526


Any help or just some guidance will be nice. Let me know if its easier through getting data in array in php file and than run through loops or easier using the
mysql
queries.

Answer

Try this query:

SELECT
    ed.id,
    ed.name,
    created,
    modified,
    min(startDate) start_date,
    max(endDate) end_date
FROM
    event_details ed
RIGHT JOIN event_dates edates ON ed.id = edates.id
GROUP BY    ed.id;