MySQL Forums
Forum List  »  New in 4.1: Subqueries

Substandard Subquery
Posted by: Guy
Date: April 28, 2005 03:14AM

I am trying to make a multiple SELECT query, the scenario is as follows. I have a house description table [gal] with numerous integer fields each of which relates to a corresponding field in a separate menu table [men]; e.g. ‘Phone’ in ‘gal’ is represented by a number that relates to type of phone service option chosen from men. So for house 2, I need to return numerous SELECTS in 1 query, each with a unique INNER JOIN linking the index field on men to the relevant field in gal.

SELECT PhoneO FROM men INNER JOIN gal ON men.IDX = gal. phone WHERE gal. phone >=1 AND gal.GiteID=2
UNION
SELECT NetO FROM men INNER JOIN gal ON men.IDX = gal. net WHERE gal. net >=1 AND gal.GiteID=2

This returns both records but in one column and I need results in separate columns. I can't figure if a subquery could do the job -this doesn't:

SELECT *
FROM
(SELECT PhoneO FROM men INNER JOIN gal ON men.IDX = gal. phone WHERE gal. phone >=1 AND gal.GiteID=2) AS UViewAjO
FROM
(SELECT NetO FROM men INNER JOIN gal ON men.IDX = gal. net WHERE gal. net >=1 AND gal.GiteID=2) AS UViewO;


I’ve being playing around with this for ages and my limited SQL is just not up to it. [ I have not stored menu options in an ENUM field in ‘gal’ as I want menus available in other languages]
Anyone with any ideas on this one would be most welcome.
Regards Guy

Options: ReplyQuote


Subject
Views
Written By
Posted
Substandard Subquery
4153
Guy
April 28, 2005 03:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.