Joining Tables in a view

[ooKmonkey]

Hi, Hope someone can help me here

I'm trying to create a view in MS SQL 2000.

Now I have two tables, one which holds all addresses and a second which holds users at each address and whether they are active.

What I'm trying to do is create a view which displays all addresses that either have no record in the second table or have no active users in the second table.

I'm sure this should be easy but I just seem to be drawing a blank when I actually try to do it.

Any help would be much appreciated

Cheers

[paul]

Can you post the exact table structures?



Paul
_________________
World Countries | Survival Skills

[ooKmonkey]

off the top of my head (sorry im not at work at the moment) they are along the lines of:-

table 1

locationid
LocationName
Address1
Address2
Town
Postcode

table 2

Userid
Locationid
UserName
Email
DateStarted
Active

Thats roughly how they are. If you need anymore info please elt me know

Cheers for the help

[paul]

It should be something like this:

SELECT T1.*, T2.Active FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.locationid = T2.locationid WHERE T2.Active = 0 OR T2.locationid IS NULL

I'm not sure if this is excatly correct, but it should be close enough.



Paul
_________________
World Countries | Survival Skills

[ooKmonkey]

cheers Paul

Ill have a stab at it in the morning when im back in the office and let you know how I get on

Cheers again dude

[paul]

Let me know how it goes



Paul
_________________
World Countries | Survival Skills

[ooKmonkey]

Just to let you know, It worked a treat.

turns out I was sooooooooo close to working this one out myself before I asked for help. I had nearly the same code except at the end i had put "= Null" Instead of "IS Null" like you put.

Oh well At least Ive learned something new

Cheers again for the Help

[paul]