0

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?

3
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by jmo
0

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.

0

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.

Edited by diafol: n/a

0

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!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.