How to exclude something from this mssql/php query?

Question

I've got this:

SELECT TOP 100 
    IDNum, 
    IDName, 
    Nation, 
    (SELECT 
        SUM(Loyalty) 
    FROM 
        USERDATA 
    WHERE 
        USERDATA.Knights = KNIGHTS.IDNum 
        AND USERDATA.Authority IN(1, 2)
    ) as ClanLoyalty 
FROM 
    KNIGHTS 
ORDER BY 
    ClanLoyalty DESC

but I want to exclude Knights (21 and 25) so knights IDNum 21 and 25 WONT be included in that query ?

How to exclude them ?

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

Wouldn't this be a simple where clause on your outer query?

SELECT TOP 100 
    IDNum, 
    IDName, 
    Nation, 
    (SELECT 
        SUM(Loyalty) 
    FROM 
        USERDATA 
    WHERE 
        USERDATA.Knights = KNIGHTS.IDNum 
        AND USERDATA.Authority IN(1, 2)
    ) as ClanLoyalty 
FROM 
    KNIGHTS 
where
    knights.idnum not in (21,22)
ORDER BY 
    ClanLoyalty DESC

If you exclude it from the main query, they won't be matched on the inner query either as you have them joining on the IDNum field anyhow.

On that note, you might also want to take a read of this Q&A that I put together which covers off a lot of SQL like this.

edited May 23 '17 at 11:52

Community

1
1

answered Sep 26 '13 at 23:31

Fluffeh

33,228
16
67
80

Wont this make the query to load slower ? Or its totally fine as it should be ? – nRov Sep 26 '13 at 23:32
Can you please let me know ? – nRov Sep 26 '13 at 23:36
@nRov Not really. I mean, the `not in` could be replaced with a `not=21 or not 25` but I doubt that would really impact things much. – Fluffeh Sep 26 '13 at 23:39
So, its totally fine to use it as you provided it above right ? – nRov Sep 26 '13 at 23:40
I would say so, I mean I haven't seen your database, don't know the indexes or how much data you have in there (is it millions, billions or rows?) but I don't see an issue with it based on the info we have here :) – Fluffeh Sep 26 '13 at 23:42
Okay, thank you a lot. It's been appreciated. Question accepted. – nRov Sep 26 '13 at 23:45
Haha, okay, thanks for that, but you have tested it and tinkered with it right? You don't need to accept things right off the bat - I mean, I am confident that it works, seems a pretty simple modification to the query and all, but don't feel pressured into accepting right away. – Fluffeh Sep 26 '13 at 23:46
It looks like it should work right ? :) What issue it can produce ? I just didnt know correctly where to place that NOT IN but you pointed me to the correct one as I can see. Am I right ? – nRov Sep 27 '13 at 00:09
@nRov It should be perfectly fine. The only issue that might come up is if you have (literally) billions of rows of data and performance comes into play, but that is exceptionally unlikely. Give it a run, I am highly confident that you will see it works exactly as you wanted and does so very well. :) – Fluffeh Sep 27 '13 at 00:27

How to exclude something from this mssql/php query?

1 Answers1