CODEWITHSUNDEEP
mysqlsqlms-access
user12475155
user12475155

Reputation: 15

Excluding records based on multiple criteria with specific values in two columns

Information:

I have one table with three columns: Book, Store_1, and Store_2. I'm using MS Access, but am also familiar with MySQL as well.

_______________________________________________
      Book        |   Store_1   |   Store_2   |
Lord of the Rings |   Paper     |             |
Ender's Game      |   Digital   |   Digital   |
Mistborn          |   Digital   |             |
Spines            |             |   Paper     |
Grapes of Wrath   |             |   Digital   |
Artemis Fowl      |             |             |
Redwall           |   Paper     |   Paper     |
Perloo the Bold   |   Digital   |   Paper     |
_______________________________________________

Issue

I need to write a single query that will exclude a row if it meets any of the following criteria:

The table should look like this after running the query:

_______________________________________________
      Book        |   Store_1   |   Store_2   |
Spines            |             |   Paper     |
Grapes of Wrath   |             |   Digital   |
Redwall           |   Paper     |   Paper     |
Perloo the Bold   |   Digital   |   Paper     |
_______________________________________________

What I've Tried

I'm going to be honest and say I don't know how to combine all three criteria into one statement. The closest I've got... is getting the opposite of what I want, which is with this:

SELECT *
FROM Books
WHERE (Store_2 IS NULL) OR (Store_1 AND Store_2 = "Digital") OR (Store_1 AND Store_2 IS NULL)
;

It gives me the books I do not want, which is a start, but I cannot figure out how to reverse it. I get a syntax error when adding NOT IN after WHERE. Thanks for any help, advice, or guidance down the right road you can provide.

Upvotes: 0

Views: 1337

Answers (3)

ComputerVersteher
ComputerVersteher

Reputation: 2686

@zip was close, but has a hidden backtick in secondStore_1 IS NOT NULLand the null-safe compare for any stores not equal to 'digital' or NULL, is to checkStore_1for Null, as Store_2 can't be Null, because of first criteria.

Now if one of those criteria is met, data is displayed.

SELECT *
FROM Books
WHERE Store_2 IS NOT NULL
    AND (
        Store_1 <> 'Digital'
        OR Store_2 <> 'Digital'
        OR Store_1 IS NOT NULL
        );

Common Errors with Null may enlight OP on getting null-safe inMs Access.

One last remark.

Fieldnames with a number (especialy in sequence), smell like bad normalization. Usually you would have a column for the store number, what makes you free to have an unlimited number of stores.

For later display the data with store-number in columns, you can create a crosstab-query

Upvotes: 0

Nick
Nick

Reputation: 147146

Your second NULL condition is superfluous as it can only be true if the first one is true. So you can simplify your expression to:

SELECT *
FROM Books
WHERE Store_2 IS NOT NULL AND NOT (Store_2 <=> 'Digital' AND Store_1 <=> 'Digital')

Output:

Book                Store_1     Store_2
Spines                          Paper
Grapes of Wrath                 Digital
Redwall             Paper       Paper
Perloo the Bold     Digital     Paper

Demo on dbfiddle

Upvotes: 3

zip
zip

Reputation: 4061

THis eill do it:

SELECT *
FROM Books
WHERE (Store_2 IS NOT NULL) AND (Store_1 <> 'Digital' OR Store_2 <> 'Digital') AND (Store_1 ÌS NOT NULL OR Store_2 IS NOT NULL)
;

Upvotes: 0

Related Questions