Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 5th, 2004, 05:01 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Exclusive selection

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.
Reply With Quote
  #2 (permalink)  
Old July 5th, 2004, 01:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old July 5th, 2004, 04:35 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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" <:-)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help - Shared MDE - Exclusive Access message box surajguru Access 3 February 22nd, 2007 12:18 PM
E-mail reports with exclusive rights Brendan Bartley Access 1 August 2nd, 2005 11:49 PM
DTD "exclusive or" confusion smallett XML 1 June 19th, 2005 04:49 AM
Exclusive ADO Connection bsa Pro VB Databases 1 September 6th, 2004 07:16 AM
Exclusive ADO Connection bsa SQL Server 2000 2 August 24th, 2004 12:10 AM



All times are GMT -4. The time now is 06:11 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.