CODEWITHSUNDEEP
phpmysqlsql
Raj
Raj

Reputation: 3061

How to solve this sql query?

I have the following query to fetch rows from wpr_subscribers provided that the conditions in the query. 

SELECT subscribers.* FROM wpr_subscribers subscribers, wpr_followup_subscriptions subscriptions WHERE 
            subscribers.id=subscriptions.sid AND
            subscribers.active=1 AND
            subscribers.confirmed=1 AND
            subscriptions.type='autoresponder' AND
            subscriptions.eid=$autoresponder_id AND 
            subscriptions.sequence = -2 OR
            CEIL((subscriptions.last_date-subscriptions.doc)/86400) >= $message_index

The query returns two of every row. How do I solve this issue?

Upvotes: 0

Views: 111

Answers (2)

cordsen
cordsen

Reputation: 1701

It is probably your OR statement

        subscriptions.sequence = -2 OR
        CEIL((subscriptions.last_date-subscriptions.doc)/86400) >= $message_index

You should enclose this in parenthesis for whatever the scope of the OR is

Upvotes: 2

Piyush Mattoo
Piyush Mattoo

Reputation: 16095

SELECT DISTINCT subscribers.* FROM wpr_subscribers subscribers, wpr_followup_subscriptions
subscriptions WHERE 
        subscribers.id=subscriptions.sid AND
        subscribers.active=1 AND
        subscribers.confirmed=1 AND
        subscriptions.type='autoresponder' AND
        subscriptions.eid=$autoresponder_id AND 
        subscriptions.sequence = -2 OR
        CEIL((subscriptions.last_date-subscriptions.doc)/86400) >= $message_index

DISTINCT keyword would help you in returning only the unique(distinct) rows in the table. Are you sure on the logic of AND and OR in the query and the unique data that you will now receive is indeed correct. Just for the info, AND operator has precedence over OR however you can override that by using parenthesis.

Upvotes: 3

Related Questions