0

I am trying to join three tables in SQL. I am using the following query but it is not working

select *
from char_level as c1 right join (SELECT distinct character_id as fid, target_character_dbid as tid  FROM house 
where reason='set_house_access' or reason='remove_house_access' and character_id is not null and target_character_dbid is not null)as vd on c1.character_id==vd.fid left join char_level as c2 on c2.character_id==vd.tid

can anyone help?

ayush singhal
  • 1,879
  • 2
  • 18
  • 33
  • the query is not executing: error ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 38 Column: 17 – ayush singhal May 13 '13 at 20:39
  • I expect the result should be a single table containing c1.account_id,vd.fid,vd.tid,c2.accoutn_id – ayush singhal May 13 '13 at 20:40
  • Oracle has an aversion for the `AS` keyword in table aliases. Remove it (besides the other corrections, in answers.) `from char_level as c1` should be `from char_level c1` and the `)as vd` should become: `) vd` – ypercubeᵀᴹ May 13 '13 at 21:08

1 Answers1

2

Add a semicolon and use a single equal sign.

select *
from char_level c1 
right join 
(SELECT distinct character_id as fid, target_character_dbid as tid  
FROM house 
where (reason = 'set_house_access' 
or reason = 'remove_house_access') 
and character_id is not null 
and target_character_dbid is not null) vd 
on c1.character_id = vd.fid 
left join char_level c2 
on c2.character_id = vd.tid;
criticalfix
  • 2,870
  • 1
  • 19
  • 32
  • Perhaps you have two statements concatenated into a single statement without a semicolon inbetween. What exactly do you have at Line: 38 Column: 17? You need semicolons after every statement in Oracle or you get this error message, [see this example here](http://stackoverflow.com/questions/72151/ora-00933-sql-command-not-properly-ended). I hope this helps! – criticalfix May 13 '13 at 20:54
  • I added parenthesis around (reason = 'set_house_access' or reason = 'remove_house_access'), which I think is what you mean. I still think you did not run this query by itself, but instead this query stuck together with some other statement that lacks a semicolon. – criticalfix May 13 '13 at 21:08
  • Following @ypercube, I have removed the "as" from the table alias. – criticalfix May 13 '13 at 21:10
  • i think you are right there is some prbolem using 'as', while typing it is showing me error on as for char_level as c1 – ayush singhal May 13 '13 at 21:16
  • I have updated the answer to remove the two other "as" keywords in the table aliases. The table aliases are still there! They just don't make use of the "as" keyword. Ambiguities between c1, c2, and vd should still be getting resolved correctly. – criticalfix May 13 '13 at 21:20