Solved

What is the problem with this query

Posted on 2008-10-27
8
177 Views
Last Modified: 2012-05-05
i am getting syntax error near "where" !!
select table1.*,[table2].*,table3.* from (table1 where table1.coulumn1='value' LEFT JOIN table1.column1=[table2].column1) LEFT JOIN table3 ON table1.column1=table3.column1)

Open in new window

0
Comment
Question by:jaisonshereen
8 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22812811
There is something wrong with the parantheses. Try removing them.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 200 total points
ID: 22812824
parentheses did not belong
WHERE needs to follow the FROM clause (which includes any JOIN's)

select table1.*,[table2].*,table3.* from 
table1 LEFT JOIN table1.column1=[table2].column1) LEFT JOIN table3 ON table1.column1=table3.column1
where table1.coulumn1='value' 

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 22812829
this will work better:
select table1.*,[table2].*,table3.* 
from table1 
LEFT JOIN table3 ON table1.column1=table3.column1
LEFT JOIN table2 ON table1.column1=[table2].column1
where table1.coulumn1='value'

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 82

Assisted Solution

by:hielo
hielo earned 150 total points
ID: 22812858
try:
SELECT 
	table1.*,table2.*,table3.* 
FROM 
	table1,table2,table3
WHERE
	table1.column1=table2.column1
AND
	table1.column1=table3.column1
AND
	table1.column1='value' 

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22812962
I agree with Angel Eyes and Daniel.  Nothing personal, Hielo.

I personally like to specify the join types in the FROM clause where I am doing the joins so I don't spend time troubleshooting why the data is coming back funny because the system decides to do an inner join here instead of a left join which have distinctly different purposes especially when dealling with 3 tables.

My humble opinion anyway.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22813009
Future editions of SQL Server (past 2000 I believe) do not allow outer joins through the non-ANSI =* syntax.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22813414
>>this will work better:

Yes, it will.  I failed to fix the JOIN syntax.  Working too fast ... trying to beat you guys to the post! :-)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22813476
We knew what you meant. LOL and I know -- posted one yesterday without the FROM clause but showed the right select, where, and group by which answered question but still...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question