How to make inner join on this query
I have this query:
SELECT hit.timestamp,hit.id,config.Name,hit.meter_id,levels.LevelName, pos.sm_pos , hit.hit_value FROM pos,hit,controllers,levels,config WHERE hit.id=config.id AND hit.meter_id=levels.id AND pos.id=hit.id AND pos.controller_id=controllers.id;
How to make an inner join query from this? With aliases or something? I was looking and I can't find anything for multiple table query.
The way you are joining tables is outdated now. It was used eartlier, now we use keyword like INNER/NATURAL/LEFT OUTER/RIGHT OUTER/CROSS etc. to join tables on basis of requirement.
SELECT hit.timestamp, hit.id, config.Name, hit.meter_id, levels.LevelName, pos.sm_pos, hit.hit_value FROM hit INNER JOIN config ON hit.id = config.id INNER JOIN levels ON hit.meter_id = levels.id INNER JOIN POS ON pos.id = hit.id INNER JOIN controllers ON pos.controller_id = controllers.id;
Note : The query posted by you is according the SQL-89 standard and the second posted by me is according to SQL-92. The SQL-92 standard introduced INNER JOIN .. ON and OUTER JOIN .. ON in order to replace the more complex(?) syntax of SQL-89.
If you want to reformat your query, you can do it like this:
SELECT H.timestamp, H.id, F.Name, H.meter_id, L.LevelName, P.sm_pos, H.hit_value FROM pos AS P INNER JOIN controllers AS C ON P.controller_id = C.id INNER JOIN hit AS H ON P.id = H.id INNER JOIN levels AS L ON H.meter_id = L.id INNER JOIN config AS F ON H.id =F.id;
Notice that I've taken the liberty to add aliases on your table names, this can simplify your queries alot.