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.

Answers


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.

Refer Join in Mysql

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.

To understand how joins work in MySQL, read about it here in the manual. A good tutorial about joins was written by Jeff Atwood, you can find it here.


Need Your Help

Is there anything faster than fastload ?

jdbc upload teradata

I am loading a table of 200,000,000 rows daily. The fastload does this in 2-3 hours. However, I have other table to upload, so this creates an issue for me because those tables are even bigger.

Resizing the components in a desktop GUI

java swing user-interface awt layout-manager

I am making a GUI for data-structure in Java. I wanted a feature that whenever the user clicks on the maximize button present at the top of the form the components and everything in the form should...