sybase - values from one table that aren't on another, on opposite ends of a 3-table join
- by Lazy Bob
Hypothetical situation: I work for a custom sign-making company, and some of our clients have submitted more sign designs than they're currently using. I want to know what signs have never been used.
3 tables involved:
table A - signs for a company
sign_pk(unique) | company_pk | sign_description
1 --------------------1 ---------------- small
2 --------------------1 ---------------- large
3 --------------------2 ---------------- medium
4 --------------------2 ---------------- jumbo
5 --------------------3 ---------------- banner
table B - company locations
company_pk | company_location(unique)
1 ------|------ 987
1 ------|------ 876
2 ------|------ 456
2 ------|------ 123
table C - signs at locations (it's a bit of a stretch, but each row can have 2 signs, and it's a one to many relationship from company location to signs at locations)
company_location | front_sign | back_sign
987 ------------ 1 ------------ 2
987 ------------ 2 ------------ 1
876 ------------ 2 ------------ 1
456 ------------ 3 ------------ 4
123 ------------ 4 ------------ 3
So, a.company_pk = b.company_pk and b.company_location = c.company_location. What I want to try and find is how to query and get back that sign_pk 5 isn't at any location. Querying each sign_pk against all of the front_sign and back_sign values is a little impractical, since all the tables have millions of rows. Table a is indexed on sign_pk and company_pk, table b on both fields, and table c only on company locations. The way I'm trying to write it is along the lines of "each sign belongs to a company, so find the signs that are not the front or back sign at any of the locations that belong to the company tied to that sign."
My original plan was:
Select a.sign_pk
from a, b, c
where a.company_pk = b.company_pk
and b.company_location = c.company_location
and a.sign_pk *= c.front_sign
group by a.sign_pk having count(c.front_sign) = 0
just to do the front sign, and then repeat for the back, but that won't run because c is an inner member of an outer join, and also in an inner join.
This whole thing is fairly convoluted, but if anyone can make sense of it, I'll be your best friend.
