• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 961
  • Last Modified:

SQL ho to retrieve multiple records form table1 linked to table 2

Hi 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
0
samassrl
Asked:
samassrl
  • 2
1 Solution
 
LegendaryTechCommented:
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.

Here is a generic representation:

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

The "keyfield" will be what links the tables so make sure both tables have a common field.
0
 
samassrlAuthor Commented:
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
0
 
samassrlAuthor Commented:
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.seriale) as note
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
0
 
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now