Query example

Hi,

I have to select two different column values from two different tables based on one condition.

Like, I have two tables - orders and fulfillment. I have to select orders.order_id and fulfillment.fulfillment_id for product_id = 2 and date_added = '08/18/06'. The only common fields between the two tables are customer_id,order_id,product_id,date_added.

I tried doing this but I know that won't work because it is returning all the rows from orders and fulfillment.

select o.order_id ,f.fulfillment_id

from orders o,fulfillment f

where o.product_id = 2

and o.date_added = '08/18/06'

and f.product_id = 2

and f.date_added = '08/18/2006'

Can somebody please suggest me how to select records from these two tables based on thte above condition?

Thanks,

Reva,

The details about the relationships of the data between the two tables are a little vague, but let me give it a try:

SELECT
o.order_id,
f.fulfillment_ID
FROM orders AS o
JOIN fulfillment AS f
ON o.order_id = f.order_id
WHERE o.product_id = 2
AND o.date_added = '08/18/06'

You will note that I don't have criteria in the WHERE clause of the f.date_added and f.product_id fields. I am assuming that the date_added and product_id values for a fulfillment are the same as the date_added and product_id values for the related order. If that is the case, you only need to filter the values in one of the tables.

The previous query uses the JOIN and ON clauses to state the join criteria. This is the standars compliant syntax that makes JOINS more accurate and less likely to cause unexpected results.

The problem with your query is that you used the older "legacy" style join syntax of FROM orders,fulfillment. When you do that you MUST state the join criteria (o.order_id = f.order_id) for the rows to match. Otherwise you get what is called a "CROSS JOIN" where every row of the first table is joined to every row of the second table. That is why you are seeing all the extra rows you didn't want. The syntax I used above prevents that from happening by accident because the ON clause is REQUIRED unless CROSS JOIN is explicitly stated.

I hope that helps.