Nesting Queries or Another Way

[DANZIG]

Hello,

I'm trying to figure out the best way to get data from 3 tables. I'm not sure if I'm going about it the right way or not though. lol


Table 1 [db_contact_role]
_______________________________________
|idx |device_id |contact_id |contact_priority|
|____|_________|__________|_____________|
|1 |1 |1 |2 |
|2 |4 |7 |1 |
|____|_________|__________|_____________|

Table 2 [lst_contact}
___________________________________
|contact_id |contact_name|contact_office |
|_________|____________|____________|
|1 |John Doe |123-456-7890 |
|7 |Jane Smith |234-567-8901 |
|_________|____________|____________|

Table 3 [lst_device]
________________________________________
|device_id |device_name|device_type|device_ip |
|________|___________|__________|________|
|1 |SQL-Svr-1 |IBM |10.9.8.7 |
|4 |MAIL-Svr-3 |Compaq |10.9.9.3 |
|________|___________|__________|________|


The idea is that the source data is contained in the lst_XXXX tables and is never directly added to a record. The records are created in the db_XXXX tables via id links to the various lst_XXXX tables. The thought behind it is that you could add other tables containing different record types ect with out having to modify your existing source data.

I can use subqueries like the ones below to link one section of the record but am not sure as to the correct way to link the entire record in a single result.


select * from lst_device where device_id IN (select device_id from db_device_role)
select * from lst_contact where contact_id IN (select contact_id from db_device_role)


Would I need to try to nest the queries 4 deep in order to get the result? I've tried using an "AND" cluase but after reading more I see that the clause only works on fileds with in the same record and table.

Any guidance would be greatly appreciated. Please leave an example of the right way to do it if possible.

Thanks in advance.

 

[markVII]

No need for subqueries:

select ld.device_name,dbdr.contact_priority,lc.contact_name
from lst_device ld
,db_device_role dbdr
,lst_contact lc
where LD.device_id = DBDR.device_id
AND LC.contact_id = DBdr.contact_id IN