SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help pulling data from 9 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssiidd
Starting Member

1 Posts

Posted - 11/29/2012 :  00:51:36  Show Profile  Reply with Quote
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
2170 Posts

Posted - 11/29/2012 :  04:31:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000