SELECT Query on Two Tables

[koopkoop]

How do I select a single row from two tables with a common field - based on a unique sharked key.

 

For example, I've got two tables "products" and "prices". I want to select all the fields from each based on the shared "itemnumber" key.

 

This returns zero results:

SELECT * FROM prices,products WHERE products.itemnumber="11004NA" AND prices.itemnumber="11004NA"

 

 

This returns 1 result - which is exactly what it should be doing:

SELECT * FROM prices,products WHERE products.itemnumber LIKE"%11004NA%" AND prices.itemnumberLIKE "%11004NA%"

 

Why doesn't the first one work? What's the proper way of writing this?

 

 

[koopkoop]

To further clarify, the number of fields between the two are completely different along with the names:

 

eg.

 

PRODUCTS

itemnumber,name,description

 

PRICES

itemnumber,prices,caseweight,caseqty

 

I want to search the two for a unique product and get a result like

itemnumber,name,description,prices,caseweight,caseqty

 

Thanks.

 

 

 

 

[priti]

hi,

 

select p.itemnumber,p.name,p.description,pr.prices,pr.caseweight,pr.caseqty

 

from products p,prices pr

 

where p.itemnumber=pr.itemnumber

 

will help.

 

Regards

[peranha]

You will need to use join statements.

 

SELECT D.name, D.description, P.prices, P.caseweight, P.caseqty FROM Products D INNER JOIN Prices P ON D.itemnumber = P.itemnumber WHERE P.itemnumber = "11004NA" AND D.itemnumber = "11004NA"

 

Something like that should work, or get you started anyway.