#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    157
    Rep Power
    89

    Query optimisation


    Hello,

    I have tried to simplify the example query as much as possible for this question.

    I have a table called person which holds people.
    I have another table which holds records of attendance.
    The goal is to produce a report which will list the people in the person table as well as showing if they were present at the three most recent events.

    The final query is intended to list the 6 most recent events. My main question is whether or not I can somehow reference the dates returned in the sub query using limit 0,6 rather than re-running the subquery each time with limits 0,1, 1,1 and 2,1 etc.



    Code:
    select
    	person.personID,
    	person.personName,
    	date_a.present as date_a_present,
    	date_b.present as date_b_present,
    	date_c.present as date_c_present,
    from
    	person
    	left join attendance as date_a
    		on person.personID=date_a.personID and
    		date_a.attendanceDate=(select 
    						distinct attendance.attendanceDate 
    					from 
    						attendance 
    					order by 
    						attendanceDate desc 
    					limit 0,1)
    	left join attendance as date_b
    		on person.personID=date_b.personID and
    		date_b.attendanceDate=(select 
    						distinct attendance.attendanceDate 
    					from 
    						attendance 
    					order by 
    						attendanceDate desc 
    					limit 1,1)
    
    	left join attendance as date_c
    		on person.personID=date_c.personID and
    		date_c.attendanceDate=(select 
    						distinct attendance.attendanceDate 
    					from 
    						attendance 
    					order by 
    						attendanceDate desc 
    					limit 2,1)
    Last edited by TASB; July 12th, 2013 at 09:35 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Code:
    SELECT person.personID
         , person.personName
      FROM person
    INNER
      JOIN attendance AS date_a
        ON date_a.personID = person.personID
       AND date_a.attendanceDate IN 
           ( SELECT DISTINCT 
                    attendanceDate 
               FROM attendance 
             ORDER 
                 BY attendanceDate DESC LIMIT 6 )
    GROUP
        BY person.personID
    HAVING COUNT(*) = 6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo