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.