CODEWITHSUNDEEP
phpmysqlajaxjson
Collins Kawere
Collins Kawere

Reputation: 3

One query for two similar tables

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

Answers (3)

flaschenpost
flaschenpost

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

Sri Tirupathi Raju
Sri Tirupathi Raju

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

Orangepill
Orangepill

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

Related Questions