Duplicate column ?

[wkpride]

Trying to move the SQL query into my program and I'm not sure the idea is right... But this is what I've tried:

$sql = "CREATE TABLE keith " . "(sysid VARCHAR(6), polenum VARCHAR(7), scname VARCHAR(15))" .
"AS " .
"SELECT a.sysid, b.sysid, a.polenum, b.polenum, a.scname, b.scname " .
"FROM test AS a " .
"INNER JOIN test AS b " .
"ON ((a.polenum=b.polenum) AND (a.scname=b.scname) AND (a.sysid<>b.sysid)) " .
"WHERE a.scname='Morgantown' " .
"GROUP BY b.sysid ";

mysql_select_db("poledb",$dbh);
mysql_query($sql) or die(mysql_error());
echo "Finished";

But I'm getting a duplicate column error & I'm wondering if the CREATE TABLE - AS - SELECT - INNER JOIN is even possible?

I'm not sure of how to put the results of my INNER JOIN into a new table...
Suggestions?

Thanks! KP

[blang]

Still working on this I see, but with a twist.

You're getting a duplicate column error because you've chosen to list columns with the same name in the query and not use an alias as per my example. Furthermore, your CREATE TABLE is defining 3 columns, and your SELECT statement retrieves 6. I don't understand why you've changed the SELECT statement in that way, but whatever floats your boat.

Now, are you attempting to create a new table populated with the data from the SELECT, or a VIEW? A VIEW might make more sense because it's taking the complicated SELECT / JOIN statement and turning it into something much more "user friendly".

[wkpride]

blang,

You were right on the $. For some reason, I thought the a.sysid AS first was a table alias, but it was a column alias & figured I didn't need it. Now I understand.
It works & I can work on it until the next wall. Your help is REALLY appreciated.

You know, the funny thing is that I wanted to concentrate on PHP - but it seems like you gotta learn it all. CSS, SQL, XHTML...

Thanks again!

Keith

[blang]

Ok good deal. I'm really glad you were able to get it working.

Just FYI, an alias can reference a table name, column name, or the return of a function call, e.g.

SELECT 
 t1.some_long_column_name AS short_name 
, DATE_FORMAT(t1.date_field, '%m/%d/%Y') AS formatted_date 
FROM some_long_table_name AS t1 
INNER JOIN another_long_table_name AS t2 
 ON ( t1.some_field = t2.some_other_field ) 
; 

So again, your table names can be cut down to make the JOIN reference easier, the long column names can be trimmed down / made into something that makes sense, and sometimes long / unwieldy function returns can be given a name that can be easily referenced in the result set.