Mutiple AND in MYSQL Query not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
princeofvegas
Forum Newbie
Posts: 11
Joined: Wed Jun 30, 2010 1:21 am

Mutiple AND in MYSQL Query not working

Post by princeofvegas »

I am running an SQL query and everything is working great with it just the way that it is supposed to. However I just had to ass the coupon_status = 'ACTIVE' portion to it and for some reason the query is ignoring that part of it. Is it possible to have multiple AND statements in a query and if so what is the proper way to do it. Any help would be appreciated.

Code: Select all

"SELECT * FROM coupons WHERE category_id IN ($selected_categories) OR category_id2 IN ($selected_categories) OR category_id3 IN ($selected_categories) OR category_id4 IN ($selected_categories) OR category_id5 IN ($selected_categories) && coupon_zip IN ($homezip, $zipsinrange) && coupon_status = 'ACTIVE' $sort LIMIT ".$limits.",$max"
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Mutiple AND in MYSQL Query not working

Post by McInfo »

In MySQL, AND has a higher precedence than OR. [MySQL Manual: Operator Precedence] Therefore, in your query, "category_id5 IN ... && coupon_zip IN ..." will be evaluated first. Next, that result will be used in the comparison "result && coupon_status = 'ACTIVE'". Then the ORs will be evaluated, starting with the leftmost comparison following "WHERE".

Use parentheses to group the comparisons so they will be evaluated in the intended order.

The appearance of sequential field names (category_id, category_id2, category_id3, ...) is a symptom of a poorly designed schema. Tables should not expand horizontally (by adding columns), they should expand only vertically (by adding rows). If you find yourself adding columns that are related such that they can be named sequentially, consider creating a separate table.

It appears that you have a coupon entity and a category entity. If coupons can belong to multiple categories and categories can contain multiple coupons, the coupons and categories are said to have a many-to-many relationship. Supposing that two tables already exist for coupons and categories, a third table (coupon_category) is needed for the relationship.
princeofvegas
Forum Newbie
Posts: 11
Joined: Wed Jun 30, 2010 1:21 am

Re: Mutiple AND in MYSQL Query not working

Post by princeofvegas »

Thank you very much for your help and input. That is making more sense to me now. I am still in the learning proces of implementing databases with PHP. I have one more question on that topic.

If I create a new table (i.e. coupons_category), how do I join that table into my query so that it searches coupons_category with $selected_categories.

Thank you again for your help.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Mutiple AND in MYSQL Query not working

Post by McInfo »

Given this schema and data...

Code: Select all

DESCRIBE `coupon`
# +--------+-----------------+------+-----+---------+----------------+
# | Field  | Type            | Null | Key | Default | Extra          |
# +--------+-----------------+------+-----+---------+----------------+
# | id     | int(3) unsigned | NO   | PRI |         | auto_increment |
# | name   | varchar(20)     | NO   |     |         |                |
# | active | tinyint(1)      | NO   |     |       1 |                |
# +--------+-----------------+------+-----+---------+----------------+

DESCRIBE `category`
# +-------+-----------------+------+-----+---------+----------------+
# | Field | Type            | Null | Key | Default | Extra          |
# +-------+-----------------+------+-----+---------+----------------+
# | id    | int(3) unsigned | NO   | PRI |         | auto_increment |
# | name  | varchar(20)     | NO   |     |         |                |
# +-------+-----------------+------+-----+---------+----------------+

DESCRIBE `coupon_category`
# +-------------+-----------------+------+-----+---------+-------+
# | Field       | Type            | Null | Key | Default | Extra |
# +-------------+-----------------+------+-----+---------+-------+
# | coupon_id   | int(3) unsigned | NO   | PRI |         |       |
# | category_id | int(3) unsigned | NO   | PRI |         |       |
# +-------------+-----------------+------+-----+---------+-------+

SELECT * FROM `coupon`
# +----+----------+--------+
# | id | name     | active |
# +----+----------+--------+
# |  1 | Coupon01 |      1 |
# |  2 | Coupon02 |      1 |
# |  3 | Coupon03 |      0 |
# |  4 | Coupon04 |      1 |
# |  5 | Coupon05 |      1 |
# +----+----------+--------+

