April 13, 2008 at 5:26 pm
I am purposely not pasing in a bunch of SQL on this point as I want to figure this one out but am not sure how to approach this logically.
I have 3 tables in my DB
* Customer
* Skill
* Customer_Skill_Map
The customer table lists all defined customers
The skill table lists all defined skills
The custoemr_skill_map table does NOt list what you'd probably think it does. 🙂
So, the map table ties the customer ID and skill ID together for customers and skills that DON'T belong together. I guess this was done to accomodate some application component that made it easier to work with the data, I'm really not sure.
The whole goal is to retrieve a result set which lists the skills associated with each customer, grouped by customer.
I guess I'm having a hard time reversing my thinking to determine how I get the skills that are ASSOCIATED with each custoemr when the map table tells me which skills are EXCLUDED from the different customers.
Like I said, I'm just looking for how I should approach this since the logic seems somewhat backwards to me.
Thanks,
Bob
April 13, 2008 at 10:57 pm
You will want to look at "Not IN(...)" or "Not EXISTS(...)"
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2008 at 7:33 am
How about creating a new junction table that has the rows you would normally join on?
SELECT ...
FROM
(SELECT t1.custid, t1.skillid
FROM
(SELECT t2.id AS CUSTID, t3.id AS skillid
FROM customer t2
CROSS JOIN skills t3) t1
LEFT OUTER JOIN customer_skill_map t4 ON (t4.custid=t1.custid AND t4.skillid = t1.skillid)
WHERE t4.skillid IS NULL) dt
JOIN customer ...
JOIN skill ...
April 14, 2008 at 8:34 am
Would a cross-join between customers and skills, and a left join to the map table, with a null select in the Where for the map table, work?
Something like:
select Customer, Skill
from dbo.Customers
cross join dbo.Skills
left outer join dbo.Customers_Skills_Map CSM
on customers.id = csm.customerid
and skills.id = csm.skillid
where csm.customerid is null
Would that do it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply