Why SQL query return additional rows when using multiple OR condition?

Question

Hello I have Filter option in my program.

When i use an single option like when i filter only by STATUS=SCHEDULED i get the correct list as shown bellow

But when i give multiple condition then the SQl query returns more additional rows irrelevant to the date. like bellow

i am trying to filter the order with STATUS=SCHEDULED and CUSTOMER ID=87.

I took reference from here1 here2

And bellow is my SQL query

SELECT
    *
FROM
    workforce_customerorder
WHERE
    ORDER_ID LIKE '$sOrder'
UNION
SELECT
    *
FROM
    workforce_customerorder
WHERE
    CUSTOMER_ID LIKE '%$sCustomerID%'
UNION
SELECT
    *
FROM
    workforce_customerorder
WHERE
    AGENT_NUMBER LIKE '%$sAgentNumber%'
UNION
SELECT
    *
FROM
    workforce_customerorder
WHERE
STATUS LIKE
    '$sStatus'
UNION
SELECT
    *
FROM
    workforce_customerorder
WHERE
    GST_NUMBER LIKE '$sGST'
UNION
SELECT
    *
FROM
    workforce_customerorder
WHERE
    DATE(ORDER_DATE) BETWEEN '$sOrderDateFrom' AND '$sOrderDateTo'

I need the best SQl query. Thanks in advance

Ok After I tried using AND this what i got

You don't need all those unions, you could just use a single query with a `WHERE` clause containing the conditions. Also note that `UNION` will remove duplicates; this could have something to do with your complaint about not seeing the correct number of records in the result set. — Tim Biegeleisen, Jan 04 '18 at 13:39
On the 3rd picture what i tried to filter the row with customerID which has the status as SCHEDULED but i got the row with STATUS as OPEN which i dont need — Vignesh, Jan 04 '18 at 14:05
i have the Customer ID= 87 and its Status = SCHEDULED and i want only the row with Customer ID= 87 and its Status = SCHEDULED but im am getting Customer ID= 87 and its Status = OPEN which i do not need — Vignesh, Jan 04 '18 at 14:10
Its because UNION works like OR. I would remove the condition on date range search when testing the suggested answers below (using AND instead UNION). — kc2018, Jan 04 '18 at 14:16

Caleth · Answer 1 · 2018-01-04T14:48:40.240

2

You should have just one query, with a WHERE clause that performs the filtering on only the columns that are set

SELECT *
FROM workforce_customerorder
WHERE
    (ORDER_ID = COALESCE('$sOrder', ORDER_ID)) AND
    (CUSTOMER_ID LIKE '%$sCustomerID%' OR '$sCustomerID' IS NULL) AND
    (AGENT_NUMBER LIKE '%$sAgentNumber%' OR '$sAgentNumber' IS NULL) AND
    (STATUS = COALESCE('$sStatus', STATUS)) AND
    (GST_NUMBER = COALESCE('$sGST', GST_NUMBER)) AND
    (DATE(ORDER_DATE) BETWEEN COALESCE('$sOrderDateFrom', ORDER_DATE) AND COALESCE('$sOrderDateTo', ORDER_DATE))

Note that LIKE without a wildcard is equivalent to =. I find it clearer to specify which are exact matches and which are sub-matches with different syntaxes.

edited Jan 04 '18 at 14:48

answered Jan 04 '18 at 14:37

Caleth

52,200
2
44
75

i am getting error as unexpected token near CUSTOMER_ID,AGENT_ID – Vignesh Jan 04 '18 at 14:45
1

I may have gotten the syntax wrong for `'%' + COALESCE('$sCustomerID', CUSTOMER_ID)) + '%'` – Caleth Jan 04 '18 at 14:45
1

Updated. Is the `LIKE` really necessary? – Caleth Jan 04 '18 at 14:52
ok i do not know much about sql i just know the very basics can u provide me the sql query just to filter rows as i shown in the picture i have just added multiple conditions for filtering i may or may not fill all the conditions necessarily i will vary on situations. Like i have option to filter by DATE, ORDER ID, CUSTOMER ID, STATUS and GST NUMBER some time i may filter using status so the other values will be passed as null so what is best query needed for this condition – Vignesh Jan 04 '18 at 15:04
yes i just pass only the status values and i got 0 results – Vignesh Jan 04 '18 at 15:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162527/discussion-between-vignesh-and-caleth). – Vignesh Jan 04 '18 at 15:26

score 0 · Answer 2 · answered Jan 04 '18 at 13:39

0

Don't use a UNION. You should be using AND in a single WHERE clause.

answered Jan 04 '18 at 13:39

Daniel A. White

187,200
47
362
445

score 0 · Answer 3 · answered Jan 04 '18 at 13:40

0

This behavior you are witnessing is due to use of UNION, Omit UNION and use AND like:

SELECT  * FROM  workforce_customerorder
WHERE
    ORDER_ID LIKE '$sOrder'
AND
    CUSTOMER_ID LIKE '%$sCustomerID%'
AND
    AGENT_NUMBER LIKE '%$sAgentNumber%'
....

When using UNION although one of the subsets may be empty you get the results from all other non empty subsets

answered Jan 04 '18 at 13:40

apomene

14,282
9
46
72

I used the above query and i return 0 result when i filter by status @apomene – Vignesh Jan 04 '18 at 14:01

score 0 · Answer 4 · answered Jan 04 '18 at 13:42

0

If you are doing a UNION that means give me anything in either of these sets, or in both. Try using just AND if you want two+ conditions to be true

answered Jan 04 '18 at 13:42

Jesse Nelson

776
8
21

score 0 · Answer 5 · answered Jan 04 '18 at 13:45

0

Using UNION, you merge your columns. Because you use the same data table in your unions, you have the same column multiple times. You should use AND instead in the WHEREstatement. Below is the corrected query.

SELECT *
FROM workforce_customerorder
WHERE
    ORDER_ID LIKE '$sOrder'
        AND CUSTOMER_ID LIKE '%$sCustomerID%'
        AND AGENT_NUMBER LIKE '%$sAgentNumber%'
        AND STATUS LIKE '$sStatus'
        AND GST_NUMBER LIKE '$sGST'
        AND DATE(ORDER_DATE) BETWEEN '$sOrderDateFrom' AND '$sOrderDateTo'

answered Jan 04 '18 at 13:45

txemsukr

1,017
1
10
32

@Vignesh - you probably need to exclude conditions on fields, not filled on web form. – Arvo Jan 04 '18 at 14:11
Ok about this query what happens when one or more condition is nul – Vignesh Jan 04 '18 at 14:16
An empty field will result in : if the condition have a '%' in the LIKE expression it will be omitted and the output will be the matching rows to the other conditions. Else, if it doesn't contain a '%' it won't return anything. – txemsukr Jan 04 '18 at 14:23
hey i have added the picture of the query which u suggested – Vignesh Jan 04 '18 at 14:27
Are you coding your site with PHP ? Then you can set a default value to your variable instead of NULL. – txemsukr Jan 04 '18 at 14:29
1

something along the lines of `WHERE (ORDER_ID = COALESCE('$sOrder', ORDER_ID)) AND ...`, so that you have vacuously true parts of your WHERE clause when a parameter is null – Caleth Jan 04 '18 at 14:30
yes im using php but still i just can not get it, i am saying i have multiple filter options i may pass many value or i may just pass only single value but why its not returning any result even when i mention those values with null – Vignesh Jan 04 '18 at 14:39
Do you have some data in your table ? Then can you try filling all fields of the form and submit to see if you got some data ? – txemsukr Jan 04 '18 at 14:49

score 0 · Accepted Answer · answered Jan 04 '18 at 23:58

ok finally i found the solution

     $query_order_id  = ($sOrder  != "") ? " AND (ORDER_ID LIKE '$sOrder') " : "";
    $query_customer_id = ($sCustomerID  != "") ? " AND (CUSTOMER_ID LIKE '%".$sCustomerID."' OR '".$sCustomerID."' IS NULL) " : "";
    $query_sAgentNumber =   ($sAgentNumber  != "") ? " AND (AGENT_NUMBER LIKE '%".$sAgentNumber."%' OR '".$sAgentNumber."' IS NULL) " : "";
    $query_sStatus =   ($sStatus  != "") ? " AND (STATUS LIKE '%".$sStatus."%' OR '".$sStatus."' IS NULL) " : "";
    $query_sGST =   ($sGST  != "") ? " AND (GST_NUMBER LIKE '%".$sGST."%' OR '".$sGST."' IS NULL) " : "";
    $query_sOrderDateFrom = ($sOrderDateFrom != "") ? " AND (DATE(ORDER_DATE) BETWEEN COALESCE('$sOrderDateFrom', ORDER_DATE) AND COALESCE('$sOrderDateTo', ORDER_DATE)) " : "";
//sql query here

    $sql = "SELECT * FROM workforce_customerorder where ORDER_ID IS NOT NULL".$query_order_id.$query_customer_id.$query_sAgentNumber.$query_sStatus.$query_sGST.$query_sOrderDateFrom;
    }

It is same as what @Caleth showed but the one which matched exactly is by applying ternary operator before passing the variable to SQL query. Thank you every one you answers...

Why SQL query return additional rows when using multiple OR condition?

6 Answers6