Confused?
 

Hello all,

hope i can get some help on this one as im not brilliant with my database scripts.

I have table "A" and table "B", table "A" contains information on a portfolio and table "B" is a joining table which connects this table to a user of the system with a 1-to-many relationship.


I am looking to return all records in table "A" that are not connected to a user in table "B"

i have tried this

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.userid <> 1

but i know this is incorrect but cannot figure out myself how to achieve what i want.

Hopefully someone can point me in the right direction.

Re: Confused?
 

Select * from Table1 a left join Table2 b on (a.id = b.id) where a.id <> '1'

Re: Confused?
 

sorry this does not work.
It simply filters table A's records removing the one with an id of '1'.



its probably easier with a working example.

I have the following tables...

### 'portfolio' - table
'portfolioid' - int [primary key]
'usertitle' - string
'clienttitle' - string

### Records
1, "test title", "test title"
2, "test title 1", "test title a"
3, "test title 2", "test title b"
4, "test title 3", "test title c"

### 'portfolioclient' - table
'portfolioid' - int
'clientid' - int

### Records
1, 1
3, 1
2, 2
4, 2

i wish to return all portfolio records which are not attached to a 'clientid'. E.G. if a client has an ID of '1' then the portfolio records returned should be 2 and 4.

i thought this would work but obviosuly not.

SELECT portfolio.* FROM portfolio LEFT JOIN portfolioclient ON portfolio.portfolioid = portfolioclient.portfolioid WHERE portfolioclient.clientid <> 1

Re: Confused?
 

Ok managed to solve this one myself after a bit of staring at my screen for a long time. For anyone interested this worked for me...

SELECT portfolio.portfolioid FROM portfolio WHERE (NOT EXISTS (SELECT portfolioclient.* FROM portfolioclient WHERE (portfolio.portfolioid = portfolioclient.portfolioid) AND (portfolioclient.clientid = 1)))