Help with a select with 'and' and 'or'

[ssmith]

I am trying to select total sales of year=2001, month=8 and till=2 but not including trans1 like '%T' or trans2 like '%1'

I have tried the following but it is returning the total sales of everything. What am I doing wrong? Thanks.

select sum(total_sales) from sales_temp
where year=2001
and month=8
and till=2
and (trans1 not like '%T' or trans2 not like '1%')
/

[jmodic]

Originally posted by ssmith
and (trans1 not like '%T' or trans2 not like '1%')

Is the last condition (not like '1%') a typo? Based on your description, it should be not like '%1'. If my assumtion is correct, then your last predicate

and (trans1 not like '%T' or trans2 not like '%1')

will always result in TRUE! You either have to change it into:

and NOT (trans1 like '%T' or trans2 like '%1')

or into:

and (trans1 not like '%T' AND trans2 not like '%1')

[ssmith]

No, its not a typo. I want to select records with TRANS1 NOT LIKE '%T' or TRANS2 NOT LIKE '1%',
e.g. I don't want to include records with TRANS1 of 123T, 457T, 4381T etc. OR with TRANS2 1PWD, 1KJD, 1SMD etc.

[ssmith]

The above queries didn't work. It only counted the records of trans1='%T' AND trans2='1%', eg. record 1 -TRANS1=123T AND TRANS2=1TWD.
I need OR, eg. record 1 - TRANS1=123T, TRANS2=PWD, record 2 - TRANS1=432, TRANS2=1KLD, therefore It should exclude record 1 and record 2.

[chrisrlong]

Okay, not that I'm helping much, but when you have to parse a field to find useful information, it is a sure sign of a bad design. Every atomic piece of information needs to be in its own field. Period. Combining multiple pieces of information into a single field, as is your case, is just asking for trouble.

Also, this is straight logic.

As Jurij points out, you are asking for stuff that is (trans1 not like '%T' or trans2 not like '1%')

Solving this (a necessary skill to have as a SQL coder) goes like this:

NOT (trans1 like '%T' AND trans2 like '1%')

So, you are only excluding values that BOTH start with 1 AND end in T. Not quite what you wanted, eh? Jurij gave you the correct solution - the assumption did not affect that.

- Chris

[jmodic]

Ssmith, I suppose you having been absent from the first lesson of boolean logic, eh?

SQL> select * from t2;

C1
-----------------------------------------------------
457T
1abc
2345

SQL> select * from t2
  2  where not (c1 like '%T' or c1 like '1%');

C1
-----------------------------------------------------
2345

SQL> select * from t2
  2  where (c1 not like '%T' and c1 not like '1%');

C1
-----------------------------------------------------
2345

SQL>

[ssmith]

