Reputation: 3
i have two similar tables with the same fields. One is named "input" and the other "product" Both have the same fields:
My first question is: How can I write a query to effectively return every item's details?
I use JSON to export this data
$json_data.='"rec'.$i.'":{"id":"'.$id.'", "name":"'.$name.'", "price":"'.$price.'", "qty":"'.$qty.'", "units":"'.$unit.'", "pic":"'.$pic.'"},';`.
My second question is: How can i effectively reference all the fields from both tables?
NB: I don't want to put the two categories in the same table, say, item
, with an additional field of category
.
Just to rectify this: the join condition is (input.pic=pic.id and product.pic=pic.id)
Upvotes: 0
Views: 75
Reputation: 2235
The problem with that scenario is the word "efficient". The pic - input - leftjoin
is slower than an inner join, and the sum of left join input
and left join output
is slower than a simple inner join
.
select pic.id as pic_id, pic.data, input.id as input_id, output.id as output_id, input....
from pic
left join input on input.pic_id = pic.id
left join output on output.pic_id = pic.id
or
select pic.id as pic_id, pic.data, "input" as type, input.id as id, input....
from pic
inner join input on input.pic_id = pic.id
union
select pic.id as pic_id, pic.data, "output" as type, output.id as id, output....
from pic
inner join output on output.pic_id = pic.id
The union is faster, because of using two inner joins.
Upvotes: 0
Reputation: 829
use full outer in mysql this is not possible so use/take help of union to get all the rows
SELECT * FROM input
LEFT JOIN product ON input.id = product.id
UNION
SELECT * FROM input
RIGHT JOIN product ON input.id = product.id
Upvotes: 0
Reputation: 24665
If the tables have the same fields you can do a union query.
SELECT * FROM table1
UNION
SELECT * FROM table2
if they are similar as you say then you just have to select the fields that they have in common into both
SELECT "table1" as source_table,id,name,qty,unit,price from table1
UNION
select "table2" as source_table,id,name,qty,unit,price from table2
note I added a source table so you could backtrack where you got it from .... cheers
Upvotes: 1