- My left table V1 will sometimes have a value of NULL for V1.prog_log_key
- I am trying to join this field, to the right table H1
FROM dbo.V_HEAD_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID
- I want to keep the null values in the left table V1, if there is no match in H1
- the old join syntax, V1.prg_lob_key *= H1.rmap_id kept my null values
- the new join, (Left Outer Join) leaves out the NULLS
Know of any tricks for keeping the NULL values? I'm using SQL Server 2005
SELECT DISTINCT
V1.RYEAR, V1.RCAT_TYPE AS v1_rcat,
V1.RM_ID AS v1_rm_id, V1.RMAP_ID, V1.RMAP_NAME AS V1_rmap_name,
V1.IMT_ID, V1.IMT_KEY, V1.CDESC2, V1.MDESC2, V1.IDESC2 AS V1_idesc2,
V1.PROGRAM AS V1_program, V1.INS_YEAR AS V1_ins_year,
V1.S_YYYY, V1.S_MM, V1.E_YYYY, V1.E_MM,
V1.CMT_KEY, V1.prg_lob_key, H1.RCAT_TYPE, H1.RM_ID, H1.RMAP_NAME
FROM dbo.V_HEAD_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID
WHERE (V1.RYEAR = '2007') AND
(V1.RCAT_TYPE <> 'L') AND
(V1.IMT_STATUS = 'E' OR V1.IMT_STATUS = 'A') AND
(H1.RCAT_TYPE = 'L')
2  6611  
- My left table V1 will sometimes have a value of NULL for V1.prog_log_key
- I am trying to join this field, to the right table H1
FROM dbo.V_HEAD_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID
- I want to keep the null values in the left table V1, if there is no match in H1
- the old join syntax, V1.prg_lob_key *= H1.rmap_id kept my null values
- the new join, (Left Outer Join) leaves out the NULLS
Know of any tricks for keeping the NULL values? I'm using SQL Server 2005
SELECT DISTINCT
V1.RYEAR, V1.RCAT_TYPE AS v1_rcat,
V1.RM_ID AS v1_rm_id, V1.RMAP_ID, V1.RMAP_NAME AS V1_rmap_name,
V1.IMT_ID, V1.IMT_KEY, V1.CDESC2, V1.MDESC2, V1.IDESC2 AS V1_idesc2,
V1.PROGRAM AS V1_program, V1.INS_YEAR AS V1_ins_year,
V1.S_YYYY, V1.S_MM, V1.E_YYYY, V1.E_MM,
V1.CMT_KEY, V1.prg_lob_key, H1.RCAT_TYPE, H1.RM_ID, H1.RMAP_NAME
FROM dbo.V_HEAD_DETAIL_Program AS V1
LEFT OUTER JOIN
dbo.T_ROAD_HEAD AS H1 ON V1.prg_lob_key = H1.RMAP_ID
WHERE (V1.RYEAR = '2007') AND
(V1.RCAT_TYPE <> 'L') AND
(V1.IMT_STATUS = 'E' OR V1.IMT_STATUS = 'A') AND
(H1.RCAT_TYPE = 'L')
Funny, I've just tried and it works fine for me in a made-up example. Can it be that your WHERE clause (and/or the data set) also changed since you tried with the old syntax? (Perhaps parenthesis were at other places?) You can try with an empty WHERE clause and sort out if you have the rows...
For some reason OUTER JOIN consideres fields having both NULL values as not equal (at least in MSSQL 2000). I use this as workaround: -
FROM dbo.V_HEAD_DETAIL_Program AS V1
-
LEFT OUTER JOIN
-
dbo.T_ROAD_HEAD AS H1
-
ON (V1.prg_lob_key = H1.RMAP_ID or V1.prg_lob_key is null
-
and H1.RMAP_ID is null)
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JBBHF |
last post by:
Hi i'm working on a web project, and i would like to make my oracle
query work in mysql.
select match.numero "nummatch",
to_char(match.datematch, 'yyyy-MM-dd') "datematch",
p1.numjoueur "j1",...
|
by: Neil Ginsberg |
last post by:
We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
linked tables. I recently created a new set of tables for the app, and users
are complaining that unsaved data is being...
|
by: Ian Boyd |
last post by:
i know nothing about DB2, but i'm sure this must be possible.
i'm trying to get a client to create a view (which it turns out is called a
"Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
|
by: lelandhuang |
last post by:
I am developing reporting service and using lots of 'LEFT OUTER JOIN',
I am worried about the performance and want to use some subquery to
improve
the performance.
Could I do that like below,
...
|
by: wgblackmon |
last post by:
I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the...
|
by: santiago |
last post by:
Hello. I'm having troubles with a query that (should) return all the
records between two dates. The date field is a datetime type. The db is
SQL Server 2000. When I try this
SELECT ...
|
by: BillCo |
last post by:
I'm new to SQL Server, so if I'm doing anything stupid don't be
mean :)
I have a procedure that I use to return data based on optional
parameters. It works fine, except when the underlying data...
|
by: paulquinlan100 |
last post by:
Hi
I have the following query:
SELECT qryBlackbook.*
FROM qryBlackbook inner JOIN qryLatestMeetingDate ON
(qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND...
|
by: Hamayun Khan |
last post by:
Hi All
I am using the following query to select records from two tables.
Select JobTitle,JobDesc,Scraped,logoimage,JobPostID,SchoolID,web,MemType,InstitutionName,PayScale,LEA,Contract as...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
