The Geeks & God Podcast

Something about left joins ??

May 26, 2006 - 9:32am

Joined: 11/28/2008

There is a system that has been used in the past, so we know the code 'works'. Now it is being used as a temporary measure and is crashing on the mysql syntax, here is some of the code that get syntax errors.

CODE

SELECT u.ID, nick, gen, zodie, poza, zi, luna, ann, oras, loc, contor, titlu, state, last, caut, TO_DAYS(CURRENT_DATE)-TO_DAYS(last) AS activ, nr_voturi AS votes, ROUND(suma_voturi/nr_voturi,1) AS nota, e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12, e13, d1,d2,d3,d4,d5,d6,d7,d8 FROM users AS u LEFT JOIN vot AS v ON u.ID=v.ID LEFT JOIN extra AS e ON u.ID=e.id LEFT JOIN descr AS d ON u.ID=d.id WHERE u.ID>1 AND blocat=0 AND mod=0 AND nad=1 AND gen='M' ORDER BY last DESC

and the msg from mySQL is

QUOTE

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 '=0 AND nad=1 AND gen='M' ORDER BY last DESC LIMIT 0,11' at line 1

Wasn't there something about left joins are now more 'strict' in syntax, but I think that was with mySQL version ?? and upwards.

The versions are:

Apache version 1.3.34 (Unix)
PERL version 5.8.7
PHP version 4.4.1
MySQL version 4.1.18-standard

Here is the structure of the USERS table

QUOTE

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
`ID` int(11) NOT NULL auto_increment,
`nick` varchar(20) NOT NULL default '',
`pass` varchar(20) NOT NULL default '',
`email` varchar(50) NOT NULL default '',
`datae` date NOT NULL default '0000-00-00',
`gen` tinytext NOT NULL,
`zi` tinyint(2) unsigned NOT NULL default '0',
`luna` tinyint(2) unsigned NOT NULL default '0',
`ann` year(4) NOT NULL default '0000',
`titlu` varchar(60) NOT NULL default '',
`descriere` text NOT NULL,
`oras` varchar(60) NOT NULL default '',
`poza` tinyint(1) unsigned default NULL,
`blocat` tinyint(1) unsigned NOT NULL default '0',
`contor` int(11) NOT NULL default '0',
`mod` tinyint(1) unsigned NOT NULL default '0',
`inreg` date NOT NULL default '0000-00-00',
`last` date NOT NULL default '0000-00-00',
`credits` smallint(5) unsigned NOT NULL default '0',
`sendmessage` int(1) unsigned NOT NULL default '0',
`search` text NOT NULL,
`zip` varchar(10) NOT NULL default '',
`zodie` smallint(5) unsigned NOT NULL default '0',
`loc` smallint(5) unsigned NOT NULL default '0',
`state` smallint(5) unsigned NOT NULL default '0',
`caut` varchar(20) default NULL,
`nad` int(1) unsigned NOT NULL default '0',
`ip` varchar(15) NOT NULL default '',
`email_notification` tinyint(1) unsigned NOT NULL default '1',
`email_format_html` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`),
KEY `nick` (`nick`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=406 ;

Woops, the cols ...

blocat
mod
nad
gen
last

are all from USERS, so shouldn't that be ?

CODE

WHERE u.ID>1 AND u.blocat=0 AND u.mod=0 AND u.nad=1 AND u.gen='M' ORDER BY u.last DESC

anyway, will give that a try. Don't know why it worked before ??

"The church is never a place, but always a people;
never a fold but always a flock;
never a building but always a believing assembly.
The church is you who pray,
not where you pray."
-Anonymous

May 26, 2006 - 9:51am

jehoshua

Joined: 11/28/2008

Well, that fixed it, .. beats

Well, that fixed it, .. beats me !!

"The church is never a place, but always a people;
never a fold but always a flock;
never a building but always a believing assembly.
The church is you who pray,
not where you pray."
-Anonymous

May 26, 2006 - 4:13pm

Kenaniah

Joined: 11/28/2008

Whenever you do joins, you

Whenever you do joins, you have to name everything using table_name.column_name format because just MySQL doesn't allow you to have ambiguous column names (not attached to a specific table) within table joins.

May 26, 2006 - 8:01pm

jehoshua

Joined: 11/28/2008

Kenaniah @ May 26 2006,

QUOTE(Kenaniah @ May 26 2006, 11:13 AM)

Actually, when you use the as construct it becomes alias.column_name rather than table_name.column_name

In a syntactic sense, the column_name should be referenced by the alias , however in a technical sense, this is not true, proved by the fact that the code has worked up until now. Later releases of MySQL are now adhering 'more to' to SQL standards/compliance, in relation to syntax, like the parentheses in table_references in versions prior to 5.01

HTH

"The church is never a place, but always a people;
never a fold but always a flock;
never a building but always a believing assembly.
The church is you who pray,
not where you pray."
-Anonymous

May 27, 2006 - 1:29am

bobbymac

Joined: 11/28/2008

MOD is a MySQL function name

MOD is a MySQL function name (modulus), so it can't be used on it's own to reference a column. So either tablename.mod OR `mod` (backticks) could be used. That would not have worked in any version of MySQL.

In this case, MySQL sees "mod" and then looks for an opening (, but doesn't find it - it finds an "=", so that's the error you see.

Joins can be done also using "USING" if both tables being joined have a column name in common.

Paul Davey
Whitford Church
"Everyone who calls on the name of the Lord will be saved." Romans 10:13
"For all have sinned and fall short of the glory of God, and are justified

May 27, 2006 - 1:52am

jehoshua

Joined: 11/28/2008

Paul,You are a genius,

Paul,

You are a genius, thanks. I couldn't understand why, in php, the query returned a FALSE all the time, when in phpMyAdmin, I run the same query and all is okay. Of course phpMyAdmin adds the 'ticks', but it always does that for any column. In the end I added a 'quick fix' ....

CODE

if ($result === false) return 0;

after the 'mysql_query()' function, because the function is just to check for users who are blocked, and no-one had that value anyway. This db is just a temp measure.

Thanks for the tip on MOD. /biggrin.gif" style="vertical-align:middle" emoid=":D" border="0" alt="biggrin.gif" />

edit: - just added the ticks to make it 'mod' and there are no errors now.

"The church is never a place, but always a people;
never a fold but always a flock;
never a building but always a believing assembly.
The church is you who pray,
not where you pray."
-Anonymous