0

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

Edited by mike_2000_17: Fixed formatting

2
Contributors
6
Replies
12
Views
7 Years
Discussion Span
Last Post by izagaren123
0

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'
0

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

0

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

0

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

0

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");
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.