MySQL Error - JOINING

[MasterACE14]

Afternoon Everyone,

 

I'm trying to do a MySQL JOIN for the first time  , I have however ran into a MySQL Error I have not encountered before.

 

I have coded my page to display MySQL Errors and die if they occur.

 

Here is what I am getting:

Query:

SELECT `id` FROM `cf_users`,`cf_users2` WHERE `cf_users`.`id`=`cf_users2`.`id` LIMIT 1

 

Error:

Column 'id' in field list is ambiguous

 

any help is greatly appreciated 

 

Regards ACE

[Aureole]

Try this... though MYSQL spits errors at me for just looking at it wrong so don't expect it to work.

 

<?php
$query = "SELECT `id` from `cf_users` INNER JOIN `cf_users2` ON `cf_users2`.`id` = `cf_users`.`id` LIMIT 1";
$result = mysql_query($query);
?>

[btherl]

Here's another try .. untested as I do not have mysql

 

<?php
$query = "SELECT `cf_users`.`id` from `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1";
$result = mysql_query($query);
?>

[MasterACE14]

Aureole's script didn't work  :-\

 

btherl's script did work, for all columns in `cf_users` , but doesn't work for any column in `cf_users2`.

 

here's the error I got:

Query:

SELECT `cf_users`.`level_supplydrop` from `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1

 

Error:

Unknown column 'cf_users.level_supplydrop' in 'field list'

 

by the way, here's the code I am using, note the $select variable is what I use for flexablity to select various columns.

 

<?php
$sql = "SELECT `cf_users`.`" . $select . "` from `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1";

[Aureole]

Weird how mine didn't work when they were both basically the same... plus the way I showed you how to do it; that's how I do it and it works fine.

 

MYSQL eludes me sometimes.

 

Not sure what your problem is anyhow, sorry.

[MasterACE14]

this is very strange  :-\ , anyone got any new ideas?

[aschk]

Basically you have a column in both tables called "id", so when you write your SELECT part of your statement (SELECT id...) how does MySQL know whether you want the id column from cf_users1 or cf_users2 ? It doesn't, hence it's ambiguous. Thus you need to specify which table you want the id column from, or if you want both use both.

 

example :

SELECT c1.id
      ,c2.id
FROM cf_users c1
JOIN cf_users2 c2 ON c1.id = c2.id 
LIMIT 1

 

Also, see how I used table aliases (c1,c2) to make readability easier as well as maintenance. Don't want to be writing out those long tables names now do you?

 

note: i need to check this, but in MySQL INNER JOIN and JOIN are performed as the same thing I believe.

[aschk]

What I am curious to know however if why you have 2 tables with cf_users in them? This sounds unnormalised to me, and I suggest that either your naming convention is wrong, or that you have to rethink your DB design. What is your DDL (data definition language) for the tables in question?

[Aureole]

No INNER JOIN, LEFT JOIN and RIGHT JOIN all do different things.. I'm not sure what they do exactly but I think INNER JOIN selects the stuff from both tables... not entirely sure.

[MasterACE14]

the query works if I change it to this:

SELECT `cf_users2`.`level_supplydrop` FROM `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1

 

Note I changed after the select "cf_users2" instead of just "cf_users" , is their a way I can make a code to check what table the column is in, and then put that table in the first part of the select??/

 

[jumpfroggy]

No INNER JOIN, LEFT JOIN and RIGHT JOIN all do different things.. I'm not sure what they do exactly but I think INNER JOIN selects the stuff from both tables... not entirely sure.

Probably a FAQ, but:

inner join - everything that's in both tables

left join - all rows from the left table, and their corresponding matches in the right

right join - all rows from the right table, and their corresponding matches in the left

 

// Finds rows that match in both tables

select table1.firstName, table2.lastName

from table1 inner join table2

where table1.personId = table2.personId

// Shows:

// 'john', 'doe'

// 'jane', 'doe'

// leaves out 'bob' cause he doesn't have a match in table2

// leaves out 'smith' because it doesn't have a first name

 

// Finds all first names, and prints lastnames if they exist or NULL otherwise

select table1.firstName, table2.lastName

from table1 left join table2

where table1.personId = table2.personId

// Shows:

// 'john', 'doe'

// 'jane', 'doe'

// 'bob', NULL

// leaves out 'smith' because it doesn't have a first name

 

// Finds all last names, and prints first names if they exist or NULL otherwise

select table1.firstName, table2.lastName

from table1 left join table2

where table1.personId = table2.personId

// Shows:

// 'john', 'doe'

// 'jane', 'doe'

// NULL, 'smith'

// leaves out 'bob' because he doesn't have a matching last name

 

[jumpfroggy]

Aureole's script didn't work  :-\

 

btherl's script did work, for all columns in `cf_users` , but doesn't work for any column in `cf_users2`.

 

here's the error I got:

Query:

SELECT `cf_users`.`level_supplydrop` from `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1

 

Error:

Unknown column 'cf_users.level_supplydrop' in 'field list'

 

by the way, here's the code I am using, note the $select variable is what I use for flexablity to select various columns.

 

<?php
$sql = "SELECT `cf_users`.`" . $select . "` from `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1";

You sure that level_supplydrop exists in cf_users?  Double-checked?  PM me the table columns and I'll take a look.

[MasterACE14]

the Column is definantly there, the problem is that the query is SELECTing from cf_users, and the column is in cf_users2. But if I change it to SELECT from cf_users2, then the query doesn't work for cf_users anymore, thats why I need a way to check if the column exists in either cf_users, or cf_users2 before running the query.

[jumpfroggy]

the Column is definantly there, the problem is that the query is SELECTing from cf_users, and the column is in cf_users2. But if I change it to SELECT from cf_users2, then the query doesn't work for cf_users anymore, thats why I need a way to check if the column exists in either cf_users, or cf_users2 before running the query.

At this point the query is not making sense, I hadn't looked closely enough:

SELECT `cf_users`.`level_supplydrop` from `cf_users` INNER JOIN `cf_users2` ON (`cf_users2`.`id` = `cf_users`.`id`) LIMIT 1

This is grabbing the first value "level_supplydrop" from "cf_users" where there is a matching "id" in cf_users2.  So given these tables:

cf_users:

idlevel_supplydrop

1100

287

350

cf_users2:

id

2

5

6

would spit out the results:

level_supplydrop:

id

87

Since that is the only row in cf_users that has a matching id in cf_users2.  That's probably not what you want, and there are many problems with this.  First, you're matching on "id" in both tables, but in this case these should not be auto_increment since if you delete a row from one table and not the other, it'll be all out of sync.  Second, you already mentioned that level_supplydrop is in cf_users2, not cf_users, so you'd need to pull it from that database.  Third, you seem to have two tables with almost the same name and a 1:1 relationship (ie. for every row in cf_users, there's a row in cf_users2); if so, they should probably be all in a single table.  And fourth, you're selecting something with LIMIT 1, but you don't specify an ORDER BY, so technically you could get any matching row, not just the "first" (since the DB may not spit out the rows in the order you're thinking).

 

The best thing would be to post the columns for each table, and then describe what you're trying to do.

[MasterACE14]

I've opened another thread, I am creating a function to check if the column I want in the SELECT is in table 1 or table 2. This is much easier way of doing it.

 

http://www.phpfreaks.com/forums/index.php/topic,170043.0.html