simple join query - 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

simple join query

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-26 : 06:07:45

CREATE TABLE PROFILES(
ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)

insert into PROFILES values ('rryan','Jamie Fox','jfox@live.com')
insert into PROFILES values ('mclark','Michael Clark','mclark@live.com')
insert into PROFILES values ('djones','Dean Jones','djones@live.com')
insert into PROFILES values ('jfox','Jamie Fox','jfox@live.com')
insert into PROFILES values ('drivers','Doc Rivers','drivers@live.com')

CREATE TABLE ROLE(
ID varchar(20) UNIQUE NOT NULL,
Role char(10) NOT NULL,
Applications char (10) NOT NULL
)

insert into ROLE values ('rryan','M','Consultant')
insert into ROLE values ('mclark','AM','Organizer')
insert into ROLE values ('djones','SM','Admin')
insert into ROLE values ('jfox','M','Consultant')
insert into ROLE values ('drivers','AM','Organizer')

I am trying to join two tables and my expected output is

EXPECTED OUTPUT
ID Name Address Role Application

This is my query and I am not able to join

SELECT ID, Name, Address, Role, Applications
from PROFILES
RIGHT JOIN ROLE
ON PROFILES.ID = ROLE.ID

Can anyone correct me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 06:34:48

[code]
SELECT p.ID, Name, Address, Role, Applications
FROM PROFILES p
INNER JOIN ROLE r
ON p.ID = r.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Prav4u
Starting Member

15 Posts

Posted - 2013-11-26 : 06:36:46

Hi,
try this

SELECT PROFILES.ID, Name, Address, Role, Applications
from PROFILES
LEFT JOIN ROLE
ON PROFILES.ID = ROLE.ID

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-26 : 06:45:15

Should have mentioned table name, got ambiguity problem. Thanks and got it solved.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 06:45:22

Doesnt require LEFT JOIN unless you've profiles without any assigned roles.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources