SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Use Of A LinkUp Table

    This is a complicated one to explain but I'll try...

    I have a database with the following tables: vehicles, owners, jobs & linkup. Because the cars are only kept by the some of the owners for a matter of weeks, months or years before being sold to a new owner, we need to be able to track who was the owner on such a date. This was the best way I could come up with and to have the linkup table link the owner to the vehicle along with a date and ref number.

    I now need to create a list of all particular vehicle model owners, but am struggling to get it to show the most recent owner. It prefers, on the otherhand, to defualt to the first owner - in which case would be our company name and not the present owner.

    I have shown my query below and if anyone can this of a better way I'd be very grateful.
    PHP Code:
    SELECT FROM jobs 
      LEFT JOIN linkup ON job_no
    =l_job_no
      LEFT JOIN owners ON o_id
    =l_o_id
      LEFT JOIN vehicles ON v_id
    =l_v_id
      WHERE model 
    ='$model'
      
    GROUP BY chassis
      ORDER BY job_no DESC 
    I have no idea if this makes any sense to you, but will try to explain it more if needs be.

    Many Thanks
    Ian Gunter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please do me a favour, show your table layouts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've taken out all the info which wouldn't be needed for this query, so they stand as...
    Code:
    jobs
     >job_no
     >date_in
     >date_out
     
     owners
     >o_id
     >name_f
     >name_l
     
     vehicles
     >v_id
     >make
     >model
     >chassis
     
     linkup
     >l_job_no
     >l_v_id
     >l_o_id

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    list of all vehicle model owners, showing most recent owner
    Code:
    select v.v_id
         , v.make
         , v.model
         , v.chassis
         , o.o_id
         , o.name_f
         , o.name_l
      from vehicles as v
    inner
      join linkup as l
        on l.l_v_id = v.id
    inner
      join jobs as j
        on j.job_no = l.l_job_no
       and j.date_in
         = ( select max(date_in)
               from jobs
              where job_no = l.l_job_no )
    inner
      join owners as o
        on o.o_id = l.l_o_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers for that, I'll have a blast with it later


Bookmarks

Posting Permissions

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