How to join Candidate and Qualification tables

Question

Post reply

How to join Candidate and Qualification tables

pandeharsh

Ten Centuries

Points: 1106
More actions
July 11, 2009 at 6:43 am

#131893

Hi,
There is two table namely candidate and qualification.
candidate contain
ID int,
jobcode varchar
Name varchar
Qualification contain
candidateid int,
jobcode varchar
qualiDesc varchar
The data is such that,
say candidate contain
id=1,
jobcode=PYT
name=xyz
qualification( 1st row) contain,
candidateid =1
jobcode=PYT
qualiDesc=graduation
second row contain,
candidateid =1
jobcode=PYT
qualiDesc=post graduation
third row contain,
candidateid =1
jobcode=PYT
qualiDesc=others.
I want to join this two table such that,i should get this records in one row adding column alias etc. or however.
Be kind enough to make tables of your own.
I hope my table description and examples are clear.
Thanks in advance
[font="Verdana"]Regards
Kumar Harsh[/font]
:unsure:

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Kumar,

I am assuming you want to pivot the data so that it looks like this:

id name jobcode qualidesc 1 qualidesc 2 qualidesc 3

----------- ---------- ---------- -------------------- -------------------- --------------------

1 xyz PYT graduation post graduation others.

This is interesting in SQL Server because there is not a "clean" way to do this in SQL Server when there could be a variable number of pivoted columns, at least not that I am aware. Jeff Moden has written a couple of articles on Cross Tabs and Pivots that you might want to read.

Here's some code that works, but probably is NOT the most efficient way to do it.

DECLARE @sql1 NVARCHAR(4000), @sql2 NVARCHAR(4000), @sql3 NVARCHAR(4000), @sql_pivot NVARCHAR(4000), @final_sql NVARCHAR(4000), @counter INT

IF OBJECT_ID('candidate') IS NOT NULL

BEGIN

DROP TABLE candidate

END

IF OBJECT_ID('qualification') IS NOT NULL

BEGIN

DROP TABLE qualification

END

CREATE Table candidate(id INT, jobcode VARCHAR(10), NAME VARCHAR(10))

CREATE TABLE qualification (candidateid INT, jobcode VARCHAR(10), qualidesc VARCHAR(20))

INSERT INTO candidate (

id,

jobcode,

[NAME]

) VALUES (

1,

'PYT',

'xyz' )

INSERT INTO qualification (

candidateid,

jobcode,

qualidesc

)

SELECT

candidateid =1,

jobcode='PYT',

qualiDesc='graduation'

UNION ALL

Select

candidateid =1,

jobcode='PYT',

qualiDesc='post graduation'

UNION ALL

Select

candidateid =1,

jobcode='PYT',

qualiDesc='others.'

SET @sql1 = N'Select id, name, jobcode, ' + CHAR(10)

SET @sql3 = N'From (

SELECT

C.id,

C.[NAME],

Q.jobcode,

Q.qualidesc

FROM

candidate C JOIN

qualification Q ON

C.id = Q.candidateid AND

C.jobcode = Q.jobcode) as D

PIVOT

(

Min(qualidesc)

For qualidesc IN

(

'

SELECT

@counter = COALESCE(@counter, 0) + 1,

@sql2 = COALESCE(@sql2, N'')+ '[' + qualidesc + N'] as [qualidesc ' + Convert(nvarchar(10), @counter) + N'], ',

@sql_pivot = COALESCE(@sql_pivot, ' ') + '[' + qualidesc + N'],'

FROM

qualification

SET @final_sql = @sql1 + SUBSTRING(@sql2, 1, LEN(@sql2) -1) + @sql3 + SUBSTRING(@sql_pivot, 1, LEN(@sql_pivot) -1) + ')) as pvt'

EXEC (@final_sql)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pandeharsh Ten Centuries Points: 1106 More actions · Answer 2

Hi,

You have half understtod my problem.

my requirement is select all record from candidates table and and only those records from qualification table who are graduate and if any candidate is post graduates it shows that also using alias and left join.

but table fields are two many condition are also two many.

so where i am getting wrong ,I don't know.

But I am in correct way,I guess.

condition passingyear between,jobcode,age between,percentage greater than.

Say candidates tables have 100 records,qualification table will have many record that 100 because it will contain records of each student 10th,12th,graduates and post graduates records.

suppose I pass above condition who are graduates or may be post graduates.

how you write such query ?

Tables structures are :

Candidates details table structure

CREATE TABLE [pll_personaldetails] (

[ID] [int] NOT NULL ,

[jobcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[sname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fhname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dob] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mstatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gender] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[paddress] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dist] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[country] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[state] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[pincode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Resstdcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Resstdnum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[offstdcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[offstdnum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[offstdext] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mobcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mobnum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[emailid] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[salexp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fileresume] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[postApp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[maxage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[datereg] [datetime] NULL ,

[pexpoinfo] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[confirm] [bit] NOT NULL ,

[jobaddinfo] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

above id and jobcode together make primary code.

Qualification table:

CREATE TABLE [pll_qualification] (

[ID] [int] NOT NULL ,

[jobcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[exam] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[spec] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[passingyear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[institution] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[cgpa] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

Thanks in advance

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

It would be helpful if you posted using the methods suggested in the 1st article linked in my signature line. It helps us understand your problem better and provide a tested solution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pandeharsh Ten Centuries Points: 1106 More actions · Answer 4

Hi,

I think I hv posted my problem ok like providing,structure of tables.

only thing is that i didn't populated my tables.

I agree ,I did'nt post my problem in few words,coz I was unable to put them in few words.

Refering to the structure of my table,you can change the data type only.

To make you understand in nutshell,

Output should result in in one row for each candidate.

See,each each candidate id has between 3 to 5 rows in qualification tables.

I want those 3-5 rows appear in one rows.

All candidates are graduate(15) and they may post graduate or may not be,

if they they are not post graduates(17) then it should blank in alias column name else it should appear.

you have a permission to change column type maximum and nothing else.

Hope my problem is little clear.

populate your table with atleast 20 rows.

also don't laugh at my query,but you can get the idea of what my condition are and what i want ?

SELECT

pd.ID AS cid, pd.jobcode, ISNULL(pd.fname, '') + ' ' + ISNULL(pd.mname, '') + ' ' + ISNULL(pd.sname, '') AS FullName, DATEDIFF(year, pd.dob, GETDATE

()) AS Age, pd.emailid, pd.mstatus, pd.gender, ISNULL(pd.paddress, '') AS paddress, ISNULL(pd.dist, '') AS dist, ISNULL(pd.state, '') AS state, ISNULL

(pd.country, '') AS country, ISNULL(pd.pincode, '') AS pincode, pd.Resstdcode, pd.Resstdnum, pd.offstdcode, pd.offstdnum, pd.offstdext, ISNULL

qf1

(pd.mobcode, '') + ' ' + ISNULL(pd.mobnum, '') AS mobnum, qf.exam, qf.cgpa, qf.passingyear, qf.spec, qf1.exam AS exam1, qf1.spec AS spec1, .passingyear AS passingyear1, qf1.institution, qf1.cgpa AS cgpa1

FROM

dbo.pll_personaldetails AS pd INNER JOIN

dbo

.pll_qualification AS qf ON pd.ID = qf.ID AND pd.jobcode = qf.jobcode LEFT JOIN

dbo

.pll_qualification AS qf1 ON pd.jobcode = qf1.jobcode AND pd.ID = qf1.ID

WHERE

(pd.jobcode = 'GETS' AND DATEDIFF(year,pd.dob, GETDATE()) = '20') AND

(qf.type = '15' AND qf.passingyear >= '2006' AND qf.passingyear = '72' AND qf.spec LIKE '%Electrical' OR

qf

.spec LIKE '%Electrical%' OR

qf

.spec LIKE 'Electrical%') and

(

qf1.passingyear >= '2006' AND qf1.passingyear = '72' or qf1.type = '17' AND

qf1

.spec LIKE '%Electrical' OR

qf1

.spec LIKE '%Electrical%' OR

qf1

.spec LIKE 'Electrical%')

ORDER

BY qf.cgpa DESC

Thanks.

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

pandeharsh (8/2/2009)
Hi,
I think I hv posted my problem ok like providing,structure of tables.
only thing is that i didn't populated my tables.

Then, as you have stated, "You have half understtod my problem."

Good luck.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chand00 SSCertifiable Points: 6466 More actions · Answer 6

Hi Jeff,

I think pande wants the output in the following way

CREATE TABLE Qualification

(

QId int identity(1,1),

QName varchar(100)

)

go

CREATE TABLE Candidate

(

CId int identity(1,1),

CName varchar(200),

QId int)

go

insert into qualification(qname) values('10th Class')

insert into qualification(qname) values('12th Class')

insert into qualification(qname) values('Graduation')

insert into qualification(qname) values('PostGraduation')

go

insert into candidate(cname,qid) values('ChandraMohan',1)

insert into candidate(cname,qid) values('ChandraMohan',2)

insert into candidate(cname,qid) values('ChandraMohan',3)

go

insert into candidate(cname,qid) values('Pande',1)

insert into candidate(cname,qid) values('Pande',2)

insert into candidate(cname,qid) values('Pande',3)

insert into candidate(cname,qid) values('Pande',4)

go

insert into candidate(cname,qid) values('Alert',1)

insert into candidate(cname,qid) values('Alert',2)

go

when he queries for candidates who are either graduates or postgraduates(i.e. who has qid 3 or more in their candidate table).

fyi- a graduate may or maynot be a postgraduate. but a postgraduate is definitely a graduate.

So output should be

CIDCName10thClass12thClassGraduationPostGraduation

1ChandraMohanYesYesYesNo

2PandeYesYesYesYes

As there is no row in candidate table for ChandraMohan with qid 4

Pande: Can you confirm whether this is your requirement.

[font="Verdana"]Thanks
Chandra Mohan[/font]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

I am not sure that I fully understand the question without seeing the data and the expected results....however I have done something similar in the past which may provide you with some ideas.

This code has only be run against relatively small tables and is probably not the most efficient

--===== Assumptions: Unique records in both tables

--===== Notes: An "ID" can have more than one "jobcode"

--===== Notes: Each "ID/jobcode" can have a maximum of 5 "grades" that are either A,B,C,D or E

--===== Notes: "grades" are independent of each other..ie you can have B and E without A,C and D

USE tempdb

GO

--===== If the test tables already exists, drop them

IF OBJECT_ID('TempDB..#personaldetails','U') IS NOT NULL

DROP TABLE #personaldetails

IF OBJECT_ID('TempDB..#qualification','U') IS NOT NULL

DROP TABLE #qualification

--===== Create the test tables

CREATE TABLE #personaldetails(

[ID] [int] NOT NULL,

[jobcode] [varchar](50) NOT NULL

)

CREATE TABLE #qualification

(

[ID] [int] NOT NULL,

[jobcode] [varchar](50) NOT NULL,

[grade] [varchar](50) NOT NULL

)

--===== populate the test tables with some data

INSERT INTO #personaldetails

(ID, jobcode)

SELECT '1','XYZ' UNION ALL

SELECT '1','ZZZ' UNION ALL

SELECT '2','XYZ' UNION ALL

SELECT '3','ABC' UNION ALL

SELECT '4','ABC' UNION ALL

SELECT '5','ABC'

INSERT INTO #qualification

(ID, jobcode,grade)

SELECT '1','XYZ','A' UNION ALL

SELECT '1','XYZ','B' UNION ALL

SELECT '1','ZZZ','B' UNION ALL

SELECT '1','ZZZ','E' UNION ALL

SELECT '2','XYZ','A' UNION ALL

SELECT '2','XYZ','B' UNION ALL

SELECT '2','XYZ','C' UNION ALL

SELECT '2','XYZ','D' UNION ALL

SELECT '3','ABC','A' UNION ALL

SELECT '3','ABC','B' UNION ALL

SELECT '4','ABC','A' UNION ALL

SELECT '4','ABC','B' UNION ALL

SELECT '4','ABC','C' UNION ALL

SELECT '4','ABC','D' UNION ALL

SELECT '4','ABC','E' UNION ALL

SELECT '5','ABC','A' UNION ALL

SELECT '5','ABC','B' UNION ALL

SELECT '5','ABC','C' UNION ALL

SELECT '5','ABC','E'

GO

--===== Create A CTE "GradesExtract"

--===== Assume a max of five grades (A,B,C,D,E)and that records are unique in #qualification

WITH GradesExtract(ID, jobcode, gradeA, gradeB, gradeC, gradeD, gradeE) AS

(

SELECT #personaldetails.ID, #personaldetails.jobcode, #qualification.grade AS gradeA, '' AS gradeB, '' AS gradeC, '' AS gradeD, '' AS gradeE