Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: MYSQL Query in PHP

    Hi,
    I have 3 tables
    asset
    monitoring
    protocols
    Each asset can have monitoring setup and each monitoring type can have protocols setup.

    I need to select all the assets that have monitoring setup of type 'calibration' and also that have protocols setup for the monitoring type.

    Asset is linked to monitoring by 'asset_id'
    Monitoring is linked to protocols by 'monitor_id'

    I have attached a diagram of my tables that might help.
    Click image for larger version. 

Name:	DB Tables.jpg 
Views:	19 
Size:	121.5 KB 
ID:	11352

    I think I need to build the query using joins but I am not sure where to even start.

    Any help or suggestions would be great.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by punky79 View Post
    Hi,
    I have 3 tables
    asset
    monitoring
    protocols
    Each asset can have monitoring setup and each monitoring type can have protocols setup.

    I need to select all the assets that have monitoring setup of type 'calibration' and also that have protocols setup for the monitoring type.

    Asset is linked to monitoring by 'asset_id'
    Monitoring is linked to protocols by 'monitor_id'

    I have attached a diagram of my tables that might help.
    Click image for larger version. 

Name:	DB Tables.jpg 
Views:	19 
Size:	121.5 KB 
ID:	11352

    I think I need to build the query using joins but I am not sure where to even start.

    Any help or suggestions would be great.

    Thanks
    The MySQL manual is a good start

    as ever Google is your matey

    BTW I reckon you are missing several tables from this design, I'd suggest you re examine your entity Asset
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    5
    thanks for the reply.
    I actually came to a solution.
    Here it is:

    Code:
    "SELECT asset.asset_id, asset.asset_date, asset.asset_num, asset.asset_serialNum, asset.asset_name, asset.asset_description, asset.asset_type, asset.asset_department, asset.asset_owner
    FROM   
    asset 
    INNER JOIN monitoring ON asset.asset_id = monitoring.asset_id
    WHERE monitoring.monitor_type='callibration'
    AND EXISTS
           ( SELECT protocols.protocols_id
               FROM protocols
              WHERE protocols.monitor_id = monitoring.monitor_id )"
    Hope this helps someone with a similar query.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by punky79 View Post
    I actually came to a solution.
    only because i handed it to you

    but you changed my nice formatting

    and you took out my 'booyah' !!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    5
    you got me

    Thanks again

    (link to original solution)
    http://www.sitepoint.com/forums/show...09#post4745009
    Last edited by punky79; 11-22-10 at 12:14.

Posting Permissions

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