![]() |
![]() |
#1 | ||
Coordinator
Join Date: May 2003
Location: Utah
|
![]() Alright,
I just wrote the following query: Code:
Just as I run it in MySQL...I found out we are running an outdated version of MySQL that doesn't support subselects. I need to rewrite this using JOINS.... Now let me say the following....JOINS are not my strong suit, and neither is MySQL.....any help that someone can give me, I would extremely appreciate it....
__________________
"forgetting what is in the past, I strive for the future" |
||
![]() |
![]() |
![]() |
#2 |
Coordinator
Join Date: Oct 2000
Location: Maassluis, Zuid-Holland, Netherlands
|
Hate to sound like an ass, but with that many joins, I'm tempted to think your data should be represented differently.
__________________
* 2005 Golden Scribe winner for best FOF Dynasty about IHOF's Maassluis Merchantmen * Former GM of GEFL's Houston Oilers and WOOF's Curacao Cocktail |
![]() |
![]() |
![]() |
#3 |
Death Herald
Join Date: Nov 2000
Location: Le stelle la notte sono grandi e luminose nel cuore profondo del Texas
|
I've been looking at this for a while now, and I don't see a way to do it without using a subselect.
__________________
Thinkin' of a master plan 'Cuz ain't nuthin' but sweat inside my hand So I dig into my pocket, all my money is spent So I dig deeper but still comin' up with lint |
![]() |
![]() |
![]() |
#4 |
College Starter
Join Date: Feb 2004
Location: Buffalo, NY
|
The "not in" clause usually (always?) cannot be replaced by a join. Same thing with "not exists".
|
![]() |
![]() |
![]() |
#5 | |
Coordinator
Join Date: May 2003
Location: Utah
|
Quote:
Yeah I know...it's why I am here.....I am rebuilding the system, moving from the PHP/MySQL solution they have been using and rolling to an MS SQL and .NET. But in the meantime I am stuck supporting and snagging data for people out of this system. I figured out how to do it, but I need root access to the machine, and the MIS guys have said no, they will upgrade MySQL to 4.1 for me ![]() Thanks for looking and trying to help me out here guys,.....
__________________
"forgetting what is in the past, I strive for the future" |
|
![]() |
![]() |
![]() |
#7 |
Pro Starter
Join Date: Jul 2005
Location: Appleton, WI
|
Would you be able to create a view using that subselect you have and then left join your cust_id against that, checking for cust_ids not in that view...
ci.cust_id LEFT JOIN your view yv ON ci.cust_id <> yv.cust_id Not sure how well that would work...
__________________
Commissioner of the RNFL |
![]() |
![]() |
![]() |
#8 | |
Coordinator
Join Date: May 2003
Location: Utah
|
Quote:
Would love to....but they are not supported in MySQL.....
__________________
"forgetting what is in the past, I strive for the future" |
|
![]() |
![]() |
![]() |
#9 | |
n00b
Join Date: Mar 2005
|
Quote:
If it doesn’t support inline views, then I’d create a temp table with the select statement in the subquery. Then I’d join those tables together. |
|
![]() |
![]() |
![]() |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|