none
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 :-)

    Friday, January 27, 2012 1:34 AM

All replies

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


    Tatyana Yakushev [PredixionSoftware.com]
    Friday, January 27, 2012 1:51 AM
  • 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

    Friday, January 27, 2012 2:04 AM
  • 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]
    Friday, January 27, 2012 4:52 AM
  • 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?

     

    Wednesday, February 01, 2012 1:37 PM