Scorpioniz Scorpioniz - 7 months ago 10
SQL Question

Get data and sort from MySQL two tables

I have

table_1
and
table_2
and they are the same structure, but different data. Need to get all the data from that tables in sort of
create_date
and no matter from which table (it can be 1 row from table_1, 3 rows from table_2 and then again from table_1). Is it possible? How? or shout I get two queries and then sort by date with PHP?

EDIT:
Sorry for first part, I thought I can do it from there, but seems like i can't :/

i have very big query like this

SELECT
table_1.id,
table_1.created,
table_1.qty,
table_1.comments,
table_1.last_update,
table_7.firstname,
SUBSTRING(table_7.lastname, 1, 1) AS lastname,
table_8.title country,
table_3.value AS sim,
table_1.offer_request,
table_5.manufacturer AS manufacturer,
table_4.name AS model,
table_6.value AS specifications,
table_9.value as shipping,
table_1.model AS mid,
table_1.user_id,
table_1.callme,
table_1.phoneprice,
table_1.phoneprice_eur,
table_1.currency,
table_1.sel_buy_show_all,
table_1.seller_buyer
FROM (`table_1`)

LEFT JOIN `table_3` ON `table_3`.`id` = `table_1`.`sim`
LEFT JOIN `table_4` ON `table_4`.`id` = `table_1`.`model`
LEFT JOIN `table_5` ON `table_5`.`id` = `table_1`.`manufacturer`
LEFT JOIN `table_6` ON `table_6`.`id` = `table_1`.`specifications`
LEFT JOIN `table_7` ON `table_7`.`id` = `table_1`.`user_id`
LEFT JOIN `table_8` ON `table_7`.`country`=`table_8`.`id`
LEFT JOIN `table_9` ON `table_9`.`id` = `table_1`.`types`
WHERE `table_1`.`status` = '1'
AND `table_1`.`deleted` = '0'
ORDER BY `last_update` DESC
LIMIT 200


And there is table_1 which structure is the same as table_2, and I need somehow to insert table_2 to the query with all joins like table_1

Answer

If I got your question right, you can use union like this -

select * from table_1 union select * from table_2 order by create_date desc

EDIT

Create a view like this -

create view table_1And2 as select * from table_1 union select * from table_2

table_1And2 is not a good name, give a meaningful name.

And modify your long query like this -

SELECT 
table_1And2.id, 
table_1And2.created, 
table_1And2.qty, 
table_1And2.comments, 
table_1And2.last_update, 
table_7.firstname, 
SUBSTRING(table_7.lastname, 1, 1) AS lastname, 
table_8.title country, 
table_3.value AS sim, 
table_1And2.offer_request, 
table_5.manufacturer AS manufacturer, 
table_4.name AS model, 
table_6.value AS specifications, 
table_9.value as shipping, 
table_1And2.model AS mid, 
table_1And2.user_id, 
table_1And2.callme, 
table_1And2.phoneprice, 
table_1And2.phoneprice_eur, 
table_1And2.currency, 
table_1And2.sel_buy_show_all, 
table_1And2.seller_buyer
FROM (`table_1And2`)

LEFT JOIN `table_3` ON `table_3`.`id` = `table_1And2`.`sim`
LEFT JOIN `table_4` ON `table_4`.`id` = `table_1And2`.`model`
LEFT JOIN `table_5` ON `table_5`.`id` = `table_1And2`.`manufacturer`
LEFT JOIN `table_6` ON `table_6`.`id` = `table_1And2`.`specifications`
LEFT JOIN `table_7` ON `table_7`.`id` = `table_1And2`.`user_id`
LEFT JOIN `table_8` ON `table_7`.`country`=`table_8`.`id`
LEFT JOIN `table_9` ON `table_9`.`id` = `table_1And2`.`types`
WHERE `table_1And2`.`status` =  '1'
AND `table_1And2`.`deleted` =  '0'
ORDER BY `last_update` DESC
LIMIT 200