What is the problem with this query

Posted on 2008-10-27
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

Question by:jaisonshereen
LVL 39

Expert Comment

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

Accepted Solution

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

LVL 143

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 82

Assisted Solution

hielo earned 150 total points
ID: 22812858

Open in new window

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.
LVL 60

Expert Comment

ID: 22813009
Future editions of SQL Server (past 2000 I believe) do not allow outer joins through the non-ANSI =* syntax.
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! :-)
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...

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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