Referencing aliased table in a subquery

[whitedragon101]

 

I have an aliased table called table1 and a real table in the database called table2.

table1 has been produced from many joins, subqueries and unions.

I want to produce a result-set that adds all the records from table1 to the records of table2 where the given row_id is not already present in table2.

eg

 

table2 (real table in database)

row_id

1

2

3

4

5

 

table1 (aliases table created with queries)

row_id | data_field1 | data_field2

1______data1______data2

2______data3______data4

 

desired result set

row_id | data_field1 | data_field2

1______data1______data2

2______data3______data4

3

4

5

 

I have tried the following where I attempt to create a table3 which only selects those records from table1 which are not already in table2 and then join the two aliased tables.  However I get the error:

 

Unknown column 'table1.row_id' in 'where clause'

 

MySQL version -  5.1.47-community-log

SELECT * FROM(
....lots of subqueries, unions etc.....

) AS table1

RIGHT JOIN (SELECT table2.* FROM table2 WHERE table1.row_id != table2.row_id)AS table3
ON table1.row_id = table3.row_id

 

 

I have also tried to just create a view of table1 however I get the error:

#1349 - View's SELECT contains a subquery in the FROM clause

It seems from a quick google mysql can not create a view from complex queries

 

 

[fenway]

Why do you have a subquery in the right join?

[whitedragon101]

Why do you have a subquery in the right join?

 

If I do a right join and the record from table2 is already in table1 it will join with it anyway giving duplicate fields for all the columns. ie there will be two columns called row_id in the result set.

 

So I was trying to use a subquery that would only give rows from table2 that are not in table1.  That way when I join them there will be no overlap.

[fenway]

I still don't see why you just can't join it directly and combine the ON conditions.

[whitedragon101]

I still don't see why you just can't join it directly and combine the ON conditions.

 

 

Could you show me a code block of what you mean please.

 

Many thanks

 

John

[fenway]

Well, maybe I don't understand what you're trying to achieve.