Creating VIEW in MySQL Version prior to 5.x

[atheistrical]

I have the following Query Construct which works great with MySQL 5.x!! Is there a way to execute the same query in MySQL versions prior to 5.x?

The creating VIEW is indispensable (or at least I think so).


CREATE VIEW Members
AS
SELECT DISTINCT HOUSENO FROM table
WHERE GENDER = 'female'

SELECT *
FROM Village
WHERE HN NOT IN (
SELECT HOUSENO FROM Members )
ORDER BY HN

[oracleguy]

This statement was added in MySQL 5.0.1.


You can just incorporate the query in the view into your other query.


SELECT *
FROM Village
WHERE HN NOT IN (
SELECT DISTINCT HOUSENO FROM table
WHERE GENDER = 'female' )
ORDER BY HN


Though maybe someone else has a clever trick to do it differently but views weren't officially added until after 5.0.

If you absolutely need views, it would be a good idea to just upgrade to a newer MySQL version if possible.

[atheistrical]

Thank you for that response. The query that you suggested throws an error: "the sub-query returns more than one row".

The very point in using VIEW is to overcome this error. I know that there must be a way out.

NB: My MySQL version cannot be upgraded to 5.x!!