Simply difficult join? - 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 2000 Forums

SQL Server Development (2000)

Simply difficult join?

Author

Topic

duncanwill
Starting Member

20 Posts

Posted - 2008-04-26 : 10:04:13

Hi,

I have a problem that I've come across before but never satisfactorily solved. It seems easy (perhaps it is?).

I have an employees table:
ID Employee_Name

I have a products table:
ID Product_Name

I have a link table between the two identifying who sells what products:
ID Employee_ID Product_ID

I want a report showing me all employees with which products each employee can sell in the following format:

Employee_ID Employee_Name Product1 Product2 Product3 Productx
1 Fred Smith Yes No Yes No
2 John Walker No No No No

This seems easy - logically easy to understand but my joining prowess fails me. The report needs ideally to be a single Proc returning one recordset (it is going to a web query in excel via an asp - don;t ask, it just is!)

I hope this makes sense - help and a solution greatfully recieved.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-26 : 10:10:20

your link table should not have an ID column. the pk in this table should be (Employee_ID, Product_ID). Otherwise, you could have dupes in these two columns, which makes no sense.

what you want is called a crosstab report - search for that and you'll find plenty of examples.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 10:24:27

quote:

Originally posted by visakh16

SELECT e.ID AS Employee_ID,
       e.Employee_Name,
       MAX(CASE WHEN p.Product_Name='Product1' THEN 'Yes' END) AS Product1,
 MAX(CASE WHEN p.Product_Name='Product2' THEN 'Yes' ELSE 'No' END) AS Product2,
 MAX(CASE WHEN p.Product_Name='Product3' THEN 'Yes' ELSE 'No' END) AS Product3,
 MAX(CASE WHEN p.Product_Name='Product4' THEN 'Yes' ELSE 'No' END) AS Product4,
....
 MAX(CASE WHEN p.Product_Name='Productx' THEN 'Yes' ELSE 'No' END) AS Productx
FROM employees e
LEFT JOIN linktable l
ON l.Employee_ID=e.ID
LEFT JOIN products p
ON p.ID=l.Product_ID
GROUP BY e.ID,e.Employee_Name

duncanwill
Starting Member

20 Posts

Posted - 2008-04-26 : 10:34:06

Thanks visakh - I've actually used that solution before (and it works) but I forgot to mention that the number of products are variable (i.e. users can add and remove products - so productX could be the tenth or 1000th product).

I think the solution needs a known number of products?

Jezemine - yes the ID is irrelevant I guess and the application prevents duplicates. I'll look at cross tab stuff, but not sure if it will solve my zero, one or many product problem (i.e. zero one or many product columns).

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-26 : 10:46:02

there are ways to do it, but you have to use dynamic sql.

for example: http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

elsasoft.org

duncanwill
Starting Member

20 Posts

Posted - 2008-04-26 : 11:14:15

hmmm thanks again - i was afraid of that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources