CODEWITHSUNDEEP
sqlderby
Suhail Gupta
Suhail Gupta

Reputation: 23216

error when joining 2 tables

Query :

select i.Name,ri.Country,ri.State,ri.City 
from Information as i
join ResidenceInformation as ri
order by Name

The error that i get is : 

Error code -1, SQL state 42X01: Syntax error: Encountered "order" at line 4, column 5.
Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

Why am i getting an error ?

Upvotes: 1

Views: 218

Answers (3)

gvt
gvt

Reputation: 2224

I think you may have forgot to tell the join clause which columns to join against. You need to tell the database how these 2 tables connect to each other. Something like ON i.id = ri.InformationId

select i.Name,ri.Country,ri.State,ri.City 
from Information as i
join ResidenceInformation as ri ON i.id = ri.InformationId
order by i.Name

Also, you may need the table alias in the order by clause, which I've added as well.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

The error is because you forgot to specify JOIN criteria, like this:

  SELECT i.Name, ri.Country, ri.State, ri.City 
    FROM Information as i
    JOIN ResidenceInformation as ri ON ri.column = i.column
ORDER BY Name

You need to replace column with the names of the appropriate columns that link the tables correctly for the output you need.

You should also specify the table alias in your ORDER BY, to protect against an ambiguous column reference error.

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

You get an error because your syntax is wrong: after join there needs to be on, like this:

select i.Name,ri.Country,ri.State,ri.City 
from Information as i
join ResidenceInformation as ri
  on ri.info_id=i.id -- <<< Added a join condition
order by Name

SQL needs to know how to "link up" the rows of the table that you are joining to the row(s) of the other table(s) in the query. I am assuming that ResidenceInformation has a foreign key into Information called info_id.

If the Name is present in both ResidenceInformation and Information, you need to prefix it with the table name or an alias. In fact, it's a good idea to do it anyway for added clarity.

Upvotes: 1

Related Questions