Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Query : Joining if null

Proposed Answer SQL Query : Joining if null

  • Friday, January 27, 2012 1:34 AM
     
     
    Sign In to Vote
    0

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

     

All Replies

  • Friday, January 27, 2012 1:51 AM
    Answerer
     
     
    Sign In to Vote
    0

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

    Tatyana Yakushev [PredixionSoftware.com]
     
  • Friday, January 27, 2012 2:04 AM
     
     
    Sign In to Vote
    0

    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 4:52 AM
    Answerer
     
     Proposed Answer
    Sign In to Vote
    0

    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]
     
  • Wednesday, February 01, 2012 1:37 PM
     
     
    Sign In to Vote
    0

    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?