Combining results of two tables

hello,
i’m developing a website using php and mysql. there i have 2 tables, tblproduct and tblaccessory. I created a search engine. currently only capable of finding items in tblproduct table. I want to take results from both tables, so i can display accessories as well. It is too late to refine my database now. is there a way i can achieve this? i tried using UNION but, it generates undefined variable errors in php, if the results from one table is unavailable. Can i use LEFT JOIN? but, there is no table to take as the LEFT side table.
I really appreciate an answer for this… Thank you…

Hey, guys… I think i got it… yeah. I realize that the first query of an UNION statement decide the field names of the resulted output. But, seems UNION works fine now, I used real field names and assign aliases to them. Now I can acquire data by that aliases. Thanx guys :slight_smile:

you are going to be so shocked when you hear this, but i feel it is my duty to inform you that UNION combines its results into one result set too

:slight_smile:

And is it working ?

no i didn’t try UNION all… Wait i’ll try that also…

thanx for your reply,
tblproduct table consists of;
product_id,product_name,brand,image,thumbnail,unit _price,description,unit_in_stock,sub_category_id
product_id is an auto generated id. and sub_category_id is a foreign key.
tblaccessory consists of;
accessory_id,accessory_name,image,thumbnail,descri ption,brand,price,units_in_stock
accessory_id is also auto generated.
image and thumbnails are stings.

This is my sql statement;
$query=“SELECT * FROM tblproducts WHERE product_name LIKE ‘%$name%’
UNION
SELECT * FROM tblaccessory WHERE accessory_name LIKE ‘%$name%’”;

Did you try UNION ALL? instead of UNION

I can’t recall how union outputs its results but, union all combines them into one resultset.

bazz

Could you post the two table descriptions and the SQL containing the UNION.

guess what I am just about to read up on. :wink: