sql delete unrelated records

8 Sep 2005 11:22 PM

Davor

Hello

Lets say i have 2 tables t1 and t2 and they are related (inner join) by ID
and t1_ID respectivley, 1 to many respectivley..

Now, lets sey they are records in column ID that aren't related to any t1_ID
column. How do i write the SQL syntax to show those that are related to
nothing.

Any suggestion is appreciated.

Greets
Davor

8 Sep 2005 11:28 PM

Tom Moreau

Try:

select
    *
from
    t1
where not exists
(
    select
        *
    from
        t2
    where
        t2.t1_ID = t1.ID
)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Davor" <Da***@discussions.microsoft.com> wrote in message
news:0B9F5734-3B5D-4239-AF01-35210F71404B@microsoft.com...
Hello

Lets say i have 2 tables t1 and t2 and they are related (inner join) by ID
and t1_ID respectivley, 1 to many respectivley..

Now, lets sey they are records in column ID that aren't related to any t1_ID
column. How do i write the SQL syntax to show those that are related to
nothing.

Any suggestion is appreciated.

Greets
Davor

9 Sep 2005 10:30 AM

Davor

Hello

Here is the tested one

DELETE DISTINCTROW t1.ID
FROM [t1]
WHERE ((Exists (select * from [t2] where t1.ID = t2.t1_ID))=False);

Didn't knew this "exists" syntax existed.

Thanks Tom, you'r the man ;)

Greets
Davor

9 Sep 2005 11:09 AM

Tom Moreau

This won't work in SQL Server, which is the theme of this newsgroup. Are
you running this on Access?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Davor" <Da***@discussions.microsoft.com> wrote in message
news:5F4AA1CB-A6D8-41FD-B57E-DE140A5B5655@microsoft.com...
Hello

Here is the tested one

DELETE DISTINCTROW t1.ID
FROM [t1]
WHERE ((Exists (select * from [t2] where t1.ID = t2.t1_ID))=False);

Didn't knew this "exists" syntax existed.

Thanks Tom, you'r the man ;)

Greets
Davor

9 Sep 2005 11:34 AM

Davor

Yes

Haven't found any Newsgroup about SQL better than this one.

Greets
Davor

Show quote

"Tom Moreau" wrote:

> This won't work in SQL Server, which is the theme of this newsgroup. Are
> you running this on Access?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Davor" <Da***@discussions.microsoft.com> wrote in message
> news:5F4AA1CB-A6D8-41FD-B57E-DE140A5B5655@microsoft.com...
> Hello
>
> Here is the tested one
>
> DELETE DISTINCTROW t1.ID
> FROM [t1]
> WHERE ((Exists (select * from [t2] where t1.ID = t2.t1_ID))=False);
>
> Didn't knew this "exists" syntax existed.
>
> Thanks Tom, you'r the man ;)
>
> Greets
> Davor
>
>

10 Sep 2005 1:27 AM

Tom Moreau

In future, please state up front that your problem is not SQL
Server-related. This way, we can better direct you. For Access issues,
check out microsoft.public.access.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Davor" <Da***@discussions.microsoft.com> wrote in message
news:09C6CD4C-C7B7-434E-B8AF-DB1B77379EA0@microsoft.com...
Yes

Haven't found any Newsgroup about SQL better than this one.

Greets
Davor

Show quote

"Tom Moreau" wrote:

> This won't work in SQL Server, which is the theme of this newsgroup. Are
> you running this on Access?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Davor" <Da***@discussions.microsoft.com> wrote in message
> news:5F4AA1CB-A6D8-41FD-B57E-DE140A5B5655@microsoft.com...
> Hello
>
> Here is the tested one
>
> DELETE DISTINCTROW t1.ID
> FROM [t1]
> WHERE ((Exists (select * from [t2] where t1.ID = t2.t1_ID))=False);
>
> Didn't knew this "exists" syntax existed.
>
> Thanks Tom, you'r the man ;)
>
> Greets
> Davor
>
>