Reputation: 94

sql outer join shows the error in mysql workbench

I have two tables country and state:

country
-----------
cid
countrycode
countryname

state
-------
sid
cid (fk)
statecode
statename

I am writing a join as:

SELECT s.sid, 
       s.statename, 
       c.countryname 
FROM   state s, 
       country c 
WHERE  s.cid = (+) c.cid 
       AND c.id = 1

The above query gives a syntax error. Does MySQL not accept '+' symbol?

Upvotes: 1

Answers (4)

Reputation: 1872

this will help you.

 SELECT s.sid, 
       s.statename, 
       c.countryname 
FROM   country c 
LEFT JOIN state s on (s.cid = c.cid )
where c.cid = 1;

Upvotes: 0

Reputation: 3213

Remove plus sign and . symbol at end of your query...

Select s.sid,s.statename,c.countryName from state s,
left join country c on s.cid = c.cid where c.id=1

Upvotes: 0

Reputation: 34055

No, MySQL does not accept the + symbol. Aside from a few exceptions, MySQL uses ANSI standard JOIN syntax:

SELECT s.sid, 
       s.statename, 
       c.countryname 
FROM   state s 
       LEFT JOIN country c 
              ON c.id = s.cid 
WHERE  c.id = 1

Upvotes: 3

Reputation: 13465

Try this::

select 
s.sid,
s.statename,
c.countryName 
from country c 
LEFT JOIN state s on  s.cid = c.cid 
WHERE c.id=1

Upvotes: 0