ASP Hosting sale!
Double disk space and transfer for FREE!
Limited time offer! Act Now!

aspdev | articles | tutorials | forums

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Joining Tables in a view

 
Post new topic   Reply to topic    ASPdev.org Forum Index -> SQL Programming (MS SQL Server, MS Access, MySQL)
View previous topic :: View next topic  
Author Message
ooKmonkey



Joined: 22 Feb 2006
Posts: 4

PostPosted: Wed Feb 22, 2006 5:50 am    Post subject: Joining Tables in a view Reply with quote

Hi, Hope someone can help me here Confused

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 Cool

Cheers
Back to top
View user's profile Send private message
paul



Joined: 11 Oct 2004
Posts: 128

PostPosted: Wed Feb 22, 2006 9:27 am    Post subject: Reply with quote

Can you post the exact table structures?



Paul
_________________
World Countries | Survival Skills
Back to top
View user's profile Send private message
ooKmonkey



Joined: 22 Feb 2006
Posts: 4

PostPosted: Wed Feb 22, 2006 9:38 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
paul



Joined: 11 Oct 2004
Posts: 128

PostPosted: Wed Feb 22, 2006 9:51 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ooKmonkey



Joined: 22 Feb 2006
Posts: 4

PostPosted: Wed Feb 22, 2006 9:52 am    Post subject: Reply with quote

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 Cool
Back to top
View user's profile Send private message
paul



Joined: 11 Oct 2004
Posts: 128

PostPosted: Wed Feb 22, 2006 10:34 am    Post subject: Reply with quote

Let me know how it goes Smile



Paul
_________________
World Countries | Survival Skills
Back to top
View user's profile Send private message
ooKmonkey



Joined: 22 Feb 2006
Posts: 4

PostPosted: Thu Feb 23, 2006 4:59 am    Post subject: Reply with quote

Just to let you know, It worked a treat. Very Happy

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 Embarassed

Cheers again for the Help Exclamation Cool Very Happy Cool Wink
Back to top
View user's profile Send private message
paul



Joined: 11 Oct 2004
Posts: 128

PostPosted: Thu Feb 23, 2006 9:46 am    Post subject: Reply with quote

ooKmonkey wrote:
Just to let you know, It worked a treat. Very Happy

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 Embarassed

Cheers again for the Help Exclamation Cool Very Happy Cool Wink


I'm glad it worked Wink.




Paul
_________________
World Countries | Survival Skills
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    ASPdev.org Forum Index -> SQL Programming (MS SQL Server, MS Access, MySQL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group

SQL Tutorial