SQL Server Performance

Universal Quantification

Discussion in 'T-SQL Performance Tuning for Developers' started by kannan_thebarbarian, Mar 20, 2003.

  1. kannan_thebarbarian New Member

    Is there any way to convert the following query using GROUP BY and COUNT. Its based on the TPC-H benchmark.

    SELECT c_name
    FROM customer
    WHERE NOT EXISTS

    (SELECT l_partkey
    FROM order, lineitem, partsupp, supplier
    WHERE o_custkey = c_custkey
    ANDl_partkey = ps_partkey
    ANDps_supkey = s_supkey

    AND s_nationkeyNOT IN

    (SELECT n_nationkey
    FROMnation
    WHEREn_name = “Germany”))

    peace
  2. Chappy New Member

    Dont know what you want to GROUP BY, but try this.. (untested)



    SELECT c_name
    FROM customer C
    WHERE (
    SELECT count(l_partkey)
    FROM order, lineitem, partsupp, supplier
    WHERE o_custkey = C.c_custkey
    AND l_partkey = ps_partkey
    AND ps_supkey = s_supkey) = 0 and
    (SELECT count(n_nationkey) FROM nation WHERE n.nationkey = C.s_nationkey and n_name = “Germany”) = 0

Share This Page