Using a view in a select statement

Question

When trying to use view to find information regarding tables in my database, I cannot seem to be able to use my view in a select statement.

CREATE VIEW Num_projects AS SELECT ENumber, COUNT(*) AS Projects FROM WorksOn Group BY ENumber;

SELECT * FROM Num_projects;

CREATE VIEW Same_num_projects AS SELECT Employee.ENumber, Name FROM Employee JOIN Num_Projects WHERE Projects IN( SELECT Projects FROM Num_projects WHERE ENumber = 00101);

SELECT * FROM Same_num_projects;

This is the output from processing the above code:

--------------
CREATE VIEW Same_num_projects AS SELECT Employee.ENumber, Name FROM Employee JOIN Num_Projects WHERE Projects IN( SELECT Projects FROM Num_projects WHERE ENumber = 00101)
--------------

ERROR 1146 (42S02): Table 'csit115.Num_Projects' doesn't exist
--------------
SELECT * FROM Same_num_projects
--------------

ERROR 1146 (42S02): Table 'csit115.Same_num_projects' doesn't exist
--------------

Why isn't my view being recognised?

I can see no point in using views in MySQL - they have severe limitations, and no benefits- although if I was going to, I'd be sure to prefix them with `v_`. Also, note that 00101 is the same as 101 — Strawberry, Oct 13 '18 at 09:03
There is no general rule that would forbid you from using views inside of views. But depending on your system configuration, tablenames (and views) can be case sensitive. So try `JOIN Num_projects` instead of `JOIN Num_Projects`. — Solarflare, Oct 13 '18 at 09:18

score 0 · Answer 1 · answered Oct 13 '18 at 11:33

0

This was solved by changing JOIN Num_Projects to JOIN Num_projects

answered Oct 13 '18 at 11:33

chad

117
3

score 0 · Answer 2 · answered Oct 13 '18 at 12:53

0

It isn't case sensitivity. You had no join condition defined when creating the second view, this is missing ON Employee.ENumber = Num_Projects.ENumber. You can''t form an inner join without an on condition.

CREATE VIEW Num_projects
AS
SELECT
    ENumber
  , COUNT(*) AS Projects
FROM WorksOn
GROUP BY
    ENumber;

SELECT
    *
FROM Num_projects;

CREATE VIEW Same_num_projects
AS
SELECT
    Employee.ENumber
  , Name
FROM Employee
JOIN Num_Projects ON Employee.ENumber = Num_Projects.ENumber
WHERE Projects IN (
    SELECT
        Projects
    FROM Num_projects
    WHERE ENumber = 00101
);

SELECT
    *
FROM Same_num_projects;

answered Oct 13 '18 at 12:53

Paul Maxwell

33,002
3
32
51

For whatever reason, MySQL actually allows [to use mysql JOIN without ON condition](https://stackoverflow.com/q/16470942/6248528) – Solarflare Oct 14 '18 at 18:23
@Solarflare Yes (regrettably this is so) but you are forming a `CROSS JOIN` (also known as `Cartesian product`) where EVERY employee row is matched to EVERY row in Num_Projects. What you NEED is this condition `ON Employee.ENumber = Num_Projects.ENumber` so that the query works correctly. – Paul Maxwell Oct 15 '18 at 00:59

Using a view in a select statement

2 Answers2