CREATE TABLE assets (
asset_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
material_id INT UNSIGNED,
project_id TINYINT UNSIGNED,
rack_id INT UNSIGNED,
name VARCHAR(50),
mounting_direction ENUM('Front','Back'),
beginning_ru TINYINT UNSIGNED,
ending_ru TINYINT UNSIGNED,
beginning_x SMALLINT UNSIGNED,
ports TINYINT UNSIGNED,
notes TEXT DEFAULT 'None',
created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_by VARCHAR(50) NULL,
updated_date TIMESTAMP NULL,
operational_status BOOLEAN DEFAULT 1,
FOREIGN KEY (material_id) REFERENCES materials (material_id),
FOREIGN KEY (rack_id) REFERENCES racks (rack_id),
PRIMARY KEY ( asset_id )
);
CREATE TABLE data_connections (
data_connection_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
to_asset_id INT UNSIGNED,
from_asset_id INT UNSIGNED,
name VARCHAR(50),
notes TEXT DEFAULT 'None',
created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_by VARCHAR(50) NULL,
updated_date TIMESTAMP NULL,
operational_status BOOLEAN DEFAULT 1,
FOREIGN KEY (to_asset_id) REFERENCES assets (asset_id),
FOREIGN KEY (from_asset_id) REFERENCES assets (asset_id),
PRIMARY KEY ( data_connection_id )
);
``
and am trying to get the asset_ids used in the second table
SELECT data_connection_id,data_connections.name FROM data_connections
RIGHT JOIN assets ON data_connections.from_asset_id = assets.asset_id
RIGHT JOIN assets ON data_connections.to_asset_id = assets.asset_id
WHERE assets.project_id = ‘.$project_id.’ AND data_connections.operational_status = 1
I expect it’s just from the PHP code where the OP isn’t using a Prepared Statement for some reason, and the rest of the quotes and so on are just not in the post.
it is considered helpful when asking for help on an SQL error to actually show what the error message said, instead of making us guess
in this case, though, it’s obvious – you’ve got the same table in the query twice, but you don’t distinguish them with aliases
FROM data_connections
RIGHT JOIN assets ON data_connections.from_asset_id = assets.asset_id
RIGHT JOIN assets ON data_connections.to_asset_id = assets.asset_id
If the query is to show information about the connection (i.e. “Connection 5 goes from Asset A-1 to Asset A-2”, then the two joins are needed, one for each asset.
If the query is to show just connections which are connected to certain assets, then one join will do, but it will have to be an OR, not an AND
The reason the query is choking as written is as Rudy states, there’s no alias on either table join. You can’t join the same table twice without at least one alias because the interpreter can’t tell which join goes with which. Add in the where clause and it really has no idea what is going on.
Add in the RIGHT JOIN makes no sense because a RIGHT JOIN would look for a record on assets and POSSIBLY a record on data_connections, but the where clause includes BOTH tables. A LEFT JOIN makes the most sense if you’re going to be looking at information from both assets, assuming there is a connection but may not be complete. Or if looking just for connections which are connected to a specific project, then the one join works but should be an INNER JOIN
So it needs to be either something like this
SELECT D.connection_id
, D.name
, AF.name AS AssetFrom
, AT.name AS AssetTo
FROM data_connections D
LEFT JOIN assets AF ON AF.asset_id = D.from_asset_id
LEFT JOIN assets AT ON AT.asset_id = D.to_asset_id
WHERE (AF.project_id = 'x' OR AT.project_id = 'x')
AND D.operational_status = 1
or it needs to be something like
SELECT D.connection_id
, D.name
FROM data_connections D
INNER JOIN assets A ON A.asset_id = D.from_asset_id OR A.asset_id = D.to_asset_id
WHERE A.project_id = 'x'
AND D.operational_status = 1