Return Single row not multiple row

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

Return Single row not multiple row

Author

Topic

eazygng
Starting Member

6 Posts

Posted - 2011-08-09 : 03:42:50

Hi guys,
Please can you look at my issue bellow

I have 4 table

1.biodata
----------------
username pk
password
firstname
lastnam

2.qualification
--------------
qualID
username
school
degree

3.application
--------------
appid
vacid
username

4.vacancy
-------------------
vacid
vacname

I created a view

SELECT dbo.biodata.UserName, dbo.biodata.FirstName, dbo.biodata.LastName, dbo.application.VacID,
dbo.vacancy.VacName,
dbo.qualification.Degree
FROM dbo.biodata INNER JOIN
dbo.application ON dbo.biodata.UserName = dbo.application.Username INNER JOIN
dbo.vacancy ON dbo.application.VacancyID = dbo.vacancy.VacancyID INNER JOIN
dbo.qualification ON dbo.biodata.UserName = dbo.qualification.UserName

You can have only 1 application but multiple qualification.

my problem is i want to display one record for each person even if you have multiple qualification

what i get now is

UserName FirstName LastName VacID vacancy Degree
-----------------------------------------------------------
joyr Joy Rice 1 Accountant Bsc
joyr Joy Rice 1 Accountant Bed
mikeL Mike Long 1 Accountant Bsc

but i want 2 see is

UserName FirstName LastName VacID vacancy Degree
-----------------------------------------------------------
joyr Joy Rice 1 Accountant Bsc
mikeL Mike Long 1 Accountant Bsc

What do i do??

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-09 : 05:06:03

Use Select Distinct

i.e. Select Distinct dbo.biodata.UserName, dbo.biodata.FirstName, dbo.biodata.LastName, dbo.application.VacID, --etc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-09 : 05:43:38

joyr has 2 degree. Which one do you want to show ?


joyr Joy Rice 1 Accountant Bsc
joyr Joy Rice 1 Accountant Bed

how do you decide which one over the others ?

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-08-09 : 05:46:57

use row_number function to get distinct record on each person...

eazygng
Starting Member

6 Posts

Posted - 2011-08-09 : 08:43:31

quote:
Originally posted by lappin

Use Select Distinct

i.e. Select Distinct dbo.biodata.UserName, dbo.biodata.FirstName, dbo.biodata.LastName, dbo.application.VacID, --etc

I have used select Distinct and it didnt work i got same result

eazygng
Starting Member

6 Posts

Posted - 2011-08-09 : 08:47:05

quote:
Originally posted by khtan

joyr has 2 degree. Which one do you want to show ?

joyr Joy Rice 1 Accountant Bsc
joyr Joy Rice 1 Accountant Bed

how do you decide which one over the others ?

KH
[spoiler]Time is always against us[/spoiler]

the first degree in the qualification table i.e by qualID

eazygng
Starting Member

6 Posts

Posted - 2011-08-09 : 08:49:15

quote:
Originally posted by bklr

use row_number function to get distinct record on each person...

How can i use ROW_FUNCTION, never used it before, any resource i can refree to?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-09 : 21:59:21

[code]
SELECT dbo.biodata.UserName,
dbo.biodata.FirstName,
dbo.biodata.LastName,
dbo.application.VacID,
dbo.vacancy.VacName,
qualification.Degree
FROM dbo.biodata
INNER JOIN dbo.application ON dbo.biodata.UserName = dbo.application.Username
INNER JOIN dbo.vacancy ON dbo.application.VacancyID = dbo.vacancy.VacancyID
INNER JOIN (
SELECT UserName,
Degree,
row_number() OVER (PARTITION by UserName ORDER BY qualID) as row_no
FROM dbo.qualification
) AS qualification ON dbo.biodata.UserName = qualification.UserName
AND qualification.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

eazygng
Starting Member

6 Posts

Posted - 2011-08-10 : 03:48:53

quote:

Originally posted by khtan


SELECT		dbo.biodata.UserName, 
		dbo.biodata.FirstName, 
		dbo.biodata.LastName, 
		dbo.application.VacID,
		dbo.vacancy.VacName,
		qualification.Degree
FROM 		dbo.biodata 
INNER JOIN	dbo.application 	ON 	dbo.biodata.UserName 		= dbo.application.Username 
INNER JOIN	dbo.vacancy 		ON 	dbo.application.VacancyID 	= dbo.vacancy.VacancyID 
INNER JOIN	(
			SELECT	UserName, 
				Degree,
				row_number() OVER (PARTITION by UserName ORDER BY qualID) as row_no
			FROM	dbo.qualification 
		) AS qualification	ON 	dbo.biodata.UserName 		= qualification.UserName
					AND	qualification.row_no		= 1

KH
[spoiler]Time is always against us[/spoiler]

Thank you so much the Script worked very well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources