Ordering query based on whether they meet a specific query

[SchweppesAle]

Hi, I have the following query which returns all events which fall within a specific list of zip codes. 

 

I'd like to know if there's a way of modifying the query so that all entries which don't meet the 'IN' clause are simply displayed after the resulting set.  Basically, we're trying to prioritize local events by displaying them first without excluding the others.

 

SELECT events.name 
         			, events.time 
         			, events.eventid 
         			, photos.thumb_file 
         		FROM events 
         			INNER JOIN performers ON 
         				(events.eventid = performers.eventid) 
         			INNER JOIN venues ON 
         				(events.venueid = venues.venueid) 
         			LEFT JOIN photos ON 
         			(performers.performerid = photos.performerid) 
         				WHERE events.child_category_id = '38' AND 
         				venues.zipcode IN ('10017','10261','10259','10174','10164','10170','10168','10087','10179','10167','10163','10166','10178','10154','10022','10158','10103','10124','10150','10173','10197','10171','10176','10165','10152','10157','10156','10043','10055','10175','10016','10020','10111','10126','10155','10104','10105','10153','10112','10151','10095','10138','10129','10010','10160','10172','10159','10018','10036','10185','10102','10149','10109','10177','10001','10108','10090','10019','10117','11120','10106','10118','10162','10122','10021','11109','10120','10060','10121','10119','10101','10107','10099','10276','11249','10123','10072','10125','10044','10113','10069','10011','10114','10003','10211','10199','10082','10133','10023','10009','10110','11222') 
         					GROUP BY performers.performer_name 
         						ORDER BY time DESC LIMIT 6

[SchweppesAle]

guessing this is no easy task

[fenway]

That's quite tricky, since you have no other source for those zipcodes other than user input.

[SchweppesAle]

That's quite tricky, since you have no other source for those zipcodes other than user input.

 

Yea, I mean the trick is to place all entries which match that venues.zipcodes IN() clause at the top of the resulting set without excluding all the others.  Is there anyway of doing this?

[gizmola]

I'd suggest you union the 2 queries, using your IN list  (IN for the ones at the top, NOT IN for the others).  In each add a column called sortorder that you set to a constant.  For the IN list set it to '0' for the NOT in set it to 1.  Then you can ORDER BY this column and all the IN's will be at the top.

[SchweppesAle]

I'd suggest you union the 2 queries, using your IN list  (IN for the ones at the top, NOT IN for the others).  In each add a column called sortorder that you set to a constant.  For the IN list set it to '0' for the NOT in set it to 1.  Then you can ORDER BY this column and all the IN's will be at the top.

 

ummmm...I'm really not too familiar with Unions.  How would that look exactly?

[gizmola]

It's not that hard, look up UNION in the mysql manual.

[SchweppesAle]

alright..here's a test query which I'm trying to run through phpmyadmin.

 

select * FROM ((SELECT events.eventid 
		, events.date 
		, events.name 
		, events.venue 
		, events.venueid 
		, venues.zipcode 
	FROM events 
		INNER JOIN venues ON 
	(events.venueid = venues.venueid) 
		where events.child_category_id='24' 
		and events.parent_category_id='2' 
		and events.status='1' 
		AND venues.zipcode IN ('07087','07086','10272','07093','07030','07047','07097','10130','07307','07096','10125','10072','10099','11249','10161') 
		group by events.name 
		order by date asc)
union (SELECT events.eventid 
		, events.date 
		, events.name 
		, events.venue 
		, events.venueid 
		, venues.zipcode 
	FROM events 
		INNER JOIN venues ON 
	(events.venueid = venues.venueid) 
		where events.child_category_id='24' 
		and events.parent_category_id='2' 
		and events.status='1' 
		AND venues.zipcode NOT IN ('07087','07086','10272','07093','07030','07047','07097','10130','07307','07096','10125','10072','10099','11249','10161') 
		group by events.name 
		order by date asc) ) 

 

That returns the following:

"#1248 - Every derived table must have its own alias "

 

So I tried this:

select * FROM ((SELECT events.eventid 
		, events.date 
		, events.name 
		, events.venue 
		, events.venueid 
		, venues.zipcode 
	FROM events 
		INNER JOIN venues ON 
	(events.venueid = venues.venueid) 
		where events.child_category_id='24' 
		and events.parent_category_id='2' 
		and events.status='1' 
		AND venues.zipcode IN ('07087','07086','10272','07093','07030','07047','07097','10130','07307','07096','10125','10072','10099','11249','10161') 
		group by events.name 
		order by date asc) AS Table1
union (SELECT events.eventid 
		, events.date 
		, events.name 
		, events.venue 
		, events.venueid 
		, venues.zipcode 
	FROM events 
		INNER JOIN venues ON 
	(events.venueid = venues.venueid) 
		where events.child_category_id='24' 
		and events.parent_category_id='2' 
		and events.status='1' 
		AND venues.zipcode NOT IN ('07087','07086','10272','07093','07030','07047','07097','10130','07307','07096','10125','10072','10099','11249','10161') 
		group by events.name 
		order by date asc) AS Table2) AS Table3

 

Which returns:

"#1064 - 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 'AS Table2 ) AS Table3 "

 

Feels like I'm getting close at least 

[SchweppesAle]

Think this might have done the trick

 

select * FROM ((SELECT events.eventid 
		, events.date 
		, events.name 
		, events.venue 
		, events.venueid 
		, venues.zipcode 
	FROM events 
		INNER JOIN venues ON 
	(events.venueid = venues.venueid) 
		where events.child_category_id='24' 
		and events.parent_category_id='2' 
		and events.status='1' 
		AND venues.zipcode IN ('07087','07086','10272','07093','07030','07047','07097','10130','07307','07096','10125','10072','10099','11249','10161') 
		group by events.name 
		order by date asc) 
union (SELECT events.eventid 
		, events.date 
		, events.name 
		, events.venue 
		, events.venueid 
		, venues.zipcode 
	FROM events 
		INNER JOIN venues ON 
	(events.venueid = venues.venueid) 
		where events.child_category_id='24' 
		and events.parent_category_id='2' 
		and events.status='1' 
		AND venues.zipcode NOT IN ('07087','07086','10272','07093','07030','07047','07097','10130','07307','07096','10125','10072','10099','11249','10161') 
		group by events.name 
		order by date asc)) AS Table3

 

w00t, executing Unions.  Thanks for the advice man. 

[gizmola]

No worries, congrats on working it out.

[ChrisA]

Could you do something like this:

 

SELECT events.name
                  , events.time
                  , events.eventid
                  , photos.thumb_file
                  , (venues.zipcode IN ('10017','10261',...)) AS ziporder
               FROM events
                  INNER JOIN performers ON
                     (events.eventid = performers.eventid)
                  INNER JOIN venues ON
                     (events.venueid = venues.venueid)
                  LEFT JOIN photos ON
                  (performers.performerid = photos.performerid)
                     WHERE events.child_category_id = '38' 
                        GROUP BY performers.performer_name
                           ORDER BY ziporder DESC

 

I.e. put the 'zipcodes IN (...)' bit in as a select expression, then order by that expression.  All the ones in the list should be 1, so they'll be before the ones not in the list..

 

Or is that more than you can do with a select expression?