One-to-Many SELECT issue - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

One-to-Many SELECT issue

Author

Topic

igendreau
Starting Member

6 Posts

Posted - 2013-12-20 : 10:00:18

Not much of an SQL expert, so wondering if you guys can help. I have the following two tables with these fields:

tblProducts
productID
productName
productDescription

tblAttributes
attributeID
productID
attributeName
attributeValue

The logic is there is a one-to-many relationship from tblProducts.productID to tblAttributes.productID. So one product, can have a bunch of attributes. Some of the attribute values will look like:

01 1747 Weight 14.7
02 1747 Price 199.99
03 1747 Quantity 412

What I'm trying to get is a SELECT statement that gets me a single row of data that gives me product #1747 with columns for it's weight, price and quantity.

Doable?

igendreau
Starting Member

6 Posts

Posted - 2013-12-20 : 10:21:35

Just to be clear, the data examples above are the 4 fields from the tblAttributes table. Alignment got a little screwy. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-20 : 11:28:33

[code]
select p.productID,
p.productName,
p.productDescription,
w.attributeValue as Weight,
r.attributeValue as Price
from tblProducts p
inner join tblAttributes w on p.productID = w.productID and w.attributeName = 'Weight'
inner join tblAttributes r on p.productID = r.productID and r.attributeName = 'Price'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-21 : 01:33:52

quote:
Originally posted by igendreau

Just to be clear, the data examples above are the 4 fields from the tblAttributes table. Alignment got a little screwy. Thanks!

if you're only interested in Weight,price and quantity then this is enough

 
SELECT p.productID,p.productName,p.productDescription,
t1.[Weight],t1.[Price],t1.[Quantity]
FROM tblProducts p
INNER JOIN (SELECT productID,
            MAX(CASE WHEN attributeName = 'Weight' THEN attributeValue END) AS [Weight],
            MAX(CASE WHEN attributeName = 'Price' THEN attributeValue END) AS [Price],
            MAX(CASE WHEN attributeName = 'Quantity' THEN attributeValue END) AS [Quantity]
            FROM tblAttributes
            GROUP BY productID
            )t1
ON t1.productID = p.productID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-21 : 01:35:43

if you want to display all available attributes at runtime use this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources