Logical thinker needed!

[nickdel]

Ok, this is causing me a headache as I just can't figure out how to go about this without getting stuck in a massive loop!

Here's a little scenrio..

Customer A
Needs Bread
Has Milk to sell

Customer B
Needs Milk
Has Bread to sell

So simply

select CustID from customers where need='Milk' and wants='Bread'

and CustomerB would be returned...

Now what if I wanted to expand on this so that...

Customer A
Needs Bread
Has Milk to sell

Customer B
Needs Beans
Has Bread to sell

Customer C
Needs Milk
Has Beans to sell

So CustomerA needs CustomerB but CustomerB doesn't need what CustomerA has to sell but CustomerB does need what CustomerC has and CustomerC needs what CustomerA has to sell.
I could wait for a perfect match but ideally if I can get the three of them together then everyone gets what they want.

I just cannot think how on earth to do this! There's bound to be some sort of mathematical solution to this but I was never that good at math!

Any takers? Think of it as a puzzle!

Thanks

 

[AlexCuse]

I don't understand what you need here. Is it simply 'who will sell what to who?'. If so, have a look at this (may need to mess around with the temp table stuff depending on what DBMS you are using):

[COLOR=green]--sample data
[/color][COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (customer [COLOR=blue]char[/color](1), need [COLOR=blue]varchar[/color](15), sell [COLOR=blue]varchar[/color](15))

[COLOR=blue]insert[/color] #t 
[COLOR=blue]select[/color] [COLOR=red]'A'[/color], [COLOR=red]'bread'[/color], [COLOR=red]'milk'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'B'[/color], [COLOR=red]'beans'[/color], [COLOR=red]'bread'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'C'[/color], [COLOR=red]'milk'[/color], [COLOR=red]'beans'[/color]

[COLOR=blue]select[/color] a.customer [COLOR=blue]as[/color] Seller, a.sell [COLOR=blue]as[/color] Item, b.customer [COLOR=blue]as[/color] Buyer
[COLOR=blue]from[/color] #t a 
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] #t b
[COLOR=blue]on[/color] a.sell = b.need

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

If this is not it, please clarify.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[johnherman]

I think you're right, this is a mathematical solution, but not a "set theory" solution, which SQL is based on. This would appear to be an iterative routine which would attempt to trade with anyone who needs what that particular vendor has excess of, then again try to trade, looping until it has succeeded in doing a multi-vendor trade or fails. Then, you would need to analyze the multi-vendor trades to find those which satisfy all parties.

Since this is an iterative approach, I don't think it lends itself to set theory and ANSI-SQL, but if someone finds a way to do it, I'd be more than happy to test it and learn.

This looks like you need a language that loops, like PL/SQL, T-SQL, SQL/PL, or whatever procedural SQL your database supports.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[nickdel]

Thanks John, think you've explained what I'm trying to do better than I have!

Hate to cross post but I reckon I will stick this in the vb.net forum as this is what the application is built in, I just thought maybe doing this on the database would be more efficient!

Cheers

Nick