additional mysql select query condition?

Question

I need to add another AND condition to this but from another table is this possible to add the condition??

currently my mysql query is:

$category_min_price = db_get_field("SELECT min(price) FROM ?:product_prices p, ?:products_categories c WHERE p.product_id=c.product_id AND c.category_id = ?i",$category_id);

but i need to check if the product is active in the above statement which is located in another table, details are as follows:

table: ?:products

col: status

if it was seperate would be FROM ?:products WHERE product_id = "ID" AND status = "A"

anyone know how i can merge that in to the above query, as at the moment it gets the min price but gets the min price for even disabled products so need to add that status condition in to only do active products

Many thanks in advanced!

Eric · Accepted Answer · 2012-01-06 19:37:49Z

5

Do joins, my good sir.

select
    min(pp.price)
from
    products p
    inner join product_prices pp on
        p.product_id = pp.product_id
    inner join products_categories pc on
        p.product_id = pc.product_id
where
    pc.category_id = ?i
    and p.status = 'A'

What you've got there are cross joins. While you can do them ad nauseum, just like with inner joins (or any join, really), it's not very clear what's getting joined to what.

answered Jan 6, 2012 at 19:37

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

additional mysql select query condition?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related