join in 3 tables throws Incorrect syntax near the keyword 'WHERE' error

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

my query is to join 3 tables but it throws error query is

SQL

select distinct p.projectid,p.projectname,p.location,p.bedrooms,p.city,p.overview,p.price,s.builduparea,s.bedrooms,s.bathrooms,s.propertyonfloor,s.price,s.units,c.speci,c.amenities
from plisting p
join indi s JOIN Sheet1$ c  on p.projectid=s.projectid AND s.projectid = c.projectid
WHERE s.projectid='PL2023002'
order by p.projectname,s.projectname

but it throws error
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHERE'.

pls help me solve this

Posted 28-Dec-12 23:17pm

chander_rani

Add a Solution

Comments

__TR__ 29-Dec-12 5:19am

Try
select distinct p.projectid,p.projectname,p.location,p.bedrooms,p.city,p.overview,p.price,s.builduparea,s.bedrooms,s.bathrooms,s.propertyonfloor,s.price,s.units,c.speci,c.amenities
from plisting p
join indi s on p.projectid=s.projectid
JOIN Sheet1$ c on s.projectid = c.projectid
WHERE s.projectid='PL2023002'
order by p.projectname,s.projectname

[no name] 29-Dec-12 5:31am

it works till here
select distinct p.projectid,p.projectname,p.location,p.bedrooms,p.city,p.overview,p.price,s.builduparea,s.bedrooms,s.bathrooms,s.propertyonfloor,s.price,s.units,c.speci,c.amenities
from plisting p
join indi s on p.projectid=s.projectid
JOIN Sheet1$ c on s.projectid = c.projectid
WHERE s.projectid='PL2023002'

but if i added order by it throws error
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

what should i use

__TR__ 29-Dec-12 5:46am

As the error says you need to include the column s.projectname in your select statement or else get rid of the distinct key word (which may not be suitable for your requirement).

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ankur789 · Answer 1 · 2012-12-28T23:48:00

Solution 4

for right answer u take
p.projectid=c.projectid

instead of
s.projectid = c.projectid

Posted 28-Dec-12 23:48pm

ankur789

Wendelius · Answer 2 · 2012-12-28T23:48:00

Looks like you have missed the joining columns for the first two tables (ON clause)

Add proper columns to the join so something like the following:

SQL

select distinct  
   p.projectid,
   p.projectname,
   p.location,
   p.bedrooms,
   p.city,
   p.overview,
   p.price,
   s.builduparea,
   s.bedrooms,
   s.bathrooms,
   s.propertyonfloor,
   s.price,
   s.units,
   c.speci,
   c.amenities,
   s.projectname
from      plisting p
     join indi     s on p.projectid=s.projectid
     JOIN Sheet1$  c on s.projectid = c.projectid
WHERE s.projectid='PL2023002'
order by p.projectname, 
         s.projectname

Also in order for the DISTINCT to work, add s.ProjectName to the select list (added as the last column)

ankur789 · Answer 3 · 2012-12-28T23:44:00

SQL

select distinct p.projectid,s.projectname,p.projectname,p.location,p.bedrooms,p.city,p.overview,p.price,s.builduparea,s.bedrooms,s.bathrooms,s.propertyonfloor,s.price,s.units,c.speci,c.amenities
from plisting p
join indi s JOIN Sheet1$ c  on p.projectid=s.projectid AND s.projectid = c.projectid
WHERE s.projectid='PL2023002'
order by p.projectname,s.projectname

now use it

ankur789 · Answer 4 · 2012-12-28T23:25:00

Solution 1

SQL

select distinct p.projectid,p.projectname,p.location,p.bedrooms,p.city,p.overview,p.price,s.builduparea,s.bedrooms,s.bathrooms,s.propertyonfloor,s.price,s.units,c.speci,c.amenities
from plisting p
join indi s on p.projectid=s.projectid 
join Sheet1$ c on s.projectid = c.projectid
 WHERE s.projectid='PL2023002'
order by p.projectname,s.projectname

Please Use It and answer me is it works or not

Posted 28-Dec-12 23:25pm

ankur789

Comments

[no name] 29-Dec-12 5:32am

it works till here
select distinct p.projectid,p.projectname,p.location,p.bedrooms,p.city,p.overview,p.price,s.builduparea,s.bedrooms,s.bathrooms,s.propertyonfloor,s.price,s.units,c.speci,c.amenities
from plisting p
join indi s on p.projectid=s.projectid
JOIN Sheet1$ c on s.projectid = c.projectid
WHERE s.projectid='PL2023002'

but if i added order by it throws error
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

what should i use

[no name] 29-Dec-12 5:44am

i want to know another thing is also result shows a 60 records of all 3 tables but in sheet1 there is 15 records of projectid='PL2023002' and in another indi table there is 4 records and in 3rd table plisting there is only 1 recored related to projectid='PL2023002'

i want to know why it shows a 60 records after running query of 3 tables