SQL query inside a query

Question

Allow me to share my query in an informal way (not following the proper syntax) as I'm a newbie - my apologies:

select * from table where
    (
            (category = Clothes)
            OR
            (category = games)
    )
    AND
    (
            (Payment Method = Cash) OR (Credit Card)
    )

This is one part from my query. The other part is that from the output of the above, I don’t want to show the records meeting these criteria:

Category = Clothes

Branch = B3 OR B4 OR B5

Customer = Chris

VIP Level = 2 OR 3 OR 4 OR 5

SQL is not part of my job but I’m doing it to ease things for me. So you can consider me a newbie. I searched online, maybe I missed the solution.

Thank you,

HimaTech

that query is SQL not MDX ...what sort of database are you querying - a relational database or an OLAP cube in SSAS ? — whytheq, Oct 22 '16 at 07:03
So is mdx involved? When using a powerpivot do you query the db using mdx? I'd suspect the language will be either sql or dax? — whytheq, Oct 22 '16 at 13:28
It's my mistake.. I realised that I'm using SQL in PowerPivot. The title of my question is wrong.. I'm sorry. — HimaTech, Oct 22 '16 at 13:59

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

There's a few ways of doing this (specifically within SQL - not looking at MDX here).

Probably the easiest to understand way would be to get the dataset that you want to exclude as a subquery, and use the not exists/not in command.

SELECT * FROM table
WHERE category IN ('clothes', 'games')
    AND payment_method IN ('cash', 'credit card')
    AND id NOT IN (
        -- this is the subquery containing the results to exclude
        SELECT id FROM table
        WHERE category = 'clothes' [AND/OR]
            branch IN ('B3', 'B4', 'B5') [AND/OR]
            customer = 'Chris' [AND/OR]
            vip_level IN (2, 3, 4, 5)
    )

Another way you could do it is to do left join the results you want to exclude on to the overall results, and exclude these results using IS NULL like so:

SELECT t1.*
FROM table
LEFT JOIN
    (SELECT id FROM table
        WHERE customer = 'chris' AND ...) -- results to exclude
    AS t2 ON table.id = t2.id
WHERE t2.id IS NULL
AND ... -- any other criteria

The trick here is that when doing a left join, if there is no result from the join then the value is null. But this is certainly more difficult to get your head around.

There will also be different performance impacts from doing it either way, so it may be worth looking into it. This is probably a good place to start: What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

edited May 23 '17 at 12:33

Community

1
1

answered Oct 20 '16 at 22:39

Zac Faragher

963
13
26

Thank you for your reply. When I try to run the query, I get an error saying incorrect syntax near the AND NOT IN. I searched the Internet and I see that I have to refer to a column. – HimaTech Oct 21 '16 at 17:22
Trying the LEFT JOIN seems easier for me but I keep getting this error: – HimaTech Oct 22 '16 at 14:00
Error: Ambiguous column name – HimaTech Oct 22 '16 at 14:00
Ok.. I managed to make the LEFT JOIN syntax work but the it is still showing the wrong results. – HimaTech Oct 22 '16 at 14:12
I fixed the issue with the first method - I was getting the syntax between `NOT IN` and `NOT EXISTS` – Zac Faragher Oct 23 '16 at 23:10
I've also updated the second method to make it a bit faster. You should make sure that you're joining `t1` and `t2` on the correct field(s) - this should be unique between the different rows, usually you can just use the primary key. If you're joining other tables on to get the criteria correct, that could affect the results too, and you'll need to sort that out in both the main queries and subqueries – Zac Faragher Oct 23 '16 at 23:14

SQL query inside a query

1 Answers1