Multiple select as from 1 table

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

General SQL Server Forums

New to SQL Server Programming

Multiple select as from 1 table

Author

Topic

Benku
Starting Member

10 Posts

Posted - 2012-01-13 : 00:59:33

Hello,

i have the following problem.

i have 2 tables. 1 fact and 1 with some descriptions called attributes.

(table 1)
fact
Account Attribute1 Attribute2 Value
1000 a b 100
1000 b a 100
1000 a v 100
1000 f f 100
1000 a e 100
1000 f a 100
1000 b d 100

(table 2)
Attribute
ID ItemID Label
1 a Desc1
2 b Desc2
1 b Desc3
1 f Desc4
2 v Desc5
2 a Desc6

I want to obtain the following result

Account A1 A2 Value
1000 Desc1 Desc2 100
1000 Desc3 Desc6 100
1000 Desc1 Desc5 100

doing it for 1 Attribut was easy

SELECT Label As A1 FROM Attribute WHERE ID = '1'

but i don't know how to have to AS statement related to the same table but giving out 2 columns..

any help appeciated
Benku

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 03:17:38

just use simple joins to attribute table


SELECT f.Account,a1.Label,a2.Label,f.Value
FROM fact f
INNER JOIN ATtribute a1
ON a1.ItemID = f.Attribute1 
INNER JOIN ATtribute a2
ON a2.ItemID = f.Attribute2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Benku
Starting Member

10 Posts

Posted - 2012-01-13 : 04:07:08

Thank you Visakh. It works like a dream and i could also very easily extend it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 04:31:51

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Benku
Starting Member

10 Posts

Posted - 2012-01-13 : 12:04:36

Actually i spoke too fast.

the result is that each line appears twice or as many time as i am adding attributes.

any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 12:08:12

one question.how are tables related?
i see ItemID repeating in table2.in that case how you want tables to be related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Benku
Starting Member

10 Posts

Posted - 2012-01-13 : 13:01:08

That is the tricky part i guess.

the ID of table 2 represents the column of table 1

so 1 for attribute 1, 2 for Attribute 2...

the ItemID represents the various element for each Attribute.

Unfortunately, those tables are not mine, so that i cannot change them. i am just trying to retrieve their content to present it in a way that is more convenient for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 23:58:21

then how do you determine which accounts descriptions each of them represent in table2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Benku
Starting Member

10 Posts

Posted - 2012-01-14 : 01:23:18

here an example

take line 1 of table 1

attribute1 = a so from table 2, it means id = 1 and itemID = a so Desc1
attribute2 = b so from table 2, it means id = 2 and ItemID = b so Desc2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 01:29:44

quote:
Originally posted by Benku

here an example

take line 1 of table 1

attribute1 = a so from table 2, it means id = 1 and itemID = a so Desc1
attribute2 = b so from table 2, it means id = 2 and ItemID = b so Desc2

no you missed my point
there are multiple records with id =1 and 2 in table2

my question is how do you link this to an account
as of now there's nothing in table2 to determine what account its linked too
So unless you're sure ID,ItemID combination wont repeat in table2 you'll have difficulty matching descriptions to correct account

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Benku
Starting Member

10 Posts

Posted - 2012-01-14 : 02:14:31

you are correct, Table 2 is solely to provide information on attributes, accounts are defined in another table.

Now the problem is not match the correct description with the account. that works just fine. the problem is that for each line in table 1, i get 2 lines (identical) in my result.3 if 3 attibutes, 4 if 4 attributes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 02:17:16

[code]
SELECT f.Account,a1.Label,a2.Label,f.Value
FROM fact f
INNER JOIN ATtribute a1
ON a1.ItemID = f.Attribute1
AND a1.ID=1
INNER JOIN ATtribute a2
ON a2.ItemID = f.Attribute2
AND a2.ID =2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Benku
Starting Member

10 Posts

Posted - 2012-01-14 : 02:59:40

Thank you. that solved the problem :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 04:28:46

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources