Not In (Select - 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)

Not In (Select

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2004-02-26 : 15:58:02

So far I was doing this which was working fine:

AND tblLBABanquePays.IDLBABanque NOT IN (SELECT IDLBABanque
FROM tblLBABanqueClient WHERE IDRacines =" & pIDRacine & ")"

Now I would like to do something like this but is doesn't look good, is there a way to perform this ?

AND tblLBABanquePays.IDLBABanque, tblLBAOtherTable.IDRacines NOT IN (SELECT IDLBABanque,IDRacines
FROM tblLBABanqueClient WHERE IDRacines )

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-26 : 16:24:54

Huh?

Oh I see it now

Coorelate them or concat

[very hokey]
AND tblLBABanquePays.IDLBABanque+tblLBAOtherTable.IDRacines NOT IN (SELECT IDLBABanque+IDRacines
FROM tblLBABanqueClient WHERE IDRacines )
[/very hokey]

Or

NOT EXISTS (SELECT * FROM tblLBABanqueClient c
WHERE IDRacines =" & pIDRacine & ")"
AND a.IDLBABanque = c.IDLBABanque
AND b.IDRacines = c.IDLBABanque

Which doesn't make much sense to me....can you post the whole thing?

Brett

8-)

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-02-26 : 16:32:40

quote:
AND tblLBABanquePays.IDLBABanque, tblLBAOtherTable.IDRacines NOT IN (SELECT IDLBABanque,IDRacines
FROM tblLBABanqueClient WHERE IDRacines )

you have a "," after your AND if you are trying to run this you are going to get an error.. try an OR or an AND instead

AND tblLBABanquePays.IDLBABanque [AND / OR] tblLBAOtherTable.IDRacines NOT IN (SELECT IDLBABanque ,IDRacines
FROM tblLBABanqueClient WHERE IDRacines )

can you post the whole query?

Sachin

Corobori
Posting Yak Master

105 Posts

Posted - 2004-02-26 : 17:01:32

The whole thing is something like this


INSERT INTO tblLBABanqueClient ( IDLBABanque, IDRacines )
SELECT DISTINCT  tblLBABanque.IDLBABanque ,  tblLBABanqueClient.IDRacines
FROM tblNPA 
INNER JOIN tblRelations ON tblNPA.IDNpa = tblRelations.NPA INNER 
JOIN tblLBABanquePays ON tblNPA.IDPays = tblLBABanquePays.IDPays 
INNER JOIN tblLBABanque ON tblLBABanquePays.IDLBABanque = tblLBABanque.IDLBABanque 
WHERE IDRelation = 1 
AND TypeCompte = 1 
AND PersonneMorale = 0 
AND NOT EXISTS (SELECT * FROM tblLBABanqueClient c
WHERE ??.IDLBABanque = ??.IDLBABanque
AND ??.IDRacines = ??.IDLBABanque)

The idea of the NOT EXISTS is to avoid having duplicate key in my tblLBABanqueClient

jean-luc
www.corobori.com

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-26 : 18:10:38

If you could show us your table DDL it would help. I think you're looking for something like this though.

INSERT INTO tblLBABanqueClient ( IDLBABanque, IDRacines )
SELECT DISTINCT
lb.IDLBABanque ,
lbc.IDRacines
FROM
tblNPA n
INNER JOIN tblRelations ON n.IDNpa = r.NPA INNER
INNER JOIN tblLBABanquePays lbp ON n.IDPays = lbp.IDPays
INNER JOIN tblLBABanque lb ON lbp.IDLBABanque = lb.IDLBABanque
LEFT OUTER JOIN tblLBABanqueClient lbc ON lb.IDLBABanque = lbc.IDLBABanque
AND lbc.IDRacines = lbc.IDLBABanque
WHERE
IDRelation = 1
AND TypeCompte = 1
AND PersonneMorale = 0
AND lbc.IDLBABanque IS NULL

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources