SQL_CALC_FOUND_ROWS & UNION & Pagination...

[JKG]

hi there,

 

it would be great if you could help, i have hit a brick wall.

 

I have two queries (hence UNION), the first query is to return all the rows with a certain ID, in a certain order, this SQL works (i have removed some unnecessary bits):

 

SELECT SQL_CALC_FOUND_ROWS * FROM `users` JOIN `u_data` ON `users`.`ID`=`u_data`.`ID` WHERE `users`.`ID` in (4,5,6,7) AND `u_data`.ID NOT IN (1,2,3) ORDER BY FIELD(`u_data`.ID, 4,5,6,7)

 

the second query is to return the rest of the rows in a random, weighted order, this SQL works (i have removed some unnecessary bits):

 

SELECT * FROM `users` JOIN `u_photographers` ON `users`.`ID`=`u_photographers`.`ID` WHERE `users`.`Level_access`=2 AND `users`.`active`=1 AND `users`.`ID` NOT IN (1,2,3) ORDER BY ((`u_data`.`referral_points` + 60) * RAND(5330)) DESC

 

when i join these two queries together i am trying to use this SQL:

 

(SELECT SQL_CALC_FOUND_ROWS * FROM `users` JOIN `u_data` ON `users`.`ID`=`u_data`.`ID` WHERE `users`.`ID` in (4,5,6,7) AND `u_data`.ID NOT IN (1,2,3) ORDER BY FIELD(`u_data`.ID, 4,5,6,7)) 
UNION
(SELECT * FROM `users` JOIN `u_photographers` ON `users`.`ID`=`u_photographers`.`ID` WHERE `users`.`Level_access`=2 AND `users`.`active`=1 AND `users`.`ID` NOT IN (1,2,3) ORDER BY ((`u_data`.`referral_points` + 60) * RAND(5330)) DESC)

 

this code works fine in phpMyAdmin however this does not quite work in that when going through the pagination on the site, the first query always returns, whatever page (1,2,3,4 etc), but it should only return the selected id's, then move onto the second query...

 

any thoughts or clarification, please ask, thanks for reading.

[The Little Guy]

I think something like this is what you want:

 

SELECT SQL_CALC_FOUND_ROWS, * FROM
(
(
	SELECT * FROM `users` JOIN `u_data` ON `users`.`ID`=`u_data`.`ID` WHERE `users`.`ID` in (4,5,6,7) AND `u_data`.ID NOT IN (1,2,3) ORDER BY FIELD(`u_data`.ID, 4,5,6,7)
) UNION (
	SELECT * FROM `users` JOIN `u_photographers` ON `users`.`ID`=`u_photographers`.`ID` WHERE `users`.`Level_access`=2 AND `users`.`active`=1 AND `users`.`ID` NOT IN (1,2,3) ORDER BY ((`u_data`.`referral_points` + 60) * RAND(5330)) DESC
)
) as myTable LIMIT 10, 10;

[JKG]

That looks spot on, thanks so much for taking the time to reply. I will report back with my results asap.

Im not too great with SQL as you can tell!

[JKG]

Hi there again,

 

I tried to implement your suggestion but I'm getting a Duplicate column name 'ID' error #1060.

 

Should I be using a different JOIN?

 

Thanks for your continued help,

Joe.