Derived Table

Question

Post reply

Derived Table

ofsouto

SSC Enthusiast

Points: 179
More actions
May 19, 2005 at 6:39 am

#93049

Derived Tables
I have two queries:
SELECT DISTINCT tot.po_tble_cod
FROM po_tabelatotal tot (NOLOCK),
       po_tabelaitem itm (NOLOCK),
       po_parametro par (NOLOCK)
WHERE tot.po_tblt_cod = itm.po_tblt_cod
   AND itm.po_para_cod = par.po_para_cod
   AND par.po_bloc_cod = 99
returns: 42, 43, 65, 114, 116, 119
SELECT DISTINCT tot.po_tble_cod
FROM po_tabelatotal tot (NOLOCK),
       po_tabelaitem itm (NOLOCK),
       po_parametro par (NOLOCK)
WHERE tot.po_tblt_cod = itm.po_tblt_cod
   AND itm.po_para_cod = par.po_para_cod
   AND par.po_bloc_cod <> 99
returns: 38, 39, 44, 45, 48, 50, 67, 69, 72, 83, 84, 85, 93, 96, 97, 98, 101,
         103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 117, 119, 120
I need this result: 42, 43, 65, 114, 116 (All registers on query A that not exists on query B)
and I tried
SELECT DISTINCT tbl.po_tble_cod
FROM
(
SELECT DISTINCT tot.po_tble_cod
FROM po_tabelatotal tot (NOLOCK),
       po_tabelaitem itm (NOLOCK),
       po_parametro par (NOLOCK)
WHERE tot.po_tblt_cod = itm.po_tblt_cod
   AND itm.po_para_cod = par.po_para_cod
   AND par.po_bloc_cod = 99
) tbl,
(
SELECT DISTINCT tot.po_tble_cod
FROM po_tabelatotal tot (NOLOCK),
       po_tabelaitem itm (NOLOCK),
       po_parametro par (NOLOCK)
WHERE tot.po_tblt_cod = itm.po_tblt_cod
   AND itm.po_para_cod = par.po_para_cod
   AND par.po_bloc_cod <> 99
) tab
WHERE tbl.po_tble_cod <> tab.po_tble_cod
this query returns: 42, 43, 65, 114, 116, 119 (119 exists on both queries)
Is there something wrong or must I use subquery?
What's better: Subqueries or Derived Tables?
Thank you very much.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

I would use a sub-query:

SELECT DISTINCT tot.po_tble_cod

FROM po_tabelatotal tot (NOLOCK),

po_tabelaitem itm (NOLOCK),

po_parametro par (NOLOCK)

WHERE tot.po_tblt_cod = itm.po_tblt_cod

AND itm.po_para_cod = par.po_para_cod

AND par.po_bloc_cod = 99

AND tot.po_tble_cod NOT IN

(SELECT DISTINCT tot.po_tble_cod

FROM po_tabelatotal tot (NOLOCK),

po_tabelaitem itm (NOLOCK),

po_parametro par (NOLOCK)

WHERE tot.po_tblt_cod = itm.po_tblt_cod

AND itm.po_para_cod = par.po_para_cod

AND par.po_bloc_cod <> 99) tab

Karl
source control for SQL Server

Seth Washeck Mr or Mrs. 500 Points: 578 More actions · Answer 2

you could also "left join ... on ... where key is null"

Kindest Regards,

Seth Washeck

If nothing in this world satisfies me, perhaps it is because I was made for another world.
C. S. Lewis