sql: where subquery not null

Question

I have the following sql query and I want to filter the results where the alias imagefile is null, but I can't get it to work. it's kinda basic sql... sorry for that!

SELECT Categorie.CategorieID, Categorie.Highlight, CategorieTaal.CategorieNaam,
        (SELECT TOP (1) ImageFile
                FROM Artikel WHERE (CategorieID = Categorie.CategorieID) 
                     AND (Onzichtbaar = 0) 
                     AND (NietBestelbaar = 0) 
                     AND (Voorraad = - 1000 OR Voorraad > LevertijdDrempel)
                     ORDER BY Volgnummer, ArtikelID DESC) AS 'imagefile' 
        FROM Categorie INNER JOIN 
                     CategorieTaal ON 
                     Categorie.CategorieID = CategorieTaal.CategorieID  
        WHERE (Categorie.CategorieGroepID = @catgroepid) 
               AND (Categorie.Onzichtbaar = 0) 
               AND (CategorieTaal.TaalCode = @tc) 
       ORDER BY Categorie.Volgnummer, CategorieTaal.CategorieNaam

VoteyDisciple · Accepted Answer · 2009-09-07 13:44:03Z

2

You might want to try this:

SELECT Categorie.CategorieID, Categorie.Highlight, CategorieTaal.CategorieNaam,
FROM Categorie 
INNER JOIN 
    CategorieTaal ON
    Categorie.CategorieID = CategorieTaal.CategorieID  
WHERE (Categorie.CategorieGroepID = @catgroepid) 
    AND (Categorie.Onzichtbaar = 0) 
    AND (CategorieTaal.TaalCode = @tc) 
    AND NOT EXISTS (SELECT 1 ImageFile
        FROM Artikel WHERE (CategorieID = Categorie.CategorieID) 
            AND (Onzichtbaar = 0) 
            AND (NietBestelbaar = 0) 
            AND (Voorraad = - 1000 OR Voorraad > LevertijdDrempel))
ORDER BY Categorie.Volgnummer, CategorieTaal.CategorieNaam

answered Sep 7, 2009 at 13:44

VoteyDisciple

37.9k5 gold badges100 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JP Hellemons Over a year ago

one small thing: there is a ',' too much on the first line. and 'NOT' should be out the query to get the correct results. execution time : 129 in sql management studio

Eric · Accepted Answer · 2009-09-07 14:02:10Z

1

You can optimize this by using an inner join again, in lieu of trying to use a subquery twice:

SELECT 
    c.CategorieID, 
    c.Highlight, 
    ct.CategorieNaam,
    a.ImageFile
FROM 
    Categorie c 
    INNER JOIN CategorieTaal ct ON 
        c.CategorieID = ct.CategorieID
    INNER JOIN 
        (select 
             CategorieID,
             ImageFile, 
             row_number() over (partition by CategorieID) as rownum
         from
             Artikel
         where
             Onzichtbaar = 0
             and NietBestelbaar = 0
             and (Voorraad = -1000 OR Voorraad > LevertijdDrempel)) a ON
        c.CategorieID = a.CategorieID
        and a.rownum = 1
WHERE 
    c.CategorieGroepID = @catgroepid
    AND c.Onzichtbaar = 0
    AND ct.TaalCode = @tc
ORDER BY c.Volgnummer, ct.CategorieNaam

Since you're using SQL Server (or at least I think you are, with your top and whatnot), you can take advantage of row_number. This will bring back just the ImageFile you need, without having to do two correlated subqueries (usually performance killers).

Also, here you only have to maintain that subquery in one place, not in two different parts of your query.

edited Sep 7, 2009 at 14:02

answered Sep 7, 2009 at 13:55

Eric

95.7k13 gold badges117 silver badges116 bronze badges

1 Comment

JP Hellemons Over a year ago

wow really impressive. i am not that good with sql.it's indeed sql server2005 but when i ran your query i got: The ranking function "row_number" must have an ORDER BY clause. so i changed the line with row_number() to: row_number() over (partition by CategorieID order by artikelid desc) as rownum but when i included the 'client statistics' in the management studio it said that it's not faster then the one i posted with the subquery in the where clause. maybe i am looking at the wrong numbers in sql management studio 2008? how can i measure it for sure? thanks for your reply, i learned from it

Qw3ry · Accepted Answer · 2016-05-26 13:09:31Z

1

found it!!

SELECT  Categorie.CategorieID, Categorie.Highlight, CategorieTaal.CategorieNaam,
        (SELECT TOP (1) ImageFile
            FROM Artikel
            WHERE (CategorieID = Categorie.CategorieID)
                AND (Onzichtbaar = 0)
                AND (NietBestelbaar = 0)
                AND (Voorraad = - 1000
                    OR Voorraad > LevertijdDrempel)
            ORDER BY Volgnummer, ArtikelID DESC) AS 'imagefile'
FROM Categorie
INNER JOIN CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID
WHERE (Categorie.CategorieGroepID = @catgroepid)
    AND (Categorie.Onzichtbaar = 0)
    AND (CategorieTaal.TaalCode = @tc)
    AND ((
            SELECT TOP (1) ImageFile
                FROM Artikel AS Artikel_1
                WHERE (CategorieID = Categorie.CategorieID)
                    AND (Onzichtbaar = 0)
                    AND (NietBestelbaar = 0)
                    AND (Voorraad = - 1000
                        OR Voorraad > LevertijdDrempel)
    ) IS NOT NULL)
ORDER BY Categorie.Volgnummer, CategorieTaal.CategorieNaam

edited May 26, 2016 at 13:09

Qw3ry

1,43919 silver badges36 bronze badges

answered Sep 7, 2009 at 13:42

JP Hellemons

6,04212 gold badges68 silver badges136 bronze badges

1 Comment

JP Hellemons Over a year ago

execution time: 210 :S (worst possible option atm)

Collectives™ on Stack Overflow

sql: where subquery not null

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related