Exclusive selection

RobbieGee · July 5th, 2004, 05:01 AM

Not sure how I should phrase this...

I need to combine two tables, but the rows from the table I'm joining should only be joined once.

Say I have a table called Cars, which contains all the cars I own (let's assume I'm filthy rich just for a moment ;). I have another table with available motors which I need to fit into the cars. Now, once I've paired up a motor with a car, that motor can no longer be assigned to another car.

Do you have any idea how I would go about doing this?

Thanks.

happygv · July 5th, 2004, 01:25 PM

Can you post the structure of those tables and explain more on what you are trying to accomplish?

_________________________
- Vijay G
Strive for Perfection

RobbieGee · July 5th, 2004, 04:35 PM

I solved the problem, but only after realizing I had over-simplified it. I can't disclose what exactly it is what I'm trying to find, but the problem is equivalent to a used car dealer trying to find the best match for all his spare hubcaps for spare tires. He needs a computer system which finds the exact radius match, coupled with a width (thickness?) close to what will fit. Naturally, we can't pair a hubcap more than once.

(You have to excuse my analogy, I couldn't think of anything better).

Given the following data:

Code:

Wheels:
id  width radius
1   4     150
2   4     250
3   5     150
4   5     300
5   10    450


Hubcaps:
id  width radius
1   4     150
2   5     250
3   6     300
4   7     450
5   8     600

The first step was identifying all hubcaps which would fit in any tire:

Code:

SELECT w.* , c.* 
FROM wheels w
LEFT JOIN hubcaps c ON ( w.radius = c.radius ) 
WHERE w.width > c.width -2 AND w.width < c.width +2

(wheel on left, hubcap on right)
id  width radius    id  width radius     
1   4     150       1   4     150    
3   5     150       1   4     150    
2   4     250       2   5     250    
4   5     300       3   6     300

As we see, hubcap no. 1 will fit in both tire 1 and 3. Hubcap 2 and 3 will fit in tire 2 and 4 respectively, while we have no tire in which hubcap 4 will fit.

Now the problem was it returned some duplicates we couldn't be sure of, and I needed these left out. To solve this I grouped the rows with the hubcaps identifier and left out any rows with more than one hubcap:

Code:

SELECT w.* , c.*, COUNT(*) counter
FROM wheels w
LEFT JOIN hubcaps c ON ( w.radius = c.radius ) 
WHERE w.width > c.width -2 AND w.width < c.width +2
GROUP BY c.id
HAVING counter = 1;

(wheel on left, hubcap on right)
id  width  radius    id  width  radius    counter     
2   4      250       2   5      250       1    
4   5      300       3   6      300       1

--
Now we're left with hubcap 2 and 3 fitting in tire 2 and 4 respectively. All other hubcaps and tires are too unsure or left over.

Hope this is of any use to someone out there. If not, it's at least a useful "note to self" <:-)