Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Unanswered: workaround for a subquery

    Hi,

    I'm connecting to a mySQL database that doesn't support subqueries (version 4.0.)...

    The following is the short version of what I initially attempted to do:
    -------------------------------
    select
    B.`Booking Number`,
    B.`Offender ID`,
    (
    Select `Arrest Date` from Arrest
    Where B.`Offender ID` = `Offender ID`
    Order By `Arrest Date` desc
    LIMIT 1
    ) as `Arrest Date`

    FROM Booking B
    where B.`Custody Status` = 'In Custody'
    -------------------------------

    As you can see, I want to return the respective arrest date per booking (most recent arrest date for each booking that has the person still in custody).

    The problem is that the Booking and Arrest table DO NOT relate by "Booking Number", which is the unique identifier for the Booking table. Trying to do a regular join through `Offender ID` would return too many records which would repeat the "Booking Number".
    (I want only one/unique "Booking Number" per record returned and avoid the growth of the recordset through joining)

    How can I accomplish that without using a subquery?

    I would appreciate any pointers.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select B.`Booking Number`
         , B.`Offender ID`
         , X.`Arrest Date` 
      from Booking as B
    inner
      join Arrest as X
        on X.`Offender ID` = B.`Offender ID`
    inner
      join Arrest as Y  
        on Y.`Offender ID` = B.`Offender ID`
     where B.`Custody Status` = 'In Custody'
    group
        by B.`Booking Number`
         , B.`Offender ID`
         , X.`Arrest Date`
    having X.`Arrest Date` = max(Y.`Arrest Date`)
    order 
        By X.`Arrest Date` desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    51
    Exactly what I was looking for, thanks. I appreciate your help.

Posting Permissions

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