Easy - Join 2 Tables Of Data - 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

Easy - Join 2 Tables Of Data

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-04 : 14:32:11

I have 1 table that is like this.

Table Users
ID
SPECIALID
SPECIALID2
NAME

Table Users Example Data
1,5,6,Joe Jones

Table SPECIALIDINFO
USERID
NAMEINFO

Table SPECIALINFO Data
1,COOL GUY
5,SWEET GUY

Table SPECIALIDINFO2
USERID
NAMEINFO

Table SPECIALINFO2 Data
1,COOL GUY2
6,SWEET GUY2

When i do my query from the Users Table, I dont want it to come back and say 1,5,6, Joe Jones

I wanted to some how refence the ID used for SPECIALID, and get the value from teh SPECIALINFO table and get he name NAMEINFO FOR IT.

so when i do a select * from users I would like to show like below

1, SWEET GUY,SWEET GUY2, Joe Jones

Thanks again guys, Im new to SQL! :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-04 : 14:47:30

[code]SELECT
u.Id,
s.NameInfo AS Info1,
s2.NameInfo AS Info2,
u.NAME
FROM
[Users] u
INNER JOIN SPECIALIDINFO s ON u.USERID= s.SPECIALID
INNER JOIN SPECIALIDINFO2 s2 ON u.USERID= s2.SPECIALID2[/code]If you want to get the user id's for users who may not have data in both specialinfo tables use LEFT JOINs instead of INNER JOINs.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-04 : 15:03:00

Ok im confused now, i dont know why when im trying to get the info from the SPECIALID table, i use u.userid

Would i use the ID like u.SPECIALID = s.SPECIALID ?

*** EDIT ***

TO make it simpler

If i have a table like this

NAMETABLE
ID
NAM

Sample Data
1, COOL GUY
2, SWEET GUY

USERTABLE
ID
CODE (This is ID From NAMETABLE)
NAME

Sample Data
1,1, Joe Jones
2,2, Billy Jones

And when i do a query i wanted it to say

1, COOL GUY, Joe Jones

How would i get it to do that, i dont think im following how to do a simple join to get the Name of the ID, not the ID itself.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-04 : 15:21:09

Ok here is the exact data, and name of columns.

Applications Table

Delegation Table

Users Table

My current query:
select
u.userid,u.username,d.applicationid,d.accountname,d.starttime,d.endtime,d.notimelimit
from users u,
delegation d
where u.userid = userid

This gives me this result.
I wanted to show all records for each person in the delegation table

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-04 : 17:30:21

I think the following would do that. Changes to your code are in red.

SELECT u.userid,
       u.username,
       a.ApplicationName,
       d.accountname,
       d.starttime,
       d.endtime,
       d.notimelimit
FROM   users u,
       delegation d,
       applications a
WHERE  u.userid = userid
       AND a.ApplicationId = d.ApplicationId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources