none
How to compare two fields from two different tables?

    Question

  • I'm trying to create a SELECT store procedure where I compare the classNote.city to city.city. If it equals, then put a blank for the classNote.state column?

    Your help is much appreciated.

    Tuesday, May 04, 2010 3:37 PM

Answers

  • If that's the case, then you're probably looking for one of these variations:

    select classNotes.fields, case when exists (select 1 from APCity where APCity.City = classNotes.City) then '' else ClassNotes.State as State end from ClassNotes

    or alternatively (assuming that APCity doesn't contain duplicate records per city)

    select classNotes.fields, case when APCity.City IS NULL then classNotes.State else '' end as State from ClassNotes LEFT JOIN

    ApCity on ClassNotes.City = APCity.City

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Charlie2 Tuesday, May 04, 2010 4:16 PM
    Tuesday, May 04, 2010 4:02 PM
  • To fix errors:

    WHERE (([classNotes].[approval] = 'True') AND ([classNotes].[archived] = 'False') AND ([classNotes].[cateID] = 2))

    [TableName].[FieldName]

    (or you may not use [ ] at all for your case).


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Charlie2 Tuesday, May 04, 2010 4:55 PM
    Tuesday, May 04, 2010 4:49 PM

All replies

  • How can you join these two tables?

    Assuming there is an ID field which is the field used to join, then

    select N.City, C.City, N.ID, case when N.City = C.City then '' else N.State end as NewState from ClassNote N inner join City C on N.ID = C.ID


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 04, 2010 3:44 PM
  • You can use CASE expression:

     

    SELECT CASE WHEN N.city = C.city THEN '' ELSE N.state END AS state

    FROM classNote AS N

    JOIN city AS C

      ON <join predicates>;


    Plamen Ratchev
    Tuesday, May 04, 2010 3:44 PM
  • Hi Charlie,

    Try following query:

    Update ClassNote Set ClassNote.State = ''
    From	
    	ClassNote.City ClassNote
    Inner Join
    	City.City City
    	on ClassNote.City = City.City
    Where
    	ClassNote.State != ''
    

    Failure in Life is failure to try...
    Tuesday, May 04, 2010 3:47 PM
  • I'll give those suggestions a try.

    I'm trying to format some texts. I have a table called classNotes that has all the user's information plus their city and state. I also have an AP city. In AP style guide, some US cities does not require the State name to be displayed after the city. So, I have a table of the AP city that does not require State name and then I'm trying to compare those city with the classNote.city field. If it equals then that means the user's city is one of the AP cities that does not require the State name after. Therefore, I need to put a blank in the user's State during SELECT for displaying.

    Tuesday, May 04, 2010 3:55 PM
  • If that's the case, then you're probably looking for one of these variations:

    select classNotes.fields, case when exists (select 1 from APCity where APCity.City = classNotes.City) then '' else ClassNotes.State as State end from ClassNotes

    or alternatively (assuming that APCity doesn't contain duplicate records per city)

    select classNotes.fields, case when APCity.City IS NULL then classNotes.State else '' end as State from ClassNotes LEFT JOIN

    ApCity on ClassNotes.City = APCity.City

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Charlie2 Tuesday, May 04, 2010 4:16 PM
    Tuesday, May 04, 2010 4:02 PM
  • Thanks, Naom! It works!
    Tuesday, May 04, 2010 4:16 PM
  • I have one addition question regarding this. I've added the WHERE clause to it and now I have a bunch of errors. Here's my complete SELECT statement:

    SELECT *,
    CASE WHEN apCity.city IS NULL
    THEN classNotes.state
    ELSE ''
    END AS apState
    FROM classNotes LEFT JOIN apCity on classNotes.city = apCity.city
    WHERE (([classNotes.approval] = True) AND ([classNotes.archived] = False) AND ([classNotes.cateID] = 2))

     

    And here's the error:

     

    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'classNotes.approval'.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'True'.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'classNotes.archived'.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'False'.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'classNotes.cateID'.

     

    I do have those columns in the classNotes table. Why is it saying column name invalid?

    Tuesday, May 04, 2010 4:46 PM
  • To fix errors:

    WHERE (([classNotes].[approval] = 'True') AND ([classNotes].[archived] = 'False') AND ([classNotes].[cateID] = 2))

    [TableName].[FieldName]

    (or you may not use [ ] at all for your case).


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Charlie2 Tuesday, May 04, 2010 4:55 PM
    Tuesday, May 04, 2010 4:49 PM
  • Perfect! Many thanks again, Naom.
    Tuesday, May 04, 2010 4:55 PM