Hi,
probabily the best way to understand what I need is to check the SQL that I posted
with the explanation of the field that I need to add in the query
Main Topics
Browse All TopicsHi experts,
I have 2 tables
one is made of
id
values
docid
reference
the other one has several fields
...
serial
...
reference in table 1 is linked to serial in table 2
now I need to retrieve the field value in table 1 where docid=1 and docid=2
I need to have something like:
[field1].....[fieldn] from table 2 + [value1], [value2] where value 1 and value2 are the fields in table 1 with docid=1 and docid=2 and reference=serial
is it possible to do it in one sql statement?
Please let me know if it is not clear enough
PS this is the real sql I am executing
SELECT s_pazienti.data, pazienti.nominativo, pazienti.sesso,pazienti. telefono, pazienti.usl, pazienti.cartella, datidoc_car.valore, pazienti.reparto
FROM datidoc_car LEFT JOIN (a_pazienti INNER JOIN (pazienti INNER JOIN s_pazienti ON
pazienti.seriale = s_pazienti.codice) ON
a_pazienti.seriale = pazienti.codice) ON
datidoc_car.riferimento = pazienti.seriale
WHERE ( s_pazienti.servizio=18 )
AND ( datidoc_car.codice=23 )
AND ( pazienti.tipo in ('D','H') )
AND ( pazienti.stato='A' )
group by 8,7,5,6,4,3,2,1
order by reparto, valore DESC, s_pazienti.data
in the same query I need to retrieve datidoc_car.valore for datidoc_car.codice=8
thanks
Samassrl
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
found my own solutions
I need to do a subquery
can you please close the question
thanks
SELECT s_pazienti.data, nominativo, sesso, telefono, usl, cartella, valore as urgente, reparto, (select valore from datidoc_car
WHERE datidoc_car.codice=8 AND riferimento=pazienti.seria
FROM datidoc_car LEFT JOIN (a_pazienti INNER JOIN (pazienti INNER JOIN s_pazienti ON
pazienti.seriale = s_pazienti.codice) ON
a_pazienti.seriale = pazienti.codice) ON
datidoc_car.riferimento = pazienti.seriale
WHERE ( s_pazienti.servizio=18 )
AND ( datidoc_car.codice =23 )
AND ( pazienti.tipo in ('D','H') )
AND ( pazienti.stato='P' )
order by reparto, valore DESC, s_pazienti.data
Business Accounts
Answer for Membership
by: LegendaryTechPosted on 2007-08-01 at 14:20:49ID: 19612983
Hi samassrl, you have a common situation, and the best practive is to use a JOIN. You can use a INNER JOIN (most probable), a LEFT JOIN, and a RIGHT JOIN. INNER JOIN (probably what you need) returns all rows from both tables where there is a match.
eld
Here is a generic representation:
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfi
The "keyfield" will be what links the tables so make sure both tables have a common field.