Null value in view

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 361,543 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,996 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 656 | Replies: 4 | Solved

•

Join Date: Apr 2008

Location: Belgium

Posts: 47

Reputation: Jens is an unknown quantity at this point

Rep Power: 1

Solved Threads: 5

Jens

Offline

Light Poster

Null value in view

Apr 11th, 2008

Hello everyone

I am building on a little project in .net, but I have ran into some problems on database - level.

I am creating a view with data from a couple of tables. It all revolves around my main table
(let's call it 'main' for now). In 'Main' there is a link to an other table by ID. The linked table is called 'Contacts'. Not every 'Main' has a 'Contact', so this field can have null values.
But, of course a contact has a name. So in the table 'Contact' my contactname - field can NOT have null values.

Now that I explained that, it's time to go to the main problem.

I am making a view from 'main'. But instead of the contactID (as it is declared in the 'main' - table) I want a direct link to the contactname.
The code that I used for this is as following:

Help with Code Tags

(Toggle Plain Text)

SELECT 
Main.ID,
Main.Name,
Contact.Name AS Contactname,
FROM
Main,
Contact
WHERE
Contact.ID = Main.ContactID

The problem here is that my view now can't show the main rows where my contactID is null, since this field in the view has a 'not - null' parameter attached to it.
(Inherited from the contact - table).

Now my question is: How can I show all the records from my main table while still maintaining my contactname. Should I change the table contactname to enable nulls, or is there a more elegant way of dealing with this?

If anyone needs additional information, feel free to ask.

•

Join Date: Mar 2008

Posts: 25

Reputation: blater is an unknown quantity at this point

Rep Power: 1

Solved Threads: 7

blater

Offline

Light Poster

Re: Null value in view

Apr 11th, 2008

Use an outer join.

e.g

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT
  Main.ID,
  Main.Name,
  Contact.Name AS Contactname
FROM Main LEFT JOIN Contact ON Contact.ID = Main.ContactID
SELECT
  Main.ID,
  Main.Name,
  Contact.Name AS Contactname
FROM Main Left Join Contact on Contact.ID = Main.ContactID

This will include all rows in the Main table whether they match to a Contact row or not.

Last edited by peter_budo : Apr 11th, 2008 at 8:51 am. Reason: Keep It Organized - please use [code] tags

http://www.inverse2.com

•

Join Date: Apr 2008

Location: Belgium

Posts: 47

Reputation: Jens is an unknown quantity at this point

Rep Power: 1

Solved Threads: 5

Jens

Offline

Light Poster

Re: Null value in view

Apr 11th, 2008

Ah thanks. I will try that.
I tried the join - statement before, but it gave me double rows. I will keep you posted if it works though.

Thanks for the fast reply.

•

Join Date: Apr 2008

Location: Belgium

Posts: 47

Reputation: Jens is an unknown quantity at this point

Rep Power: 1

Solved Threads: 5

Jens

Offline

Light Poster

Re: Null value in view

Apr 11th, 2008

Oh god, I just got it to work. Really thanks! I'll be marking this as solved soon.

I know the question sounded a bit as coming from an uninformed person, but truth is that I did try joins. As I tried your statement again, it still wouldn't run the first time.
The problem was that I used multiple left joins (since I have a lot of tables) and I used a ',' between every 'LEFT JOIN' statement. This ',' was not needed and caused the statement to error.

Thank you for your help, it is really appreciated.

•

Join Date: Feb 2008

Posts: 6

Reputation: arupa is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

arupa

Offline

Newbie Poster

Re: Null value in view

10 Days Ago

•

Originally Posted by Jens

Hello everyone

I am building on a little project in .net, but I have ran into some problems on database - level.

I am creating a view with data from a couple of tables. It all revolves around my main table
(let's call it 'main' for now). In 'Main' there is a link to an other table by ID. The linked table is called 'Contacts'. Not every 'Main' has a 'Contact', so this field can have null values.
But, of course a contact has a name. So in the table 'Contact' my contactname - field can NOT have null values.

Now that I explained that, it's time to go to the main problem.

I am making a view from 'main'. But instead of the contactID (as it is declared in the 'main' - table) I want a direct link to the contactname.
The code that I used for this is as following:
Help with Code Tags

(Toggle Plain Text)
SELECT 
Main.ID,
Main.Name,
Contact.Name AS Contactname,
FROM
Main,
Contact
WHERE
Contact.ID = Main.ContactID
SELECT Main.ID, Main.Name, Contact.Name AS Contactname, FROM Main, Contact WHERE Contact.ID = Main.ContactID
The problem here is that my view now can't show the main rows where my contactID is null, since this field in the view has a 'not - null' parameter attached to it.
(Inherited from the contact - table).

Now my question is: How can I show all the records from my main table while still maintaining my contactname. Should I change the table contactname to enable nulls, or is there a more elegant way of dealing with this?

If anyone needs additional information, feel free to ask.