Join 2 tables with 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

General SQL Server Forums

New to SQL Server Programming

Join 2 tables with SELECT

Author

Topic

SuperSQLNewb
Starting Member

9 Posts

Posted - 2009-08-28 : 17:25:27

I must be missing something Simple...

Sammple Data from both tables
Table 1 = SMEs

UserID UserName
JXJohns John Jones
BDJones Brad Johnson
JPApple Johny Appleseed

Table 2 = SMEInvites

UserID AppID Invited
JXJohns 12 1
BDJones 12 1
BDJones 15 1

What I want to see is *All* UserName in SMEs table. Add a 1 in the invited col if they exist in SMEInvites for AppID = 12.

desired result:

Invited UserName
1 John Jones
1 Brad Johnson
Johny Appleseed

Thanks!

alanw64
Starting Member

4 Posts

Posted - 2009-08-28 : 18:22:16

Maybe this will work?

SELECT a.username FROM SMEs a
JOIN (SELECT b.userid FROM SMEInvites b WHERE appid = 12) as b
ON a.userid = b.userid

I might have misunderstood what you want, but I hope that helps. I've got to run for now, but let me know if you need something different.

Thanks,
Alan

Fall down seven, stand up eight.
~Japanese Proverb

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-29 : 00:20:43

select invited,username from SMEs s left join SMEInvites i on i.UserID = s.UserID

SuperSQLNewb
Starting Member

9 Posts

Posted - 2009-08-31 : 17:06:18

Neither return the desired results,
alanw64 returns only names in SMEInvites table
bklr is close and is what I was going on but it does not designate which ones are tied to appid = 12

Maybe this will help if I explain what this will be used for:
SMEs is a table with names. SMeInvites tracks which names will be invited to a meeting to discuss an application. In my user interface I have a list of all of the SME names with check boxes. If a user goes into this dialog for a given application it should show all of the names and if some have been previously pick for this application they should also have a check mark.

Sooo the query I need shows everyone from SMEs and designates which ones are assigned to AppID = 12

Any other suggestions? and thanks for the help so far!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-31 : 17:15:51

is this what you need?

declare @sme table (UserID varchar(10), UserName varchar(40))
insert @sme
select 'JXJohns', 'John Jones' union all
select 'BDJones', 'Brad Johnson' union all
select 'JPApple', 'Johny Appleseed'

declare @SMEInvites table (UserID varchar(10) ,AppID int, Invited int)
insert @SMEInvites
select 'JXJohns', 12, 1 union all
select 'BDJones', 12, 1 union all
select 'BDJones', 15, 1

Query

select COALESCE(b.Invited,0),a.UserName
from @sme a LEFT JOIN @SMEInvites b on 
a.UserID = b.UserID and b.AppID = 12

Result

Invited     UserName
----------- ----------------------------------------
1           John Jones
1           Brad Johnson
0           Johny Appleseed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-31 : 17:22:28

I thought the column Invited in table SMEInvites will not always give the wanted value '1' because op wrote:
Add a 1 in the invited col if they exist in SMEInvites for AppID = 12
He wrote not: take Invited from table.


select
case
 when i.userid isnull then ''
 else '1'
end as invited,
s.UserName
from SMEs s
left join SMEInvites i
  on s.UserID=i.UserID and i.AppID=12

No, you're never too old to Yak'n'Roll if you're too young to die.

SuperSQLNewb
Starting Member

9 Posts

Posted - 2009-08-31 : 17:35:35

Nice! I just had to change isnull to IS NULL for WebFreds, they both work though .. COALESCE Thats very handy.

Thanks again to both of you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources