CODEWITHSUNDEEP
mysqlsql
Shwetanka
Shwetanka

Reputation: 5036

Mysql complex querying

Hi I've two tables like this - 

Event
id   title
1    'Test 1'
2    'Test 2'
3    'Test 3'

and Schedule
id  event_id   price   event_date
1      1         100    2013-04-15
2      1         150    2013-04-20
3      2         80     2013-04-18
4      3         120    2013-04-26
5      3         140    2013-04-22
6      2         100    2013-04-22

I want events as follows - Unique events Ordered by event_date ASC 

e.id   e.title   s.price   s.event_date
1       Test 1    100       2013-04-15
2       Test 2    80        2013-04-18
3       Test 3    140       2013-04-22

I tried querying like this - 

SELECT e.id, 
       e.title, 
       s.price, 
       Min(s.event_date) AS min_start 
FROM   events AS e 
       JOIN schedules AS s 
         ON ( e.id = s.event_id ) 
GROUP  BY s.event_id;

and then querying again for events by iterating the result set of previous query. But this is too many queries. Also I don't get the correct price. what is the best way to achieve above result? Thanks

Upvotes: 2

Views: 1992

Answers (5)

puneet jain
puneet jain

Reputation: 1

select a.id, a.title, b.price, b.event_date from event a inner join schedule b ON a.id = b.event_id order by b.event_id desc ;

Upvotes: 0

smurf
smurf

Reputation: 219

it looks complex but its simpler either

select * from 

    (select 
           event.id,
           event.title,
           schedule.event_date,
           schedule.price,
           event_id 
    from schedule 
    left join event on event.id=schedule.event_id 
    group by event_date,event_id
    )s

group by s.event_id;

Upvotes: 0

smurf
smurf

Reputation: 219

you can create a view and select from it:

CREATE VIEW scheduleview AS select * from schedule group by event_date,event_id;
select * from scheduleview group by event_id;

Upvotes: 0

Sakthivel
Sakthivel

Reputation: 1

SELECT   s.title, a.event_id, a.price, a.event_date
  FROM schedule a JOIN event s ON a.event_id = s.ID
 WHERE a.event_date = (SELECT MIN (b.event_date)
                         FROM schedule  as b
                        WHERE b.event_id = a.event_id)
 ORDER BY a.event_id ASC

Upvotes: 0

John Woo
John Woo

Reputation: 263803

There can be many possible solutions to solve your problem. One of it which I used to do it is by using a separate subquery which gets the first event_date for every event_ID. The result is then joined back on the the other table to get the other columns.

SELECT  a.ID, a.Title,
        b.Price, b.Event_date
FROM    `Event` a
        INNER JOIN `Schedule` b
            ON a.ID = b.event_ID
        INNER JOIN
        (
            SELECT  event_ID, MIN(event_date) min_date
            FROM    `Schedule`
            GROUP   BY event_ID
        ) c ON b.event_ID = c.event_ID AND
                b.event_date = c.min_date

OUTPUT

╔════╦════════╦═══════╦══════════════════════════════╗
║ ID ║ TITLE  ║ PRICE ║          EVENT_DATE          ║
╠════╬════════╬═══════╬══════════════════════════════╣
║  1 ║ Test 1 ║   100 ║ April, 15 2013 00:00:00+0000 ║
║  2 ║ Test 2 ║    80 ║ April, 18 2013 00:00:00+0000 ║
║  3 ║ Test 3 ║   140 ║ April, 22 2013 00:00:00+0000 ║
╚════╩════════╩═══════╩══════════════════════════════╝

The simpliest way, if you are only interested on the two columns is

Upvotes: 2

Related Questions