如何从多个表中获取数据？

what i want is all the record from tbmedAssign where zoneid or zone name given as parameter. how to wrote query for this? please help.

我想要的是来自tbmedAssign的所有记录,其中zoneid或zone名称作为参数给出。怎么写这个查询?请帮忙。

table name tablzone

表名tablzone

zone_id(PK)   ZoneName
-----------   --------
1             east
2             west
3             north
4             south

tbluser

usrId(PK) userzoneId(FK to tblzone)   username
--------  -------------------------   ------------
1         1                           manish
2         3                           rahul
3         2                           ankit
4         4                           amir
5         2                           rashmi
6         1                           akash
tbldoctor

docId(PK)    usrId(Fk to tbluser)      docname
--------     --------------------      ------------
1             2                        hemant
2             2                        chintu
3             3                        rahim
4             1                        salman
5             3                        kishor
6             3                        saurabh
7             2                        banti

tblmedAssign 

transId(Pk)  doctorId(FK to tbldoctor)    medId(FK)    dateInsert
----------   -------------------------    ------   -----------
1            2                            2        20/12/2012
2            3                            3        21/12/2012
3            2                            3        23/12/2012
4            4                            1        24/12/2012

tblmedia

medid(PK)   medianame 
---------   ---------
1           casfung 
2           inem 
3           media1
4           tplan
5           casfung test

i want all the record from tblmedAssign where doctor belongs to particular user in tbluser and in the tbluser user is belongs to particular zone and zone id is provided as parameter? for example zoneid= 1;

我想要来自tblmedAssign的所有记录,其中医生属于tbluser中的特定用户,而tbluser用户属于特定区域,区域ID是作为参数提供的?例如zoneid = 1;

i want to select medianame too in the record

我想在记录中选择中值

4 个解决方案

#1

Basically you need to join the four tables with their linking columns. Try this,

基本上,您需要将四个表与其链接列连接起来。尝试这个,

SELECT  a.*,
        b.*,
        c.*,        
        d.*,
        e.*
FROM    tblmedAssign a
        INNER JOIN tblDoctor b
            ON a.doctorID = b.docID
        INNER JOIN tblUser c
            ON b.usrID = c.usrID
        INNER JOIN tblZone d
            ON c.userzoneID = d.zone_ID
        INNER JOIN tblmedAssign e
            ON e.medid = a.medid
WHERE   d.zone_id = @zone_id OR    -- supply value here
        d.zoneName = @zoneName

#2

By JOINing the three tables, something like:

通过加入三个表,类似于:

SELECT -- what you want to select
FROM tblmedAssign ta
LEFT JOIN tbldoctor td ON ta.doctorId = td.docId
LEFT JOIN tbluser tu ON td.usrId = tu.usrId
LEFT JOIN tablzone tz ON tu.userzoneId = tz.zone_Id
WHERE tz.zone_Id = @zoneIdParam

#3

select * 
from tblmedAssign 
inner join tbldoctor on tbldoctor.docId = tblmedAssign.doctorId
inner join tbluser on tbluser.usrId = tbldoctor.usrId
inner join tablzone on tablzone.zone_id = tbluser.userzoneId
where tablzone.ZoneName = 'east' 
or tablzone.zone_id = 2

#4

This should serve your purpose

这应该符合您的目的

select * 
from
tblmedAssign, tbldoctor, tbluser, tablzone 
where
tblmedAssign.doctorId = tbldoctor.docId and 
tbldoctor.usrId = tbluser.usrId and 
tbluser.userzoneId =  tablzone.zone_id and 
(tablzone.zone_id = x or tablzone.ZoneName = 'y')

#1