SQL Query : Joining if null

Question

Hi All,

Question.  I have one table that contains individuals and organisations.  To make things a little easier, I've created two different tables as views; one of individuals with relevant info (uniqueID, name, email), one of organisations (with uniqueID, org name).  I need to join to the organisation view from another table where an organisationID may exist, but not always.  How can I create a valid join between the view and the other table that will still give me the rows where the organisationID is null in the other table?

Hope that makes sense?! Appreciate any ideas.

Thanks :-)

Answer 1

Read the "Using Outer Joins" article http://msdn.microsoft.com/en-us/library/ms187518.aspx

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 2

Hi Tatyana,

Thanks for this, however I'm still a little confused (sorry, being a bit dim!).

- Do I still need a WHERE clause to join the two tables?
- I'm guessing it's ok to use left outer join on a view?

Thank you!

Sian

Answer 3

You don't have to have WHERE clause. You must have ON clause, it specifies how 2 tables are related to each other. 

Another example http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php (there are a lot more on the web).

 

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 4

Hi,

I don't understand why are joining the organisations table if you want to obtain the records that have organisationID is null?

why don't you just do it with normal select

select * from othertable where organisationID is null

if you mean that want to obtain the unmatched organisationId values with organisations table

All you have to do is a left or right join (depends on the direction)

 

for example :

select Tbl.* from tbl left join organisation

on organisation.uniqueId = Tbl.organisationId

where organisation.uniqueId is null

 

does it make sense now?