October 28, 2007 at 6:11 am
Let's say I have following database structure:
table computers
----------------
ID (int, PK)
NAME (varchar)
table components
----------------
ID (int, PK)
DESCRIPTION (varchar)
table computers_to_components_relations
----------------
rel_ID (int, PK)
COMPUTER_ID (int, FK)
COMPONENT_ID (int, FK)
So you get the idea: each computer can have variable count of components.
But how I can create SQL query to display following:
Computer descriptions:
---------------------------------------
ID NAME COMPONENT1 COMPONENT2 COMPONENT3 ...
...
is it possible in a simple SQL statement or I need some way of looping through each computer ID and select corresponding components through relations table?
Really confused where to start.
I would like to create a statement which could be used as a base for View in SQL Management Studio.
Thanks a lot for any info.
October 28, 2007 at 7:11 am
this would take a tripple join
select ...
from computers C
inner join computers_to_components_relations CCR
on C.ID = CCR.COMPUTER_ID
inner join components Cmp
on CCR.COMPONENT_ID = Cmp.id
where ...
order by ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 28, 2007 at 9:55 am
Thanks for the hint, it shows all the components, but they appear in rows. I need them in columns and that is the problem - is it even possible to SELECT unknown column count?
I do not know, how many components each of computer may have, but I need them all to appear in a row for each computer.
If the database contains following data:
components
--------------
1 ASUS motherboard
2 ATI video
3 Intel CPU
computers
----------------
1 Averagerator
2 Superious
computers_to_components_relations
-------------------------------------
1 1
1 2
2 3
then I need to get the following view:
ID Name Component 1 Component 2
-------------------------------------------------
1 Averagerator ASUS motherboard ATI video
2 Superious Intel CPU
Is it possible?
October 29, 2007 at 12:59 am
have a look at the new pivot methode:
Pivot table for Microsoft SQL Server
http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/
http://www.sqlservercentral.com/articles/Development/2734/
http://www.sqlservercentral.com/redirect/articles/2791/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 30, 2007 at 7:21 am
Pivot will only work if you know the column names.
Dynamic SQL is about your only option here.
CASE statements or Pivot would be the way to implement the statement, but you will have to use dynamic sql to construct the statement after you determine the column names.
October 30, 2007 at 7:25 am
Oh by the way... Proper structure for that table should be 1 table, not 2.
CREATE TABLE BOM_Construct (
ID INT IDENTITY PRIMARY KEY,
PART_OF INT NULL FOREIGN KEY REFERENCES BOM_Construct,
NAME VARCHAR(100)
)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply