A Left Join Sample


USE tempdb

GO

IF

OBJECT_ID(‘dbo.education’, ‘U’) IS NOT NULL

DROP TABLE dbo.education

IF

OBJECT_ID(‘dbo.qualification’, ‘U’) IS NOT NULL

DROP TABLE dbo.qualification

GO

CREATE

TABLE dbo.education

(

id

int identity (1,1),

diplomaCode

int, degreeCode int, masterCode int, phdCode int

)

GO

CREATE

TABLE dbo.qualification

(

code

int identity,

name varchar(50)

)

go

INSERT

into education

SELECT

1,2,3,4

UNION ALL

SELECT 2,2,3,2

GO

INSERT

into qualification

SELECT

‘aaa1’

UNION ALL

SELECT ‘aaa2’

UNION ALL SELECT ‘aaa3’

UNION ALL

SELECT ‘aaa4’

UNION ALL

SELECT ‘aaa5’

GO

 

SELECT

a.name as diplomaName

,b.name as degreename

,

c.name as mastername

,

d.name as phdname

FROM

education LEFT JOIN qualification a ON a.code = education.diplomaCode

LEFT

JOIN qualification b ON b.code = education.degreeCode

LEFT

JOIN qualification c ON c.code = education.masterCode

LEFT

JOIN qualification d ON d.code = education.phdCode

WHERE

education.id = @qsId

http://forums.asp.net/p/1345746/2735787.aspx#2735787

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s