Problem while joining table columns containing null values.

Question

0.00/5 (No votes)

See more:

Hi
In my sql query, I have to join more than 6 tables to select a record. In this some of the tables contains null values. Because of this null values i couldn't retrieve any records.

For ex,

My query looks like this

SQL

Select columns from table1 join table2 on tabl1.c1 = table 2.c join table3 on table1.c2 = table3.c join table4 on table1.c3 = table4.c join table5 on table1.c4=table5.c  ........... where id=value

like this.

Here
----> In this line join table4 on table1.c3 = table4.c

table1.c3 is null... So I am unable to select records.

My question is If that column is null then just return null values with that records.

How can i solve it?

Posted 12-Aug-12 19:22pm

Velkumar Kannan

Updated 12-Aug-12 19:40pm

Wendelius

v2

Add a Solution

Comments

__TR__ 13-Aug-12 1:57am

You can use ISNULL() function for assigning a default value for records containing NULL.
Eg: join table4 on ISNULL(table1.c3,'') = ISNULL(table4.c,'')

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

AshishChaudha · Answer 1 · 2012-08-12T19:36:00

Solution 1

Use Left outer join for table4 on table1.c3 = table4.c, then all the records from table table1 will be retrieved.

Thanks
Ashish

Posted 12-Aug-12 19:36pm

AshishChaudha

Comments

Velkumar Kannan 13-Aug-12 1:46am

no need to select all records. just select a record which have this ID value.

AshishChaudha 13-Aug-12 1:57am

Then your query is giving right result.

Wendelius · Answer 2 · 2012-08-12T19:44:00

Solution 2

You need to use an outer join in this kind of situation. Outer join returns rows from a table even if it has no corresponding rows in the joined table.

The outer join can be a left outer join or a right outer join depending on the order you list the tables.

Have a look at all of the join examples in SQL Joins[^], especially SQL LEFT JOIN[^] and SQL RIGHT JOIN[^]

Posted 12-Aug-12 19:44pm

Wendelius

Comments

Velkumar Kannan 13-Aug-12 1:56am

But one of the column contains NULL values. So there is a problem while comparing