Null value in view
 

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:

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.

Re: Null value in view
 

Use an outer join.

e.g

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.

Re: Null value in view
 

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.

Re: Null value in view
 

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.

Re: Null value in view
 

you can create a UNIQUE key constraint on this table which can allow you a null valle in your table.