Help with a query

[technisup]

Hi, i have to tables like this:

Table: HV
Id
Name

Table: VS
Idhorse
Idmare
value

so i need to bring from hv the names according to the idhorse and idmare in HV, how can i do this?

Thanks in advance for the help.

 

[drtree]

Hi,

You could just do this:

SELECT Horse.name, Mare.name
FROM VS LEFT OUTER JOIN HV Horse
ON VS.Idhorse = Horse.Id
LEFT OUTER JOIN HV Mare
ON VS.Idmare = Mare.Idmare


I think that should do it. There are a few things to check out here - look into left outer joins (which pull all the data from VS and only the matching records in HV), and aliasing - "Horse" and "Mare" are both aliases for the HV table.

HTH,

Doc Tree

 

[Kozusnik]

I don't think I would have designed the database like this, but try this...

select t1.idhorse,t2.name,t1.idmare,t3.name,t1.value
from vs t1
left join hv t2 on t2.id=t1.idhorse
left join hv t3 on t3.id=t1.idmare

Mark

 

[r937]

left outer joins are wrong here

how can we have a value in Idhorse or Idmare that isn't in HV?

only if we were sloppy twice -- by failing to declare proper foreign keys, and then by not checking ourselves (which is what the database does for us with FKs)


r937.com | rudy.ca