Return a single row instead of multiple

Question

Return a single row instead of multiple

uche_ezeribe

Newbie

Points: 7
More actions
August 9, 2011 at 1:48 am

#74363

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??

Viewing 4 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

uche_ezeribe (8/9/2011)
...
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??

First thing you have to do is figure out the rules for deciding which degree to display. Joy Rice has BSc and BEd, how did you arrive at BSc in the lower result set? Why not display them all like this:

UserName FirstName LastName VacID vacancy Degree

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

joyr Joy Rice 1 Accountant BSc, BEd

mikeL Mike Long 1 Accountant Bsc

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

Jayanth_Kurup

SSC-Insane

Points: 22967

August 9, 2011 at 3:08 am

#1365689

Look up ROW_NUMBER function

Jayanth Kurup[/url]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

{Edit} Sorry... wrong thread...

--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)