#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    8
    Rep Power
    0

    Question Question on building a join query


    Hi there,

    I'm writing to ask for your help on how to build a join query... (at least that's what I think I need to do...) I've read a bunch of tutorials on the subject over the years, but it just doesn't make any sense to me. I was hoping you could help. Here's what I'm trying to do...

    I have two tables that look like this:

    TABLE: inspections

    inspection_id (this is the auto incrementing row_id)
    account_id
    technician_id
    inspector_id
    supervisor_id
    manager_id
    etc.

    TABLE: inspections_dates

    inspection_id (this links to the inspection_id of the inspections table; this one is not auto incrementing)
    date_service
    date_inspection
    date_flagged
    etc.

    I need to be able to pull records from the inspections table based on their dates from the second table. Sometimes I also need to filter them by other fields in the main table. So it could be one of the following three (the first is easy for me, of course):

    1. SELECT * FROM inspections WHERE account_id = '123456'

    2. SELECT * FROM inspections WHERE account_id = '123456' AND date_service <= '2004-01-01'

    3. SELECT * FROM inspections WHERE date_service <= '2004-01-01'

    The problem is...I don't know how to reference the date_service in a query like that. Plus it has to somehow know that the inspection_id is the same in both fields, so I can pull just one array using PHP...

    Can you help? Thanks in advance!

    Robert Samuel
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    8
    Rep Power
    0
    Well, I finally figured it out...

    SELECT * FROM inspections INNER JOIN inspections_dates ON inspections.inspection_id = inspections_dates.inspection_id WHERE inspections_dates.date_service <= '2004-01-01'

    Hopefully this post will help someone else out in the future...

IMN logo majestic logo threadwatch logo seochat tools logo