CODEWITHSUNDEEP
mysql
fschoot1
fschoot1

Reputation: 61

Returning a specific type of entry in a table (SQL)

I have three tables Buildings, EnergyType and EnergyCategory. The first two are connected by a bridge table called build_types. The second two are connected by having related IDs (type_ID = cat_ID).

This join outputs buildings and every energy type they use:

SELECT B.building, E.energytype
FROM Buildings b
INNER JOIN build_types bt ON B.build_ID = bt.build_ID
INNER JOIN EnergyTypes e ON bt.type_ID = E.type_ID
ORDER BY B.building;

This join outputs the total number count of each energy type:

SELECT E.energytype, COUNT(*)
FROM Buildings b
LEFT JOIN build_types bt ON B.build_ID = bt.build_ID
LEFT JOIN EnergyTypes e ON bt.type_ID = E.type_ID
GROUP BY E.energytype;

I want a select statement that will return a table with building name on the left and energy category on the right, with only categories described that are 'Renewable'. I have:

SELECT B.building, E.energytype, E.energycategory
FROM Buildings b
INNER JOIN build_types bt ON B.build_ID = bt.build_ID
INNER JOIN EnergyTypes e ON bt.type_ID = E.type_ID
INNER JOIN EnergyCategories e ON E.cat_ID = E.type_ID
WHERE E.energycategory = 'Renewable';

but I get Error 1066: Not unique table/alias 'EnergyTypes'.

Upvotes: 1

Views: 31

Answers (2)

codeMonger123
codeMonger123

Reputation: 505

Change the alias for one of the tables. Make 'e' something else for one of them.

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65264

You have chosen the alias "E" for both EnergyTypes and EnergyCategories.

SELECT B.building, T.energytype, C.energycategory
FROM Buildings b
INNER JOIN build_types bt ON B.build_ID = bt.build_ID
INNER JOIN EnergyTypes T ON bt.type_ID = T.type_ID
INNER JOIN EnergyCategories C ON C.cat_ID = T.type_ID
WHERE C.energycategory = 'Renewable';

should work.

Upvotes: 1

Related Questions