Join Two Tables To Get All Rows Without Duplicates

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 2005 Forums

Transact-SQL (2005)

Join Two Tables To Get All Rows Without Duplicates

Author

Topic

atulkukreja
Starting Member

13 Posts

Posted - 2012-01-09 : 20:17:33

Using SQL Server 2005 Express.

Table Definitions:

CREATE TABLE [dbo].[subjects] (
[subject] varchar(50) NOT NULL,
) ON [PRIMARY]
go

INSERT INTO [subjects] ([subject])VALUES('Math')
INSERT INTO [subjects] ([subject])VALUES('English')
INSERT INTO [subjects] ([subject])VALUES('Biology')
INSERT INTO [subjects] ([subject])VALUES('Physics')

CREATE TABLE [dbo].[studentSubjects] (
[student] varchar(50) NOT NULL,
[subject] varchar (50) NOT NULL
) ON [PRIMARY]

go

INSERT INTO [studentSubjects] ([student],[subject])VALUES('Jim','Math')
INSERT INTO [studentSubjects] ([student],[subject])VALUES('Kim','Math')
INSERT INTO [studentSubjects] ([student],[subject])VALUES('Kim','Biology')

I'd like to list all students and all subjects, with a 'Y' if the subject is selected or 'N' if the subject is not selected:

Student Subject Selected?
--------------------------
Jim Math Y
Jim English N
Jim Biology N
Jim Physics N
Kim Math Y
Kim English N
Kim Biology Y
Kim Physics N

I have tried a cross join (select S.studentName, T.subject, case when S.subject = T.subject then 'Y' else 'N' end
from studentSubjects S cross join subjects T order by 1,2) but it yields incorrect results and I cannot find the SQL to do this, any help is greatly appreciated.

scptech

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 04:19:55

[code]
SELECT m.Student,m.subject,
CASE WHEN n.subject IS NOT NULL THEN 'Y' ELSE 'N' END AS [Selected]
FROM
(
SELECT s.Student,sb.subject
FROM (SELECT DISTINCT Student FROM studentSubjects) s
CROSS JOIN subjects sb
)m
LEFT JOIN studentSubjects n
ON n.Student = m.Student
AND n.subject = m.subject
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

atulkukreja
Starting Member

13 Posts

Posted - 2012-01-10 : 08:03:06

Thanks for the quick response visakh16, that works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources