[Solved] Implicit inner joins issues

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

[Solved] Implicit inner joins issues

Post by DaveTheAve »

Code: Select all

$sql = "SELECT I.*,IT.name as itname,RB.firstname FROM jtrd_items I,jtrd_itemtypes IT LEFT JOIN jtrd_repby RB ON I.itemid=RB.repid WHERE I.itypeid=IT.id AND I.uid!=$uID $st_sql $nametitle_sql $nw_sql $mod_sql $limits";

$result = mysql_query($sql) or die ("Wrong SQL query! $sql " . mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     $ret[$row['itemid']] = $row;
}
First of all, WTF is that?

Here it is again but easier to read:

Code: Select all

$sql = "
SELECT I.*,
IT.name as itname,
RB.firstname FROM jtrd_items I,
jtrd_itemtypes IT LEFT JOIN jtrd_repby RB ON I.itemid=RB.repid 
WHERE I.itypeid=IT.id 
AND I.uid!=$uID 
$st_sql 
$nametitle_sql 
$nw_sql 
$mod_sql 
$limits";
Second of all, this is what my employer current has for his CMS and, as you can tell, I need to fix this NOW.

Can someone help me out with at LEAST understanding this thing? I mean I have 5years of MySQL experience but this stuff is a first. Oh and I should tell you this works on his OLDER MySQL server but not on my NEWER version of MySQL I have on my personal development server.
Last edited by DaveTheAve on Thu Jan 11, 2007 6:45 pm, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What's the difficulty? It appears straight forward.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

and what error message do you get?
DaveTheAve wrote:Oh and I should tell you this works on his OLDER MySQL server but not on my NEWER version of MySQL I have on my personal development server.
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Post by DaveTheAve »

Wow... that was fast...
Development_Server wrote: Wrong SQL query! SELECT I.*,IT.name as itname,RB.firstname FROM jtrd_items I,jtrd_itemtypes IT LEFT JOIN jtrd_repby RB ON I.itemid=RB.repid WHERE I.itypeid=IT.id AND I.uid!=170 LIMIT 0,10 Unknown column 'I.itemid' in 'on clause'
Quoth the server: '404'.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Unknown column 'I.itemid' in 'on clause'
your table jtrd_items has no column itemid
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try

Code: Select all

SELECT
	jtrd_items.*,
	jtrd_itemtypes.name as itname,
	jtrd_repby.firstname
FROM jtrd_items,
INNER JOIN jtrd_itemtypes
	ON
	(
		jtrd_items.itypeid = jtrd_itemtypes.id
		AND
		jtrd_items.uid <> $uID
	)
LEFT JOIN jtrd_repby
	ON
	(
		jtrd_items.itemid = jtrd_repby.repid
	)
WHERE
	$st_sql
	$nametitle_sql
	$nw_sql
	$mod_sql
	$limits
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Post by DaveTheAve »

Yes their is a itemid...

SELECT jtrd_items.*, jtrd_itemtypes.name AS itname, jtrd_repby.firstname FROM jtrd_items, INNER JOIN jtrd_itemtypes ON ( jtrd_items.itypeid = jtrd_itemtypes.id AND jtrd_items.uid <> 170 ) LEFT JOIN jtrd_repby ON ( jtrd_items.itemid = jtrd_repby.repid ) WHERE LIMIT 0,10 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN jtrd_itemtypes ON ( jtrd_items.itypei' at line 6

is the error i'm getting with feyd's sql.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

oops, I forgot to remove the comma before it.
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Post by DaveTheAve »

Feyd Thanks your SQL works 90%... The where clause is wrong.. If i remove that part it works but - of-course - their is no where filter.

Care to tell me were to learn this? There are a few SQL Querys like this that need to be rebuilt.

Edit: Well, I don't know for a fact but did you accidentally include the WHERE clause? It appairs you placed the WHERE clause contents before it and don't even need it... or am I just reading this wrong?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

DaveTheAve wrote:Feyd Thanks your SQL works 90%... The where clause is wrong.. If i remove that part it works but - of-course - their is no where filter.

Care to tell me were to learn this? There are a few SQL Querys like this that need to be rebuilt.

Edit: Well, I don't know for a fact but did you accidentally include the WHERE clause? It appairs you placed the WHERE clause contents before it and don't even need it... or am I just reading this wrong?
I just copied your post's query and modified it to more standard syntax. The WHERE clause is pretty much as is, made entirely of variables like your original post.

The query was using implicit inner joins, where as I used explicit ones.

I shifted one part of the WHERE clause to the inner join which should help jump circuit the request making it a bit faster in the process. :)
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Post by DaveTheAve »

Thanks to your prior code I was able to modify it to make the other SQL Querys work.

Thanks again.
Post Reply