ianh Posted January 21, 2010 Share Posted January 21, 2010 For some reason my UNION select query doesn't seem to return the next record. I have 3 records with the same 'category', 'status', yet the 'right_column' is different. id 3015 category_id 2 status 1 right_column 0 id 3021 category_id 2 status 1 right_column 1 id 3023 category_id 2 status 1 right_column 0 I want to select 2 records; the current record (3015) and the next highest record from 3015 which should be 3021, however it is returning 3023 ?? It's driving me insane. Ideas anyone? Thanks in advance. (SELECT id, title, abstract, body, status, right_column FROM Articles WHERE category_id = 2 AND id = 3015 AND status = 1) UNION (SELECT id, title, abstract, body, status, right_column FROM Articles WHERE category_id = 2 AND id > 3015 AND status = 1 AND right_column = 1 ORDER BY id ASC LIMIT 1) ORDER BY right_column ASC Quote Link to comment Share on other sites More sharing options...
ianh Posted January 21, 2010 Author Share Posted January 21, 2010 Anyone? Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 21, 2010 Share Posted January 21, 2010 Hi Can't see anything obvious. Can you export the table structure and a couple of records to make it easy to have a play? All the best Keith Quote Link to comment Share on other sites More sharing options...
ianh Posted January 21, 2010 Author Share Posted January 21, 2010 Here is the Articles table structure..... [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
ianh Posted January 21, 2010 Author Share Posted January 21, 2010 ...and here are all the records from the same category..... [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 21, 2010 Share Posted January 21, 2010 Hi Put inverted commas around the value for right_column in the 2nd part of your query. That fixes it but not sure why. Only thing I can think of is that using a number forces it to pick that number of the ENUM rather than the one with that value. All the best Keith Quote Link to comment Share on other sites More sharing options...
ianh Posted January 21, 2010 Author Share Posted January 21, 2010 Hi Put inverted commas around the value for right_column in the 2nd part of your query. That fixes it but not sure why. Only thing I can think of is that using a number forces it to pick that number of the ENUM rather than the one with that value. All the best Keith Works perfectly! Thanks mate Strange about the inverted commas. Perhaps it is something to do with whe there are a number of ANDs in a UNION statement? Corrected/working code: (SELECT id, title, abstract, body, status, right_column FROM Articles WHERE category_id = 2 AND id = 3015 AND status = 1) UNION (SELECT id, title, abstract, body, status, right_column FROM Articles WHERE category_id = 2 AND id > 3015 AND status = 1 AND right_column = "1" ORDER BY id ASC LIMIT 1) ORDER BY right_column ASC Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 21, 2010 Share Posted January 21, 2010 Strange about the inverted commas. Perhaps it is something to do with whe there are a number of ANDs in a UNION statement? Fairly certain not. Think the ENUM of right_column is treated by MySQL as an array starting at 1. If you refer to '1' you are referring to the value '1' while if you refer to 1 you get the first possible value of the enum (which happens to be 0). If you have a play with a select and refer to right_column = 2 you will find it will bring back columns where the value of right_column is 1 (ie, the 2nd possible value). All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.