Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ssiidd
Starting Member
1 Post |
Posted - 2012-11-29 : 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_idfrom management_system ms, hwvpd_cfs cfswhere ms.id=cfs.system_idgroup by machine_type,machine_model,serial_numberI 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
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-29 : 04:31:58
|
select ms.machine_type,ms.machine_model,ms.serial_number,cfs.component_id, t1.component_id, t2.component_id,.........,t8.component_idfrom management_system ms JOIN hwvpd_cfs cfs ON ms.id=cfs.system_idJOIN table1 t1 ON t1.system_id = ms.idJOIN table2 t2 ON t2.system_id = ms.id........................group by machine_type,machine_model,serial_number--Chandu |
|
|
|
|
|
|
|