"Blending" Two Tables

Hi All,

I've searched and haven't found anything so if this has been answered, please point me to the correct thread.

I'm trying to "blend" two tables together into a dataset for use in an asp project - sort of merging but not exactly. Here is the scenario:

Table 1 - Software

MfrID ProductID Description

1 1 Word

1 2 Excel

1 3 Access
2 1 AutoCAD

2 2 Inventor

- where 1=Microsoft; 2 = Autodesk

Table 2 - User Proficiency

UserID ProductID SkillLevel

1 1 2

1 2 3

So I want to end up with:

ProductID Description SkillLevel

1 Word 2

2 Excel 3

3 Access null --> or anything; the point is this record!

If I use a LEFT JOIN, I get it to work for ALL users/ALL Products but as soon as I specify WHERE clauses for USERID and MFRID [respectively], I only get all of one table or the other. If I apply a literal new column "1 AS SkillLevel" on Products and use a UNION, I get "duplicates" because the values for SkillLevel are different.

What I want is to ignore the SkillLevel row if it is the null or 1 case...IOW if the user has a setting use it instead of the default, otherwise use the default. I hope this makes sense.

TIA

Mike

Ok..One possible solution, instead of using the where clause filter, on the join condition itself you can apply the filter (be carefull, it may change the entire query result)

But this is depends with what result you need.

Can you post the detailed sample data. I feel there is some gap on your posted sample data.

|||

There is not enough data in the Proficiency table. You cannot distinguish between any Manufacturer's ProductID. Whose ProductID = 1? Microsoft or Autodesk?

Surely you don't mean to equate a SkillLevel of 2, for ProductID = 1 means that the user is equally proficient in both Word and Autocad.

And your Proficiency table doesn't indicate that there is anyone with skills in Access, yet you want Access in the desired output -But following the same 'rules', Autocad and Inventor 'should' also be in the output with NULL values for SkillLevel. But it is not -why?

|||

Hi Mike,

I agree with Arnie, you need to expand more about what you want to accomplish here. This is a guess:

select

ProductID,

Description,

(select max(up.SkillLevel) from dbo.[User Proficiency] as up where up.ProductID = s.ProductID) as SkillLevel

from

dbo.software as s

where

MfrID = 1;

Select the maximum [SkillLevel] from table [User Proficiency], for each row that match the criteria in table [Software].

Notice that there is not relation with the [MfrID], so it could be that we select the [SkillLevel] from a product that does not belong

to the same manufacture.

AMB

|||

Ok, sorry guys! I was trying to simplify this and I guess it backfired on me. To answer the questions - I work for a global Autodesk reseller. We have hundreds of trainers and each has a multi-level proficiency heirarchy such as Demo-Low Level which means the person can Train at the highest level AND is a low level Demo jock. I'm working on taking the old system, moving it forward and putting it on the web. I have tables out the backend with lookups and everything else so in my example, the Products table is linked to a manufacturer's table to pull in the Microsoft, or Autodesk, etc. as are the skill levels, users, and on and on.

What happens is products fall into "Verticals" with Autodesk so there is MSD [Mechanical software], BSD [Architectural software], etc. Each of these could have a dozen programs and a trainer can be proficient in some and not know others within that Vertical. Some trainers can also be multi-Vertical so they have skills in multiple areas but, again, may only know one or two programs within each specific vertical. Also programs are added as Autodesk releases them so the user may not have a proficiency yet if the program was just added.

So what I want to do is

Step 1: Find out which Verticals the trainer in question has selected, then pull ALL of the software programs from that Vertical into a result which goes to a dataset then an ASP gridview.

Step 2: Then poll the UserSkills table and return any proficiencies the user has already set for any of the given software and set the Skill Level for any of the rows returned in Step 1

Now, I could do this in 2 steps looping through the results from Step 2 and applying them to Step 1. If I can do this in a single SQL statement it would work a lot more efficiently. Here is my closest attempt:

SELECT DISTINCT Products.VerticalID, Products.ProductID, 1 AS SkillPrimaryID, 1 AS SkillSecondaryID FROM Products

WHERE VerticalID='4'

UNION

SELECT DISTINCT UserSkills.VerticalID, UserSkills.ProductID, UserSkills.SkillPrimaryID, UserSkills.SkillSecondaryID FROM UserSkills WHERE UserID='6' AND VerticalID='4'

ORDER BY ProductID

This produces this result where the user only has set a proficiency for Vertical #4 -> Product #54:

4 54 1 1
4 54 4 1
4 55 1 1
4 56 1 1
4 57 1 1
4 58 1 1
4 59 1 1

You can see where lines 1 and 2 are "duplicates" - line 1 has the default "1" and line 2 has the user's stored value of "4". Now I realize that these are not duplicates to SQL since the values are different. What I want to do is have this result set w/o line 1 since line 2 is the row I need. Does this clear things up? Whether the first Select adds a literal or not doesn't matter to me if there is some other way to accomplish this. I added the literal so I could use the Union.

|||Awesome! Thank you very much AMB!!! I got it using:

select ProductID, ProductDescription, (select max(up.SkillPrimaryID) from dbo.UserSkills as up where up.ProductID = s.ProductID and userid='6') as SkillLevel

from dbo.Products as s where VerticalID = '4';

I handle cad programming and there's not sql typically so I'm rusty. I definitely need to read up on the filters!

Mike