Thanks for all your help, but as mentioned above I want trans1 not like '%T' OR trans2 not like '1%'. Please note TRANS1 and TRANS2 are different fields, therefore I want to have TRANS1 not ending in '%T' OR TRANS2 not starting with '1%'. Let me put this another way:
Lets change TRANS1 for field FLIGHT_NO and trans2 for field FLIGHT_OPERATOR, I want to select all records with year=2001, month=8 and till=2 but exclude 'transit' (flight_no not like '%T') OR 'transfer' passengers (flight_operator not like '1%).

select sum(total_sales) from sales_temp
where year=2001
and month=8
and till=2
and (FLIGHT_NO not like '%T' or FLIGHT_OPERATOR not like '1%')
/

I have done the following to get the result:
select sum(total_sales) from sales_temp
where year=2001 and month=8
and till=2; ----- Result 217358.32
THEN
select sum(total_sales) from sales_temp
where year=2001 and month=8 and till=2
and flight_no like '%T' ----- Result 1337.10
THEN
select sum(total_sales) from sales_temp
where year=2001 and month=8 and till=2
and flight_operator like '1%' ---- Result 1004.15
Giving the result --- 217358.32 - 1337.10 - 1004.15 = 215017.07.
Obviously I want an easier method of doing the above calculation. Can anyone help? Thanks.

[chrisrlong]

I don't know how we can make this any clearer, but here is one final try...

YOU WANT AN AND!!!!!!!!!

Okay, now.... why do you want an AND?

Because, if you have the following data:

RECORD_ID TRANS1 TRANS2
--------- ------ ------
1 xxxxxT 1xxxxx
2 xxxxxT 2xxxxx
3 xxxxxZ 1xxxxx
4 xxxxxZ 2xxxxx

...which records do you want to get rid of? From what I can tell, you want to get rid of rows 1, 2, and 3. If I'm incorrect, the rest of this will obviously be wrong as well.

Now, let's try your original WHERE clause...

WHERE
TRANS1 NOT LIKE '%T' OR
TRANS2 NOT LIKE '1%'

So, in order for a record to pass these predicates, it needs to EITHER have field 1 NOT end with a T **OR** have field 2 NOT start with a 1

Record 1: Fails both conditions, so is properly excluded
Record 2: Field2 does NOT start with a 1, so passes the second predicate. Since this is an **OR**, passing EITHER of the predicates is sufficient for inclusion in the final result, so this record passes.
Record 3: Field 1 does NOT end with a T, so this record passes as well.
Record 4: Both predicates succeed, so this record obiously passes.

So, the final result is records 2, 3, and 4. Since you only wanted record 4, this is obviuously not correct.

CREATE TABLE CRL_TEST(RECORD_ID NUMBER, TRANS1 VARCHAR2(6), TRANS2 VARCHAR2(6));

INSERT INTO CRL_TEST VALUES(1, 'xxxxxT', '1xxxxx');
INSERT INTO CRL_TEST VALUES(2, 'xxxxxT', '2xxxxx');
INSERT INTO CRL_TEST VALUES(3, 'xxxxxZ', '1xxxxx');
INSERT INTO CRL_TEST VALUES(4, 'xxxxxZ', '2xxxxx');

SELECT * FROM CRL_TEST WHERE TRANS1 NOT LIKE '%T' OR TRANS2 NOT LIKE '1%';

2 xxxxxT 2xxxxx
3 xxxxxZ 1xxxxx
4 xxxxxZ 2xxxxx


Okay, now let's talk about WHERE clauses for a moment. They specify what we want to INCLUDE in our result set. We only want to INCLUDE records where neither the first field ends in T nor the second field starts with 1. Therefore, this means that we only want records where the first field ends with something other than T AND the second field starts with something other than 1.

WHERE
TRANS1 NOT LIKE '%T' AND
TRANS2 NOT LIKE '1%'


SELECT * FROM CRL_TEST WHERE TRANS1 NOT LIKE '%T' AND TRANS2 NOT LIKE '1%';

4 xxxxxZ 2xxxxx


And as a final note...

I suspect that you are running our examples and finding out that they don't match your original query. You are therefore incorrectly concluding that our solutions must be wrong.

However, it is your original query that is wrong.

Consider our original example table with a new column

CREATE TABLE CRL_TEST(RECORD_ID NUMBER, TRANS1 VARCHAR2(6), TRANS2 VARCHAR2(6), VALUE NUMBER)
INSERT INTO CRL_TEST VALUES(1, 'xxxxxT', '1xxxxx', 2);
INSERT INTO CRL_TEST VALUES(2, 'xxxxxT', '2xxxxx', 4);
INSERT INTO CRL_TEST VALUES(3, 'xxxxxZ', '1xxxxx', 8);
INSERT INTO CRL_TEST VALUES(4, 'xxxxxZ', '2xxxxx', 16);


RECORD_ID TRANS1 TRANS2 VALUE
--------- ------ ------ -----
1 xxxxxT 1xxxxx 2
2 xxxxxT 2xxxxx 4
3 xxxxxZ 1xxxxx 8
4 xxxxxZ 2xxxxx 16

Now, since you are looking for only row 4, you want a value of ***16*** back.

In your last reply, you said that you did it this way:

SELECT SUM(VALUE) FROM CRL_TEST

30

SELECT SUM(VALUE) FROM CRL_TEST WHERE TRANS1 LIKE '%T'

6

SELECT SUM(VALUE) FROM CRL_TEST WHERE TRANS2 LIKE '1%'

10

So, your answer is 30 - 6 - 10 = 14!!?!?!??

How is this possible? We expected a 16 and got a 14. That is because the first record (with a value of 2) satisfied BOTH the second AND third queries. Therefore, you subtracted its value from the total TWICE, which is why we were off by 2. Your logic was incorrect.

So, hopefully this will show both why you want the AND, and yet why using the AND will not match your previous queries.

...and that's the best I can do,

- Chris


[Edited by chrisrlong on 10-17-2002 at 09:43 AM]

[ssmith]

OK, let me try and make it clearer....

These are the results I get when using the solutions:

Using the solution:

1 select sum(total_sales) from sales_temp
2 where year=2001
3 and month=8
4 and till=2
**5* and not (FLIGHT like '%T' AND OPERATOR like '1%') ****
SQL> /

SUM(TOTAL_SALES)
----------------
2200838.18

Selecting the TOTAL:
SQL>

1 select sum(total_sales) from sales_temp
2 where year=2001
3 and month=8
4* and till=2
SQL> /

SUM(TOTAL_SALES)
----------------
2200838.18

Therefore the above solution did not exclude flight of '%T' or operator '1%'.

selecting with flight of '%T'

1 select sum(total_sales) from sales_temp
2 where year=2001
3 and month=8
4 and till=2
5* and flight like '%T'
SQL> /

SUM(TOTAL_SALES)
----------------
3755

Selecting with operator of '1%'
1 select sum(total_sales) from sales_temp
2 where year=2001
3 and month=8
4 and till=2
5* and operator like '1%'
SQL> /

SUM(TOTAL_SALES)
----------------
14078.38

total=2200838.18 - 3755 - 14078.38 =2183004.80
SQL>
1 select sum(total_sales) from sales_temp
2 where year=2001
3 and month=8
4 and till=2
5* and flight like '%T' and operator like '1%'
SQL> /

SUM(TOTAL_SALES)
----------------

The above is due to the data NEVER having flight of '%T' AND operator of '1%' i.e. BOTH

Therefore I conclude that the above solution doesn't work because there isn't a record of flight 'T%' AND operator '1%' (that satifies BOTH), so will not exlude any records.

Tell me where I am going wrong? What should the correct syntax be?

[ssmith]

Thanks for all your help but I have finally worked out the query. The query is:

1 select sum(total_sales) from sales_temp
2 where year=2001
3 and month=8
4 and till=2
5* and not (flight like '%T' OR operator like '1%')
SQL> /

SUM(TOTAL_SALES)
----------------
2183004.8

I DID need the OR (as I thought).