Page 1 of 1
[Solved] Implicit inner joins issues
Posted: Thu Jan 11, 2007 6:01 pm
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.
Posted: Thu Jan 11, 2007 6:03 pm
by feyd
What's the difficulty? It appears straight forward.
Posted: Thu Jan 11, 2007 6:08 pm
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.
Posted: Thu Jan 11, 2007 6:10 pm
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'.
Posted: Thu Jan 11, 2007 6:11 pm
by volka
Unknown column 'I.itemid' in 'on clause'
your table jtrd_items has no column itemid
Posted: Thu Jan 11, 2007 6:15 pm
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
Posted: Thu Jan 11, 2007 6:19 pm
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.
Posted: Thu Jan 11, 2007 6:20 pm
by feyd
oops, I forgot to remove the comma before it.
Posted: Thu Jan 11, 2007 6:25 pm
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?
Posted: Thu Jan 11, 2007 6:38 pm
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.

Posted: Thu Jan 11, 2007 6:45 pm
by DaveTheAve
Thanks to your prior code I was able to modify it to make the other SQL Querys work.
Thanks again.