[SOLVED] Cross Table Reference Query Problems

[roddyguy]

I have two tables: PRODUCTS and PATTERN BINDINGS

The PRODUCT_ID is the field they have in common.

So sometimes a Product may have more than one record associated within the PATTERN BINDINGS table.

Using a search form I have built, queries are made to the Database to find PATTERN BINDING records that are associated with the PRODUCTS.

 

Thus using the "OR" situation for searching works fine.

IE:

"SELECT p.products_id FROM products p, pattern_bindings pb WHERE p.products_id = pb.products_id AND (pb.pc_id = '5' OR pb.pc_id = '32')"

 

But using the "AND", obviously does not.

IE:

"SELECT p.products_id FROM products p, pattern_bindings pb WHERE p.products_id = pb.products_id AND (pb.pc_id = '5' AND pb.pc_id = '32')"

 

In other words, I am trying to come up with a way to look for cases where the Product has a record for both patterns. (A more filtered search)

 

The only way that I figured out how to do this -- is by using sub-queries.

IE:

select p.products_id from products p, pattern_bindings pb where p.products_id = pb.products_id and p.products_id IN (select p.products_id from products p, pattern_bindings pb where p.products_status = '1' and p.products_id = pb.products_id and (pb.pc_id = '31' or pb.pc_id = '999999999')) and p.products_id IN (select p.products_id from products p, pattern_bindings pb where p.products_status = '1' and p.products_id = pb.products_id and (pb.pc_id = '31' or pb.pc_id = '999999999')) GROUP BY p.products_id

 

 

As you can imagine, this takes a while to process. Slow moving.

 

I tried many JOIN Query solutions, but could not get any to work.

 

Any ideas?

 

[fenway]

You'll need a join for each of the "AND" conditions.... there are many threads that cover this topic.

[roddyguy]

OK folks, let me share a little bit clearer example I got from Peter Brawley:

 

SELECT p.products_id

FROM products p

JOIN pattern_bindings a ON p.products_id = a.products_id AND a.pc_id = 5

JOIN pattern_bindings b ON p.products_id = b.products_id AND b.pc_id = 32;

 

PB

http://www.artfulsoftware.com