SELECT * FROM `category`
# +----+-------+
# | id | name  |
# +----+-------+
# |  1 | Cat01 |
# |  2 | Cat02 |
# |  3 | Cat03 |
# |  4 | Cat04 |
# |  5 | Cat05 |
# +----+-------+

SELECT * FROM `coupon_category`
# +-----------+-------------+
# | coupon_id | category_id |
# +-----------+-------------+
# |         1 |           2 |
# |         1 |           3 |
# |         2 |           4 |
# |         3 |           5 |
# |         5 |           4 |
# |         5 |           6 |
# |         6 |           5 |
# +-----------+-------------+
This query selects all associated coupons and categories. Notice that INNER JOIN is used to exclude [Coupon 5, Category 6] and [Coupon 6, Category 5] because there is no Coupon 6 or Category 6.

Code: Select all

SELECT
    cc.coupon_id,
    cp.name AS coupon_name,
    cc.category_id,
    ct.name AS category_name
FROM
    coupon_category AS cc
INNER JOIN
    coupon AS cp
    ON (cp.id = cc.coupon_id)
INNER JOIN
    category AS ct
    ON (ct.id = cc.category_id);
# +-----------+-------------+-------------+---------------+
# | coupon_id | coupon_name | category_id | category_name |
# +-----------+-------------+-------------+---------------+
# |         1 | Coupon01    |           2 | Cat02         |
# |         1 | Coupon01    |           3 | Cat03         |
# |         2 | Coupon02    |           4 | Cat04         |
# |         3 | Coupon03    |           5 | Cat05         |
# |         5 | Coupon05    |           4 | Cat04         |
# +-----------+-------------+-------------+---------------+
This query is the same as above with an additional WHERE clause. Notice that the row [Coupon 3, Category 5] is missing because Coupon 3 is not active. Also notice that row [Coupon 1, Category 2] is missing because Category 2 is not in the set {3, 4, 5}.

Code: Select all

SELECT
    cc.coupon_id,
    cp.name AS coupon_name,
    cc.category_id,
    ct.name AS category_name
FROM
    coupon_category AS cc
INNER JOIN
    coupon AS cp
    ON (cp.id = cc.coupon_id)
INNER JOIN
    category AS ct
    ON (ct.id = cc.category_id)
WHERE
    cp.active = 1
    AND cc.category_id IN (3, 4, 5);
# +-----------+-------------+-------------+---------------+
# | coupon_id | coupon_name | category_id | category_name |
# +-----------+-------------+-------------+---------------+
# |         1 | Coupon01    |           3 | Cat03         |
# |         2 | Coupon02    |           4 | Cat04         |
# |         5 | Coupon05    |           4 | Cat04         |
# +-----------+-------------+-------------+---------------+
Edit: Changed LEFT JOINs to INNER JOINs.
princeofvegas
Forum Newbie
Posts: 11
Joined: Wed Jun 30, 2010 1:21 am

Re: Mutiple AND in MYSQL Query not working

Post by princeofvegas »

Hello again. Thank you for all of your help.

I have been learning more and more about the different types of joins and I have recreated the tables similar to what you have recommended. I now have 3 different tables, coupons, coupons_categories, and business_locations. This is the query that I am using now:

Code: Select all

SELECT * FROM (coupons LEFT JOIN business_locations on coupons.location_id = business_locations.location_id) LEFT JOIN coupons_categories on coupons.coupon_id = coupons_categories.coupon_id WHERE category_id IN ($selected_categories) AND business_zip IN ($homezip, $zipsinrange) AND coupon_status = '1'
Everything works fine until I join the categories table to it. When I have a coupon that is in multiple categories, the left join returns how ever many rows for that coupon depending on the number of categories it is in. Is there a way to run the query so that if there is more than one row in the result for each coupon_id to limit it to one row per coupon_id?

Thank you again for all of your help I am learning a lot and very fast.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Mutiple AND in MYSQL Query not working

Post by McInfo »

Please describe all the tables you have now.
Post Reply