Help writing awkward query - 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 2008 Forums

Transact-SQL (2008)

Help writing awkward query

Author

Topic

sparkyboy22
Starting Member

1 Post

Posted - 2010-02-18 : 05:37:42

I am trying to query an sql database but the application it is for has an awkward layout, plus my sql skills are ropey.

Simplified problem:

Table1
Account ID
Name
Address1
etc

Table2
Account ID
Data Type
Data

So table2 stores all sorts of info for people in table1
Some rows of table2 have DataType - Email Address and Data - me@me.com
Other rows have DataType - Product and Data - ProductName

I need a single query that will return all accounts including their email address where a certain ProductName exists.

Not all people in Table1 will have an email address in table2.

Any ideas? or if it is even possible?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 05:40:50

it should be like

SELECT t1.*,t2.Email,t2.ProductName,...
FROM Table1 t1
LEFT JOIN (SELECT AccountID,
           MAX(CASE WHEN DataType='Email Address' THEN Data ELSE NULL END) AS Email,
           MAX(CASE WHEN DataType='Product' THEN Data ELSE NULL END) AS ProductName,
           ....
           FROM Table2
           GROUP BY AccountID)t2
ON t2.AccountID = t1.AccountID

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources