Page 1 of 1

Peoblem with JOIN and AND / OR

Posted: Sun Sep 19, 2010 9:09 pm
by me!
This is what I have:

Code: Select all

$query = "SELECT id, generator_id, city, last_name, first_name, business_name, last_service, customers.type AS customer_type FROM customers, generators
            WHERE customers.id = generators.customer_id
            AND generators.track_service='yes'
            OR generators.flag='on'
            ORDER BY generators.flag, generators.last_service, customers.last_name, customers.business_name";
It is part of a service system for generator maintenance, what I want is a list of all costumes that we are tracking service for and only the ones I care about.
So I put in "AND generators.track_service='yes'", but if there is a problem win any generator we need it to also show up on the top of the list, so I did "OR generators.flag='on'"

The problem is whenever there is a generator with the "flag" set to on the quarry shows all records in the BD?

Re: Peoblem with JOIN and AND / OR

Posted: Sun Sep 19, 2010 9:53 pm
by Jonah Bron
I'm not entirely sure I understand your question, but try this:

Code: Select all

$query = "SELECT id, generator_id, city, last_name, first_name, business_name, last_service, customers.type AS customer_type FROM customers, generators
            WHERE customers.id = generators.customer_id
            AND (generators.track_service='yes'
            OR generators.flag='on')
            ORDER BY generators.flag, generators.last_service, customers.last_name, customers.business_name";
If that doesn't work try

Code: Select all

$query = "SELECT id, generator_id, city, last_name, first_name, business_name, last_service, customers.type AS customer_type FROM customers, generators
            WHERE (customers.id = generators.customer_id
            AND generators.track_service='yes')
            OR generators.flag='on'
            ORDER BY generators.flag, generators.last_service, customers.last_name, customers.business_name";