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.

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.

commented: Thank you for helping me out with this. I was stuck for quite a while on this little problem, but you sure helped me out. +1

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.

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.

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.