www.webdeveloper.com
Results 1 to 5 of 5

Thread: Tricky query

Hybrid View

  1. #1
    Join Date
    Nov 2009
    Location
    Oslo, Norway
    Posts
    3

    Cool Tricky query

    I have a table 'order' with the following columns:
    Code:
    id | name | status | year
    For simplicity, let's say the table contains people who attended an annual event. The 'status' column is '1' if the attendant has paid his invoice for the event, and '0' if he has not paid yet.

    I currently have a query listing all attendants from a given year:
    select * from order where year = 2009
    Easy.

    Now, what's not so easy:
    I want to add a column to query result, which should tell me if the attendant has unpaid invoices for previous events.
    Let's say the table contains the following data:
    Code:
    1 | John Doe | 0 | 2009
    2 | Dunc Doe | 0 | 2009
    3 | Jack Doe | 0 | 2009
    4 | John Doe | 1 | 2008
    5 | Jane Doe | 1 | 2008
    6 | Jack Doe | 0 | 2008
    7 | Dunc Doe | 0 | 2007
    This means that John, Dunc and Jack all have signed up for the 2009 event. No-one has paid yet. John and Jane attended last year's event (2008), and are all paid up. Jack, however, did never pay for 2008. Dunc even has an unpaid bill from the 2007 event.

    Now, I want a query that gives me the following result:
    Code:
    1 | John Doe | 0 | 2009 | 0
    2 | Dunc Doe | 0 | 2009 | 1
    3 | Jack Doe | 0 | 2009 | 1
    Column 5 would be 1 if the attendant has paid for all his previous events, and 0 if he has unpaid bills from previous events. Wether he has paid for this year's event (2009) is irrelevant.
    Thus, Dunc and Jack gets a '0' because they have unpaid bills from 2007 and 2008, respectively.

    What should this SQL query look like?

    Using MySQL 5.0.67, with InnoDB tables (if that matters).

  2. #2
    Join Date
    Oct 2009
    Posts
    658
    Quote Originally Posted by vramdal View Post
    Using MySQL 5.0.67, with InnoDB tables (if that matters).
    Yes it does as we can't reliably use counts on innodb tables which was my first impulse by the way. Check this out and just clean it up a bit to optimize performance

    Code:
    select
    	t4.id
    	,t4.name
    	,t4.status
    	,t4.year
    	,min(t4.year)
      from
        (select
    		*
    	  from
    		(select
    			t1.id
    			,t1.name
    			,t1.status
    			,t1.year
    			,t2.status delinquency
    		  from
    			order t1
    		  left outer join
    			  order t2
    			on
    			  t1.name = t2.name
    			  and t1.year != t2.year
    		) t3
    	  where
    		t3.year = 2009
    	) t4
      group by
    	t4.name
    Good Luck

    Santos Systems

  3. #3
    Join Date
    Nov 2009
    Location
    Oslo, Norway
    Posts
    3
    Thanks for your suggestion!

    However, I get a syntax error on the "from order t1" part - are you sure that's correct?
    Code:
     You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order t1 left outer join order t2 on t1.name = t2.name and t1.year != t2.year' at line 2

  4. #4
    Join Date
    Oct 2009
    Posts
    658
    Yes I'm sure about the concept. I forgot to mention avoid using reserved words for your table names, column names, etc. ("Order")

    Code:
    select
    	t4.id,
    	t4.name,
    	t4.status,
    	t4.year,
    	min(t4.delinquency) delinquency
      from
    	(select
    		*
    	  from
    	    (select
    			t1.id
    			,t1.name
    			,t1.status
    			,t1.year
    			,t2.status delinquency
    		  from
    			  order_table t1
    			left outer join
    				order_table t2
    			  on
    				t1.name = t2.name
    				and t1.year != t2.year
    		) t3
    	  where
    		t3.year = 2009
    	) t4
      group by
    	t4.name;
    Good Luck

    Santos Systems

  5. #5
    Join Date
    Nov 2009
    Location
    Oslo, Norway
    Posts
    3
    Ah, of course, I didn't spot the "order" ambiguancy.
    The query seems to work, thanks sooooo much!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles