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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help pulling data from 9 tables

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_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
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_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
Go to Top of Page
   

- Advertisement -