Derived table

[ofsouto]

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.

 

[checkai]

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 Left Join 
(
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 on tbl.po_tble_code = tab.po_tble_cod
 WHERE tab.po_tble_cod is null

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[ofsouto]

Thank you, chekai. It works.

Could you answer me another question?
What's better? Subqueries or Derived Tables?

I know I could use subquery to get the result but I don't know about the performance.

Thank you again.

Obede
Brazil

 

[checkai]

i think it depends on how much data you're talking about retrieving within the subquery...if you're using a lot of data, you may want to use a temp table.

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."