SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    May 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with correlated subquery

    I have a query which when I run in phpmyadmin does not give any error or results. It is not returning an empty result set, just appears to not do anything. I'm not sure what it means. Is there something wrong with my query?

    select e.event_id, e.serial_number, e.event_type,
    (
    select ehin.state from event_history ehin
    where ehin.event_id = e.event_id
    order by date_time desc limit 1
    )as state
    from events e
    where e.serial_number = '2000234372'
    and state != 'closed'

    Fields in events table are event_id, serial_number and event_type.

    Fields in event_history_table are event_history_id, event_id, state, date_time among others.

    Any help would be appreciated. Thanks.

  2. #2
    SitePoint Wizard cmuench's Avatar
    Join Date
    Jul 2005
    Location
    At my computer
    Posts
    2,251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This may be a stupid question but are you sure you have data in both tables and the event_id is the same? I have debugged for an hour or 2 before not realizing I was working on the wrong db or a table that had no info in it Can you give me your create table statements?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT e.event_id
         , e.event_type
         , h.state
      FROM events e
    INNER
      JOIN ( SELECT event_id
                  , MAX(date_time) AS max_date_time
               FROM event_history 
             GROUP
                 BY event_id ) AS m
        ON m.event_id = e.event_id
    INNER
      JOIN event_history AS h
        ON h.event_id = m.event_id
       AND h.date_time = m.max_date_time
     WHERE e.serial_number = '2000234372'
       AND h.state <> 'closed'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    May 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. I do have data in the tables. Good question, though. I still don't know why my first query didn't work but this one does:

    select e.event_id, e.serial_number, e.event_type, eh.state
    from events e inner join event_history eh
    on e.event_id = eh.event_id
    where eh.event_history_id =
    (
    select event_history_id from event_history
    where event_history.event_id = e.event_id
    order by date_time desc limit 1
    )
    and serial_number = 2000234609
    and state != 'closed'

    I can only run it through the bash shell, though. When I try to run it in phpmyadmin, I don't get any results. No error and no emply result set, it just doesn't do anything.

    Thanks.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what version of phpmyadmin?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Member
    Join Date
    May 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am running phpMyAdmin - 2.11.4

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by swsmoore View Post
    I still don't know why my first query didn't work but this one does:
    didja try mine?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    May 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, sorry for the delay in replying. Your query does the same thing. In phpmyadmin it doesn't give an error, just no results.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •