Help with MySQL Query

[ElegantElephant]

I am having trouble with a certain query. The problem is that I am using an INNER JOIN with two tables that both have a column called "id". I want the query to return ALL the column names but in addition I also want table1.id to be returned as id1 and table2.id to be returned as id2 like this:

$query = "SELECT table1.id AS id2, table2.id AS id2 WHERE column = 'blah'";

However, how do I combine that query with a "SELECT *" query to return all the other columns as they are but with the id columns as defined above too? - without having to list out all the columns individually?

[jmo]

It sounds like you might need to try a union. See if this gets what you need:

$query = "SELECT table1.id AS id2 WHERE column = 'blah'
UNION
SELECT table2.id AS id2 WHERE column = 'blah'";

You can check out the mysql documentation for UNION for more info.

Which fields are joined - not your 'id' fields obviously as there would not be any point in retrieving identical data.

EXAMPLE:

table1
id (primary key)
fieldA
fieldB

table2
id (primary key)
field1 (this is the foreign key of 'id' in table1)
field2

To return all fields (but only id in table1 - not field1 in table2):

SELECT t1.id AS id1,t1.fieldA,t1.fieldB,t2.id AS id2,t2.field2 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.field1 WHERE t1.fieldA = 'blah'

I've used a filter on fieldA in table1. Haven't checked it, but think it's OK.

Your particular problem was that you tried to name both 'id' fields with the same alias (id2).

BTW: UNION will only work for tables with identical datatypes for chosen 'common' fields. This will 'stack up' or combine the results as opposed to join them in a relational way.

[jmo]

BTW: UNION will only work for tables with identical datatypes for chosen 'common' fields. This will 'stack up' or combine the results as opposed to join them in a relational way.

Good call!