Re: surely, there's got to be a better way

From: Brian W (brianw_at_gold_death_2_spam_rush.com)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 08:32:38 -0700

WOW! That was fast!

Cool! I knew it had to be something simple I was missing!

Thanks
Brian W

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ea794MbaEHA.3204@TK2MSFTNGP09.phx.gbl...
> Subqueries are not evil. However, this may help you achieve your
objective:
>
> select distinct
> a.*
> from
> TABLE_A as a
> left join
> TABLE_B as b on a.id = b.TABLE_A_id
> and a.date = b.TABLE_A_date
> where
> b.TABLE_A_id is null
> and b.TABLE_A_date is null
>
>
> --
> Tom
> -------------------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> "Brian W" <brianw@gold_death_2_spam_rush.com> wrote in message
> news:Oo%23GUJbaEHA.3352@TK2MSFTNGP12.phx.gbl...
> Hello SQL Gurus!
>
> Several years ago I was chastised by a DBA for using sub-querys. And he
> would redo my query without it.
>
> Well, that was then, and this is now, and I'll be damned if I can
> remember/figure out how to do this simple thing without using a sub-query
>
> Basically I have 2 tables; TABLE_A and TABLE_B
>
> TABLE_A
> id INT
> date DATETIME
> name NVARCHAR(x)
>
> TABLE_B
> TABLE_A_id INT
> TABLE_A_date DATETIME
> description NVARCHAR(x)
>
> TABLE_A.id and TABLE_A.date for the primary key in TABLE_A while
TABLE_A_id
> and TABLE_A_date are foreign keys into TABLE_A
>
> Basically, I want a query that will give me everything from TABLE_A if it
is
> NOT in TABLE_B what I came up with was:
> select id, date, name from TABLE_A where (id not in (select TABLE_A_id
from
> TABLE_B))
>
> The other problem is TABLE_A.id is unique now by may not be.
>
> I'd be grateful is someone could help me out here with this simple problem
> with your enormous brains ;-)
>
> TIA
> Brian W
>
>
>
>
>

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
Loading