August 2, 2009 at 11:15 pm
Hi,
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
Refering to the structure of my table,you can change the data type only.
Output should result in in one row for each candidate.
See, each candidate id has between 3 to 5 rows in qualification tables.
I want those 3-5 rows appear in one rows of my join query.
All candidates are graduate(15) and they may be 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 if required.
Hope my problem is clear.
my join query, is (not giving correct result,
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()) <= '25' AND DATEDIFF(year, pd.dob, GETDATE()) >= '20') AND
(qf.type = '15' AND qf.passingyear >= '2006' AND qf.passingyear <= '2007' AND qf.cgpa >= '72' AND qf.spec LIKE '%Electrical' OR
qf
.spec LIKE '%Electrical%' OR
qf
.spec LIKE 'Electrical%') and
(
qf1.passingyear >= '2006' AND qf1.passingyear <= '2007' AND qf1.cgpa >= '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
you can the idea of what I want and condition that is required.
Thanking you.
[font="Verdana"]Regards
Kumar Harsh[/font]
August 3, 2009 at 8:01 pm
Could you post some sample data in a readily consummable format for loading your tables and the expected results of the query based on the sample data?
For assistance in this, please read the first article I reference in my signature block. Follow those guidelines and we can easily help you with some tested code.
August 4, 2009 at 2:51 am
Table are so big,I don't know how to present data.
Minimum Data of Personal Details can be
id Jobcode Name Address
1000 GETS Chandra mohan Including All
1001 GETS Jeff Including All
Qualification Details
id JobCode exams specs passingyear institution cgpa type
1000 GETS 10th all subjects 1980 St. Xyz 80 10
1000 GETS 12th PCM 1982 St. Xyz 84 12
1000 GETS B.com Accounts 1985 St. Xyz 90 15
1001 GETS 10th all subjects 1980 St. PQR 90 10
1001 GETS 12th PCM 1982 St. PQR 72 12
1001 GETS B.com Accounts 1985 St. PQR 65 15
1001 GETS M.com Accounts 1987 St. PQR 65 17
Qualification table contain data of two candidates
type 10=10th class
type 12=12 class
type 15=graduate
type 17=post graduates
cgpa=percentile
spec=specializations
Also id and jobcode together make primary key in personal detail tables
and foriegn key in qualification details.
I guess sir,I hv already provided the structure of tables
Output should be in one row containg all name address and qualification details for each students even if the post graduate columns is blanks.
Also Sir,you can see my query that I was trying in forum,there you can get the idea of filteration.
I was using alias to get qualification table in one row of that candiates.
Hope I am clear.
Thanking you.
[font="Verdana"]Regards
Kumar Harsh[/font]
August 4, 2009 at 6:34 am
Please take the sample data you provided, and create the expected output that should be generated by the query. Please don't just describe the output, show use what it should look like. This will give us something to check against instead of just guessing if it is correct.
August 4, 2009 at 7:29 am
You really ought to read the advice given here
http://www.sqlservercentral.com/articles/Best+Practices/61537/
if you follow this advice then i am sure that you will get more response and answers that provide tested code.
Because I am still unsure of what you require as the end result I have "guessed" and using the snippets of data that you have actually managed to provide I give you the code below.
Could you please run this and let us know if this is any way close to what you are expecting?
Kind regards gah
USE tempdb
GO
--===== If the test tables already exists, drop them
IF OBJECT_ID('TempDB..#pll_personaldetails','U') IS NOT NULL
DROP TABLE #pll_personaldetails
IF OBJECT_ID('TempDB..#pll_qualification','U') IS NOT NULL
DROP TABLE #pll_qualification
--===== Create the test tables
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
) ON [PRIMARY]
GO
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
--===== populate the test tables with some data
INSERT INTO #pll_personaldetails
(ID,jobcode,fname)
SELECT '1000','GETS','Chandra' UNION ALL
SELECT '1001','GETS','Jeff'
INSERT INTO #pll_qualification
(ID, jobcode,exam,spec,passingyear,institution,cgpa,type)
SELECT '1000','GETS','10th','all subjects','1980','StXyZ','80','10' UNION ALL
SELECT '1000','GETS','12th','PCM','1982','StXyz','84','12' UNION ALL
SELECT '1000','GETS','Bcom','Accounts','1985','StXyz','90','15' UNION ALL
SELECT '1001','GETS','12th','PCM','1982','StPQR','90','10' UNION ALL
SELECT '1001','GETS','10th','all subjects','1980','StPQR','90','10' UNION ALL
SELECT '1001','GETS','Bcom','Accounts','1985','StPQR','65','15' UNION ALL
SELECT '1001','GETS','Mcom','Accounts','1987','StPQR','65','17'
--===== the follwoing taken from http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ by Anith Sen
--===== "The blackbox XML methods"
;WITH qualificationextract(ID, jobcode, qualifications) AS
(
SELECT p1.ID, p1.jobcode,
(
SELECT [type] + ',' + exam + ',' + spec + ' || '
FROM #pll_qualification p2
WHERE p2.ID = p1.ID AND p2.jobcode = p1.jobcode
ORDER BY ID
FOR XML PATH('')
) AS qualifications
FROM #pll_qualification p1
GROUP BY ID,jobcode
)
SELECT #pll_personaldetails.ID, #pll_personaldetails.fname, #pll_personaldetails.jobcode, qualificationextract.qualifications
FROM #pll_personaldetails INNER JOIN qualificationextract ON #pll_personaldetails.ID = qualificationextract.ID
AND #pll_personaldetails.jobcode = qualificationextract.jobcode
GO
--=====END
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply