Page 1 of 2
MySQL AS (alias) conflict?
Posted: Sat May 23, 2009 9:55 pm
by JAB Creations
I've encountered some sort of issue where I can't select an id with an alias (AS) from a table because another table has the same column name..even though I'm using the alias to dictate which table I'm choosing from! The "shared" column name is 'id'.
The minimal query before I select from both tables...
Code: Select all
SELECT pm.id, pm.date_sent, pm.message, pm.subject, ua.user_username FROM private_messages AS pm LEFT JOIN user_accounts AS ua ON ua.user_id = pm.id_from WHERE pm.id = '1'
...and when I add cms_sections bit...
Code: Select all
SELECT pm.id, pm.date_sent, pm.message, pm.subject, ua.user_usernameFROM private_messages AS pm, cms_sections AS cs LEFT JOIN user_accounts AS ua ON ua.user_id = pm.id_fromWHERE pm.id = '1'
...phpMyAdmin gives me the following error...
Unknown column 'pm.id_from' in 'on clause'
...obviously if it works without the cms_sections table I can only presume it'll work with it especially since I've used the alias for the table I'm selecting the id from!
Thoughts please?
Re: MySQL AS (alias) conflict?
Posted: Sat May 23, 2009 10:07 pm
by Eran
This can't be the entire query, can it? you aren't selecting anything from the cms_sections table or using it as a filter. Right now it looks like a cross-join, which could be the source of the problem
Re: MySQL AS (alias) conflict?
Posted: Sat May 23, 2009 10:27 pm
by JAB Creations
When I reduce the query to this...
Code: Select all
SELECT pm.date_sent, pm.message, pm.subject FROM private_messages AS pm, cms_sections AS csWHERE pm.id = '1'
...I get the same results for each row/column repeated over 15 times. There are 15 records in the cms_sections table...
When I add to the very end the following...
...it works. However I generally remember when I was first really learning the basics that repeating rows of data generally meant I was selecting wrong (although it wasn't until recent I knew you could select from multiple tables without having to do a JOIN).
If I throw the JOIN in I get the same error. I minimized the query (because you can technically get away without selecting anything from a table even if you don't choose to even though that's a waste) just to minimize any possible troubles.
Here is a table export (without the test data) in case it helps where each table has the same id column names...
cms_sections
Code: Select all
CREATE TABLE IF NOT EXISTS `cms_sections` ( `id` INT(2) NOT NULL AUTO_INCREMENT, `section_name` VARCHAR(32) COLLATE latin1_general_ci NOT NULL, `section_url` VARCHAR(32) COLLATE latin1_general_ci NOT NULL, `meta_description` VARCHAR(112) COLLATE latin1_general_ci NOT NULL, `meta_language` VARCHAR(16) COLLATE latin1_general_ci NOT NULL, `meta_robots` VARCHAR(19) COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=16 ;
private_messages
Code: Select all
CREATE TABLE IF NOT EXISTS `private_messages` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_for` INT(7) NOT NULL, `id_from` INT(7) NOT NULL, `id_folder` INT(1) NOT NULL, `date_read` DATETIME NOT NULL, `date_sent` DATETIME NOT NULL, `message` TEXT character SET hp8 NOT NULL, `subject` TINYTEXT character SET hp8 NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;
Re: MySQL AS (alias) conflict?
Posted: Sat May 23, 2009 10:34 pm
by Eran
...I get the same results for each row/column repeated over 15 times. There are 15 records in the cms_sections table...
That's because a cross join with that table is happening. When you add the second join you have a double cross join which is even worse.
It's always best to indicate specifically the type of join you want to use (in this case, an INNER JOIN). Don't use SELECT .. FROM table1,table2
Re: MySQL AS (alias) conflict?
Posted: Sat May 23, 2009 10:44 pm
by JAB Creations
Oh I see! Why is it doing a double JOIN then if I'm explicitly setting the alias of both tables to join though? Wouldn't I have to manually JOIN the second table to do that?
I suppose I'll have to use UNION then to merge the queries?
Re: MySQL AS (alias) conflict?
Posted: Sat May 23, 2009 10:50 pm
by JAB Creations
WHOA! I took a total guess and this
works!
Code: Select all
SELECT pm.date_sent, pm.message, pm.subject, cs.section_name FROM private_messages AS pmINNER JOIN user_accounts AS ua ON ua.user_id=pm.id_from, cms_sections AS csWHERE pm.id = '1' AND cs.section_url='messages'
WOOHOO!
Thanks for your replies!
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 2:44 am
by Eran
You are still doing it.. use a separate JOIN clause for each table, and specify it's join conditions. There is nothing linking cms_sections to the other tables
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 2:47 am
by JAB Creations
If it were a join clause wouldn't I receive the same error? It seems to work perfectly fine for me.
Does MySQL not allow you to select data from joined tables and a third (not joined) table?
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 4:33 am
by Eran
There are only two ways to select data from separate tables - a JOIN and a UNION
When you specify two table names separated with a comma, it is converted to a JOIN internally by the optimizer. The type of join will be decided by the other parameters of the query - but it's better to specify it yourself to prevent accidental cross-joins (which you were getting previously).
There is no reason to use tables that have no relation in the same query. Use separate queries for that
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 7:37 am
by JAB Creations
Is there a way I can have MySQL tell me if it's treating that third table as a join? If the issue was because of a join and it's not longer occurring I can only presume it's no longer joined. I recently learned you can select from multiple tables in MySQL without joins or unions...
You've always given me great advice so you definitely have my gratitude...I'm just saying it seems to work and here is about the best thing I can come up with for an explanation...
SELECT is used to retrieve rows selected from one or more tables
http://dev.mysql.com/doc/refman/5.0/en/select.html
It would be nice if we could get a third or fourth opinion.

Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 8:34 am
by Eran
A join is part of the SELECT syntax obviously.
This:
is a join. Just alternative syntax.
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 1:28 pm
by jayshields
I thought
gave the cartesian product?
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 1:59 pm
by VladSun
We had a discussion about it:
viewtopic.php?f=2&t=95398
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 3:30 pm
by Eran
I thought .. gave the cartesian product?
Yeah, joining without a condition gives a cartesian product. Which is why I'm trying to convince JAB to use a proper join syntax with an ON condition
Vlad, any take on this?
Re: MySQL AS (alias) conflict?
Posted: Sun May 24, 2009 6:21 pm
by jayshields
pytrin wrote:I thought .. gave the cartesian product?
Yeah, joining without a condition gives a cartesian product. Which is why I'm trying to convince JAB to use a proper join syntax with an ON condition
Vlad, any take on this?
Read the thread he linked to - I learnt a few things from that thread earlier!