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 2012 Forums
 Transact-SQL (2012)
 One-to-Many SELECT issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

igendreau
Starting Member

6 Posts

Posted - 12/20/2013 :  10:00:18  Show Profile  Reply with Quote
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 - 12/20/2013 :  10:21:35  Show Profile  Reply with Quote
Just to be clear, the data examples above are the 4 fields from the tblAttributes table. Alignment got a little screwy. Thanks!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17636 Posts

Posted - 12/20/2013 :  11:28:33  Show Profile  Reply with Quote

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'



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/21/2013 :  01:33:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/21/2013 :  01:35:43  Show Profile  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000