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

    Unanswered: workaround for a subquery


    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:
    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
    Toronto, Canada
    select B.`Booking Number`
         , B.`Offender ID`
         , X.`Arrest Date` 
      from Booking as B
      join Arrest as X
        on X.`Offender ID` = B.`Offender ID`
      join Arrest as Y  
        on Y.`Offender ID` = B.`Offender ID`
     where B.`Custody Status` = 'In Custody'
        by B.`Booking Number`
         , B.`Offender ID`
         , X.`Arrest Date`
    having X.`Arrest Date` = max(Y.`Arrest Date`)
        By X.`Arrest Date` desc | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    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