Error in query, two select statements with left join?

Question

I have two select statements:

A: select a.1,a.2,a.3 from table1 a

B: select b.1,b.2,b.3 from table1 b

Now I join these two statements?

I tried in the below way and got error:

select * 
(select a.1,a.2,a.3 from table1 a) aa
left join 
(select b.1,b.2,b.3 from table1 b) bb
aa.a.1 = bb.b.1;

Please provide schema for both tables, as well as what you're trying to get from both. This seems quite unclear with all these A's and B's — , Oct 27 '14 at 09:09
What error do you get? (And why do you let us guess at all instead of just telling us right away?) — Thorsten Kettner, Oct 27 '14 at 09:57

score 0 · Answer 1 · 2014-10-27T09:34:36.010

0

Within your Left Join, you need to include the ON/WHERE clause:

select * 
(select a.1,a.2,a.3 from table1 a) aa
left join 
(select b.1,b.2,b.3 from table1 b) bb
aa.a.1 = bb.b.1,

should be in the format:

   SELECT *
   (SELECT a.1, a.2, a.3 FROM table1 a) aa
   LEFT JOIN
   (SELECT b.1,b.2,b.3 FROM table2 b) bb
   ON a.1 = b.1
   WHERE ...

For more clarification, please see this image:

enter image description here

As it currently stands, it's quite hard to distinguish what exactly your requirements are in terms of what you want the query to return, but I op this image will visually display the syntax for each of the joins.

edited Oct 27 '14 at 09:34

answered Oct 27 '14 at 09:13

aa.a.1 = bb.b.1 <= is it correct to call like this – CHUN LAM Oct 27 '14 at 09:21
but left join in two select statment what is the best format of that – CHUN LAM Oct 27 '14 at 09:42
WHERE ... <----should be what? – CHUN LAM Oct 27 '14 at 09:44
select * (select a.key,a.2,a.3 from table1 a) aa left join (select b.key,b.2,b.3 from table1 b) bb on a.key = b.key where (<----what should put in?) – CHUN LAM Oct 27 '14 at 09:47
top right or middle right option (both are right joins) but the `WHERE` clause could be a.1 = b.1 (this would give top right option) – Oct 27 '14 at 09:49
Thanks, but then what is the difference on "ON a.1 = b.1" AND ""where a.1 = b.1 – CHUN LAM Oct 27 '14 at 09:51
please see [this question](http://stackoverflow.com/questions/2722795/in-sql-mysql-what-is-the-difference-between-on-and-where-in-a-join-statem) to answer that. – Oct 27 '14 at 10:11

Thorsten Kettner · Answer 2 · 2014-10-27T11:40:33.667

Numbers (a.1, a.2, i.e. columns 1 and 2 for table alias a) are usually not valid column names. Are the columns really named thus? Then you'd need something to indicate that these are column names. Depending on the dbms that could be `a.1` or "a.1" or [a.1]. Or use different names, such as num1, num2, num3, or one, two, three, etc.

EDIT: You are also missing the word ON before your criteria. And aa.a.1 is invalid, for your table alias is now aa and the column name is still "1" and the table alias a is no longer known. So it must be a."1" instead. Moreover you are missing the keyword FROM for your first derived table.

select * 
from      (select a."1", a."2", a."3" from table1 a) aa
left join (select b."1", b."2", b."3" from table1 b) bb ON aa."1" = bb."1";

Error in query, two select statements with left join?

2 Answers2