Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
my query is to join 3 tables but it throws error query is
 

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 29-Dec-12 0:17am
Comments
__TR__ at 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
chander_rani at 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__ at 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).
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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:
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)
  Permalink  
v2
Comments
__TR__ at 29-Dec-12 5:50am
   
My 5!
Mika Wendelius at 29-Dec-12 6:02am
   
Thank you :D
Espen Harlinn at 29-Dec-12 7:38am
   
5'ed!
Mika Wendelius at 29-Dec-12 7:54am
   
Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
chander_rani at 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
chander_rani at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

for right answer u take
p.projectid=c.projectid
 
instead of
s.projectid = c.projectid
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 390
1 Sergey Alexandrovich Kryukov 329
2 BillWoodruff 210
3 Afzaal Ahmad Zeeshan 204
4 CPallini 185
0 OriginalGriff 5,515
1 DamithSL 4,451
2 Maciej Los 3,902
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100