Derived Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Derived Table

Author

Topic

ofsouto
Starting Member

22 Posts

Posted - 2005-05-19 : 08:27:01

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.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 21:43:09

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
)

Cursors are for those who doesn't know how to use SQL

raclede™

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources