basic INNER join question

[voidflux]

Hello everyone I'm having issues converting this statement into an inner join statement.

The statement I wrote works fine but isn't up to date with standards where they prefer inner joins rather than the method i'm doing it.


Here is the orginal code that works:

select * 
FROM 
	ncim.entity, 
	ncpgui.collectionViewEntity, 
	ncpgui.collectionView, 
	ncpgui.networkView  
WHERE
	ncim.entity.entityName = ncpgui.collectionViewEntity.entityName 
AND
	ncpgui.collectionViewEntity.viewId = ncpgui.collectionView.viewId 
AND
	ncpgui.networkView.viewId = ncpgui.collectionView.viewId;

Here's my attempt to convert it with inner joins but i'm confused on what goes where....

SELECT * 
ncim.entity, 
ncpgui.collectionViewEntity,
ncpgui.collectionView, 
ncpgui.networkView 
from ?
INNER JOIN ? ON ncim.entity.entityName = ncpgui.collectionViewEntity.entityName 
INNER JOIN ? ON ncpgui.collectionViewEntity.viewId = ncpgui.collectionView.viewId 
INNER JOIN ? ON ncpgui.networkView.viewId = ncpgui.collectionView.viewId;

I understand it when its used with just 1 table and with 1 key....
but I have 2 databases, using tables from each database...
like database: ncpgui and ncim.

Any help would be great!

Thanks!

[andreasblixt]

With an inner join, you'll want to select from the table that represents one row in your final result set. If you have a 1:1 relationship, you should select from the table that contains the bulk of the data, and doesn't rely on the other. Then you'll want to join in all the other tables. After INNER JOIN you put the name of the table you're joining. Then the ON statement is basically the respective WHERE condition.

SELECT
  * -- It's a good idea to specify each column you want explicitly because if someone adds
    -- a big column in the future, it could have a devastating effect on your application.

  FROM ncim.entity AS e, -- Note that I'm using aliases here; 'ncim.entity' becomes 'e'.
                         -- The AS keyword is optional and can be removed if you prefer.

  INNER JOIN ncpgui.collectionViewEntity AS cve
    ON cve.entityName = e.entityName

  INNER JOIN ncpgui.collectionView AS cv
    ON cv.viewId = cve.viewId

  INNER JOIN ncpgui.networkView AS nv
    ON nv.viewId = cv.viewId

To put it in text:

You're selecting entities from a table. The entities are what you base your result on, and the other tables supply extra data about the entities. This is why it's in the FROM statement.

The collectionViewEntity rows rely directly on the names of the entities in the entity table, so we'll join this next on that condition.

The collectionView table relies on the collectionViewEntity table, and the networkView table relies on the collectionView table.

With all the joins chained with the correct conditions, your query should work. Now you can add WHERE conditions that work on the actual result set after the joins, which makes writing them a lot easier.

[voidflux]

Thanks so much that made it really clear but i'm not sure why i'm getting this error, the syntax looks correct....
I'm using mysql 5.0

mysql> SELECT
    ->   * 
    ->   FROM ncim.entity AS 'e', 
    ->  
    ->   INNER JOIN ncpgui.collectionViewEntity AS 'cve'
    ->     ON cve.entityName = e.entityName
    -> 
    ->   INNER JOIN ncpgui.collectionView AS 'cv'
    ->     ON cv.viewId = cve.viewId
    -> 
    ->   INNER JOIN ncpgui.networkView AS 'nv'
    ->     ON nv.viewId = cv.viewId;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''e', 
 
  INNER JOIN ncpgui.collectionViewEntity AS 'cve'
    ON cve.entityName ' at line 3

Any ideas?


NOTE: I added the ' ' to all the aliais, if I didn't I also got an error.

mysql> SELECT
	->   * -- It's a good idea to specify each column you want explicitly because if someone adds
	->	 -- a big column in the future, it could have a devastating effect on your application.
	-> 
	->   FROM ncim.entity AS e, -- Note that I'm using aliases here; 'ncim.entity' becomes 'e'.
	->						  -- The AS keyword is optional and can be removed if you prefer.
	-> 
	->   INNER JOIN ncpgui.collectionViewEntity AS cve
	->	 ON cve.entityName = e.entityName
	-> 
	->   INNER JOIN ncpgui.collectionView AS cv
	->	 ON cv.viewId = cve.viewId
	-> 
	->   INNER JOIN ncpgui.networkView AS nv
	->	 ON nv.viewId = cv.viewId;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN ncpgui.collectionViewEntity AS cve
	ON cve.entityName = e.entityNa' at line 6

It seems this worked though but I'm not sure why this one worked and the other didnt....

use ncpgui; --thats why I don't have ncpgui.collectionViewEntity..etc
SELECT *
from ncim.entity
INNER JOIN collectionViewEntity ON ncim.entity.entityName = collectionViewEntity.entityName
INNER JOIN collectionView ON collectionViewEntity.viewId = ncpgui.collectionView.viewId
INNER JOIN networkView ON networkView.viewId = collectionView.viewId;

[TheCPUWizard]

Why did you put quotes around the aliases?

They don't belong there, and were not there in the sample that was provided to you....

[voidflux]

I posted 2 versions of the code, one a straight paste from this guy into a text file then in the mysql console and another version with the quotes, that were wrong like you said, i was thinking it might fix it because of this, which I was wrong about:

SELECT v.VPNName AS 'VPN Name',
v.VPNType AS 'VPN Type',
e.entityName AS 'Entity Name'
FROM networkVPN v
INNER JOIN collects c ON c.collectingEntityId = v.entityId
 INNER JOIN entity e ON e.entityId = c.collectedEntityId
 ORDER BY v.VPNName

I see now you only use single quotes if your renaming a column name rather than making an alias to the tables.

But still without the quotes I'm getting that error in the 2nd posted code

[TheCPUWizard]

PAste andreasblixt's version without changes. What happens?

[andreasblixt]

I looked through the query and noticed a comma was left after the FROM statement. Try this:

USE ncpgui;
SELECT *
  FROM ncim.entity e
  INNER JOIN collectionViewEntity cve
    ON cve.entityName = e.entityName
  INNER JOIN collectionView cv
    ON cv.viewId = cve.viewId
  INNER JOIN networkView nv
    ON nv.viewId = cv.viewId;

[voidflux]

Awesome! worked like a charm! thanks!!