mysql php join query

[izagaren123]

hey

i have two tables in a database that i will like to join in the below code, but i cant. i can only use one table at a time.

$result = mysql_query("SELECT name,price from products  WHERE itemid = '$id'",$cxn) or die ("Unable to retrieve information from database");

if i put the below code in then i get the die error message.

$result = mysql_query("SELECT name,price from products,discount  WHERE itemid = '$id'",$cxn) or die ("Unable to retrieve information from database");

any ideas on how i can use both tables in the same query

regards

foad

[urtrivedi]

To join two table you must have pk/fk relation between tables. I am assuming that itemid is link column between two tables.

SELECT a.name, a.price,b.somcolname from products a ,discount b 
WHERE a.itemid=b.itemid and a.itemid = '$id'

[izagaren123]

thanks for the reply

i dont think i have explined what i wanted properly. all the data in the tables are different so the method does not really work.

i have two tables in the database, products and discount.

the first query i gave for the products works fine as it queries the product table for the name and price for whatever id is stored in id.

is it possible to query multple tables so that it will look for the id in products but if it is not there will look in discount and then output the relevant details

[urtrivedi]

You may use union to combine multiple tables. also Post your table structure.

[izagaren123]

the table structure is as follows

products
itemid pk int
name varchar
price varchar
image varchar
stock varchar

discount
itemid pk int
name varchar
price varchar
image varchar
stock varchar

[izagaren123]

i have used the following union statement

("SELECT * FROM products UNION SELECT * FROM discount WHERE itemid = '$id'",$cxn)

but it does not return the corrent details and only returns the details of the item in the first row

[izagaren123]

sorted used the code below

thanks for your help urtrevedi

$result = mysql_query("SELECT name,price FROM products WHERE itemid = '$id' union SELECT name,price FROM discount WHERE itemid = '$id'",$cxn) or die ("Unable to retrieve information from database");