Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Complex self join.

    Good Morning all.... I have a data set and a needed result. I just can't seem to get there in a single query. Which is unfortunate, since I'm likely to have 50-200 of these rows come out. Let me explain.

    Here is a (short and simple) look at my data:

    Car Officer Date
    ----------------------------------------------
    S24 Morales 2003-09-01
    F21 Perkins 2003-09-01
    S06 Markel 2003-09-02
    S24 Perkins 2003-09-02
    .....

    Note: Cars that are not in use in a given day do not have an entry.

    Now the output of my query needs to look like this:

    Car Date Date Date Date Date Date Date
    ------------------------------------------------------------------------------------
    S24 Morales Perkins [null] Morales Smith [null] [null]
    F21 Perkins [null] Smith Perkins Davis [null] [null]
    S06 Markel Morales [null] Smith .......

    The key here is that each car must show once and only once in the
    left column. It would be best if the query could handle any date range, but for now I can get away with a fixed 7 day span each time. I have a little flexibility in the structure of the data (but not complete, I need to do this with one table if possible).

    I'm not even really convinced that it's possible, as I have tried a number of combinations.

    Thanks for looking.
    R

  2. #2
    Join Date
    Jul 2003
    Posts
    35
    Hi R,

    You could try the following...

    SELECT
    A.CAR,
    MAX(CASE WHEN A.DATE='2003-09-01' THEN A.OFFICER ELSE
    (IF (C.DATE='2003-09-01' AND A.CAR=C.CAR, C.OFFICER, NULL)) END) AS '1/9/03',
    MAX(CASE WHEN A.DATE='2003-09-02' THEN A.OFFICER ELSE
    (IF (C.DATE='2003-09-02' AND A.CAR=C.CAR, C.OFFICER, NULL)) END) AS '2/9/03'
    FROM CARS AS A
    LEFT JOIN CARS AS C ON A.CAR=C.CAR
    GROUP BY CAR

    +------+---------+---------+
    | CAR | 1/9/03 | 2/9/03 |
    +------+---------+---------+
    | F21 | Perkins | NULL |
    | S06 | NULL | Markel |
    | S24 | Morales | Perkins |
    +------+---------+---------+
    3 rows in set (0.00 sec)

    This works ok on the data you provided and seems to function ok for other timelines.

    I have assumed all cars, drivers and dates are unique, so there can only be one car used by one officer on any one date.

    Obviously this statement is restricted by a fixed set of dates. So it should work ok for the fixed 7 day period you want. Would take a bit of mucking around to get it to work for dynamic periods... but this could easily be done through the client side program.

    Its a bit of a hack, but should provide you with something until someone else formulates something more elegant.

    Chrs,
    Ash
    Last edited by asherh; 09-02-03 at 03:07.

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    Ah... that's very interesting. I'll try it tomorrow. Thanks.

    R

    Chrs,
    Ash [/SIZE][/QUOTE]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •