SQL 2005 Left join

Posted on 2008-06-24
Last Modified: 2010-05-18
I have a query that is similar to the one below.  I am trying to get all the records from tableA but the where clause is removing the records. I want the records that do not exists in the table b to be null.
select max(cola)
from tableA
left join tableB on tableB.colB = tableA.colB
where tableA.colA<table.colB
group by tableA.colA

Open in new window

Question by:yanci1179
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 21855455
select max(cola)
from tableA
left join tableB on tableB.colB = tableA.colB AND tableA.colA<table.colB
group by tableA.colA

Open in new window

LVL 43

Expert Comment

ID: 21855464
Hello yanci1179,

1:   select max(cola)
2:   from tableA
3:   left join tableB on tableB.colB = tableA.colB
4:   where tableA.colA<tableB.colB Or tableB.colB Is Null
5:   group by tableA.colA



Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 37
SQL Query 2 61
Why do I get extra rows when I do inner join? 12 39
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 66
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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