Complex (for me) Join

[fnairb]

Alrighty so I was finally convinced to use JOIN instead of implicitly joining in the where clause.  However, I'm unable to properly construct the queries for some of my more complex stuff.

 

For this one I am dealing with three tables.  Using the whole tables here would just over complicate the situation so I simplified the definitions to only what is relevant here.  A node can be associated with zero or many platform.  A platform can have zero or many nodes.  I don't care about nodes without platforms or platforms without nodes (either of these cases is someone else's problem).

 

Table 1: node (simplified)

id, hostname, tier

 

Table 2: platform (simplified)

id, platname

 

Table 3 node_platform

node, platform

 

Without JOIN:

SELECT tier, hostname, platname
FROM node, platform, node_platform
WHERE node_platform.node = node.id
  AND node_platform.platform = platform.id

 

 

How would this be written using JOIN?

[fenway]

SELECT tier, hostname, platname
FROM node
INNER JOIN node_platform ON node_platform.node = node.id
INNER JOIN platform ON node_platform.platform = platform.id

I like to prefix field names and alias tables.

[fnairb]

Could you give an example of what you mean by

I like to prefix field names and alias tables.

[fenway]

Could you give an example of what you mean by

I like to prefix field names and alias tables.

 

SELECT tier, hostname, platname
FROM node AS n
INNER JOIN node_platform AS np ON np.node = n.id
INNER JOIN platform AS p ON p.id = np.platform

Same for the fields, but I don't know which tables they're in.

[fnairb]

Gotcha!  I thought you were referring to how the tables were named.

 

Thanks for the help.