select
mc.id,
c.id,
d.rubrica_completa,
c.fecha,
p.dni,
p.apellido || ' ' || p.nombre
from consultas c
inner join consultas_mc mc on (c.id = mc.id_consultas)
inner join diagnosticos d on (mc.id_diagnosticos = d.id)
inner join pacientes p on (c.id_pacientes = p.id)
where
c.id_pacientes = :id and
not exists(select * from consultas_mc mc2
where mc.id_consultas = mc2.id_consultas
and mc.id > mc2.id)
order by mc.id desc

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Sergio H. Gonzalez
Sent: 27. april 2010 14:33
To: firebird-support@yahoogroups.com
Subject: [firebird-support] help with a select

Hello ! I'm trying to do this: I have a table in which I store visits to a
doctor. In each visit, the patient can be diagnosed with *ONE OR MORE* illness
(they are stored in the table "consultas_mc"). With this select I get all the
visits (table "consultas") but if one visit has more than one illness I get the
visit duplicated:

select
mc.id,
c.id,
d.rubrica_completa,
c.fecha,
p.dni,
p.apellido || ' ' || p.nombre
from consultas c
inner join consultas_mc mc on (c.id = mc.id_consultas)
inner join diagnosticos d on (mc.id_diagnosticos = d.id)
inner join pacientes p on (c.id_pacientes = p.id)
where
c.id_pacientes = :id

order by mc.id desc

*I want to get just the first illnes of each visit, in order to get each visit
once*. So I did this:

select
min(mc.id),
min(c.id),
min(d.rubrica_completa),
c.fecha,
p.dni,
p.apellido || ' ' || p.nombre
from consultas c
inner join consultas_mc mc on (c.id = mc.id_consultas)
inner join diagnosticos d on (mc.id_diagnosticos = d.id)
inner join pacientes p on (c.id_pacientes = p.id)
where
c.id_pacientes = :id

group by
c.fecha,
p.dni,
p.apellido || ' ' || p.nombre

order by 1

But comparing both results I've find that the second does not return all the
rows. So I gess I did something wrong..

Any help, please?

Thanks!!

sergio




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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links