| Author |
 Topic  |
|
|
ssiidd
Starting Member
1 Posts |
 Posted - 11/29/2012 : 00:51:36
|
Hi everyone,
i need help in making a query that needs to pull a component id from 9 different tables.. for example i need the first 3 columns from the first table and every matching component for these 3 columns. An example with one component is as follows:
select ms.machine_type,ms.machine_model,ms.serial_number,cfs.serial,ms.id,cfs.system_id,cfs.component_id
from management_system ms, hwvpd_cfs cfs
where ms.id=cfs.system_id
group by machine_type,machine_model,serial_number
I want a similar result but including 8 more component tables.
Basically, for every machine,model and serial info i need to get all its components if they exist and null if they dont...Each of the other 8 tables also contain this system_id and their own component_id. How do i go about this so that my final result set looks like this:
Final Result:
machine_type model serial comp1 comp2 comp3 comp4 ... comp9
-------------------------------------------------------------
ABC_________23____1234___NULL__D31___E45___NULL ... E19 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1702 Posts |
Posted - 11/29/2012 : 04:31:58
|
select ms.machine_type,ms.machine_model,ms.serial_number,cfs.component_id,
t1.component_id, t2.component_id,.........,t8.component_id
from management_system ms JOIN hwvpd_cfs cfs ON ms.id=cfs.system_id
JOIN table1 t1 ON t1.system_id = ms.id
JOIN table2 t2 ON t2.system_id = ms.id
............
............
group by machine_type,machine_model,serial_number
--
Chandu |
 |
|
| |
Topic |
|
|
|
Help pulling data from 9 tables
