asked on

Select 1 row from MANY

I have a 1 to M table join but I only need a single value from the many table. Is this possible?

For instance:

People
PeopleID, PeopleName

Jobs
JobID, JobName

PeopleJob
PeopleID,JobID

SELECT PeopleName,JobName AS OneJobName
FROM People p
JOIN PeopleJob pj ON p.PeopleID = pj.PeopleID
JOIN Jobs j ON j.JobID = pj.JobID --how do I just get one value?

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

coldchillin

ASKER

I just need one of the job names!

I was able to get the first version working, but I'm more interested in the second solution you offered. I'm familiar with aggregates using group by, but I have no idea what the over(partition by) does!!

Is there any benefits to one over the other?
If it matters, my actual query is more involved. This is the inner query which uses multiple joins, which is then pivoted.

dqmq

Performance wise, I don't know. But, logically, the partition by pattern is not aggregating; it is numbering within a group (partition) and then choosing. For example, you could identify the second highest salary which you cannot do with max(). The real nice thing about the partition by construct is that identifies the row of interest. Unlike max(), for example, which requires a clumsy nested, correlated subuery in the where clause to identify the row containing the max value. The equivalent partion by condition is trivial--> where seq = 1.

Get familiar with partition by, you will find is solves many thorny query problems and you will soon be living in despair when you need to work on a database that doesn't support it. :>)

coldchillin

ASKER

I used the first option, worked like a charm. Will look into the second option.