Mysql query help...

[ajlisowski]

I am simplifying a much larger issue to hopefully be able to get some insight on how best to accomplish my bigger task.

 

Basically I have two tables, they are identical. We will call them table A and table B. All Primary keys in table B exist in table A, but not all keys in A exist in B. Data associated with the keys of B will be slightly different then that of A.

 

I want to grab one set of data from A and B where if a key exists in B we use that, otherwise we use A.

 

 

In case your wondering why. Basically I have a system where users can modify data but it has to be approved before it can go live. And when said users view the data I want them to see their modified data if it exists, otherwise they see the default data.

 

 

[robert_gsfame]

have u tried JOIN

[ajlisowski]

The issue with joining that I see is that if I join I will have two results, one for each table. And in the end i need to sort by and paginate based on the results. So lets say I want to sort by price, id have a.price and b.price, one of which would be null, the other a value, how would I sort by both of them?

[ajlisowski]

I think what I should do is UNION the two queries and then do my sorting and LIMITs on the unioned result set.

 

so, (SELECT id from A) UNION (SELECT id from B) ORDER BY id LIMIT 0,

 

though I cant imagine this is the most effecient way.