Help With SQL query

[chuy08]

I have two tables, leads and mortgages that have a one to many relationship. The relationship exists in these two tables with the id field that is in both tables. In the leads table the mortgage id will be unique, in the mortgage table this id can be duplicated. Currently I am using the following query:

select leads.fname, leads.lname, mortgage.id from leads, mortgages where leads.id = mortgage.id

this returns something that looks like this

fname lname name
jon doe wamu
jon doe county
jane doe wells
jane doe indy

What I want the return to look like would be the following:

fname lname name1 name2
jon doe wamu country
jane doe wells indy

Any assistance requested please do let me know?

 

[AlexCuse]

if you only have two records for each, you could try this:

select a.fname, a.lname, min(b.name) as name1, max(b.name) as name2
from LEADS a inner join MORTGAGES b on a.id = b.id

This will give the 'names' in alphabetical order. Is there anything within the morgages table that would indicate whether a value should go in name1 or name2?

It sounds like it's first and second mortgage lender, if there is a field to indicate whether it's a first or second that would help.

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[PruSQLer]

Do you really have tables in this DB where the Key relations can only return multiple rows? Commonly there will be a secondary key to allow the return of one row.