none
Conditional Join Based on Field Value RRS feed

  • Question

  • Okay, the answer to this question is in my brain somewhere, probably between the seat cushions, but I can't tease it out. So I'll just ask.

    I have two tables, Case and Court. In the both tables I have these fields:

    County

    CourtLevel

    CourtNumber

    If the County value in the Case table is "Justice", I want to join the tables using all three fields. Otherwise, I want to join them only on County and CourtLevel. 

    How does that conditional join look in SQL? 

    tod


    Wednesday, September 14, 2016 7:07 PM

All replies

  • Hi tod,

    I think you may need two separate queries (to make it simpler).

    Just my 2 cents...

    Wednesday, September 14, 2016 7:36 PM
  • You could not use JOIN because there is no definite relationship between the two queries since WHERE condition is opposite.
    You could not use UNION for two queries, because the number of columns do not match.
    Monday, September 19, 2016 2:44 PM
    Moderator
  • With due respect to Celeste, you should be able to use a UNION ALL operation along these lines:

    SELECT <column list>
    FROM Case, Court
    WHERE Case.County = "Justice" AND Court.County = "Justice"
    AND Case.Courtlevel = Court.CourtLevel
    AND Case.CourtNumber = Court.CourtNumber
    UNION ALL
    SELECT <column list>
    FROM Case, Court
    WHERE Case.County <> "Justice"
    AND Case.County = Court.County
    AND Case.Courtlevel = Court.CourtLevel
    AND Case.CourtNumber = Court.CourtNumber;

    All that matters is that the column lists are identical.

    Ken Sheridan, Stafford, England

    Monday, September 19, 2016 5:43 PM