problem joining two tables - 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

SQL Server Development (2000)

problem joining two tables

Author

Topic

larmister
Starting Member

2 Posts

Posted - 2006-07-07 : 15:56:59

Hi,

I am a intermediate with sql server and can write moderately difficult sql queries. I am writing a message board application and I am trying to join two tables that are related by a userid.

The first table (tbl_messages) contains (this is abbreviated but shows the key fields):
topicId
userId
showEmail
showSignature

The second table (tbl_users) contains (again abbreviated):
userId
emailAddr
signature

The showEmail and showSignature in tbl_messages indicates whether I need to get the emailAddr and signature from tbl_users. So, I am looking for a result set that looks like this:

userId   showEmail  showSignature   emailAddr      signature
85           1            1         jdoe@abc.com   My signature
86           0            0         NULL           NULL 
87           0            1         NULL           Love & Kisses

So the query should return:
1. All messages matching the topicid in the tbl_messages
2. the emailaddr and signature for userId 85 from tbl_users
3. not retrieve the email and signature for userId 86
4. only return the signature for userId 87
5. and place the results in one result set.

I have tried all kinds of methods (joins, unions, temp tables) for achieving this result but have been unsuccessful.

The reason I am looking for a single result set is because I am using a datagrid (.Net) to display the messages.

I'd appreciate an example of how I can do this.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-07 : 16:59:23

select m.topicID, u.UserID, emailAddress = case when m.showEmail = 1 then u.emailAddr else null end, Signature = case when m.showSignature = 1 then u.signature else null end
from tbl_messages m
join tbl_users u
on m.userId = u.userId

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

larmister
Starting Member

2 Posts

Posted - 2006-07-07 : 17:20:29

Thanks nr! That worked perfectly.

quote:
Originally posted by nr

select m.topicID, u.UserID, emailAddress = case when m.showEmail = 1 then u.emailAddr else null end, Signature = case when m.showSignature = 1 then u.signature else null end
from tbl_messages m
join tbl_users u
on m.userId = u.userId

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources