MYSQL Query in PHP

punky79 · #1 (**permalink**) 11-22-10, 07:42

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.

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

healdem · #2 (**permalink**) 11-22-10, 10:11

Quote:

Originally Posted by punky79

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.
Attachment 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

punky79 · #3 (**permalink**) 11-22-10, 10:31

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.