Short-answer: use "IN" instead of "="
Long-answer:
Your query is kind of weird. I think you want to use "IN":
SELECT memberid, fullname FROM members WHERE memberid IN (select
distinct memberid FROM familymembers)
The equals implies an exact match between the top-level, and the
sub-query, but I am guessing your sub-query will return more than one
row, and that's where your problem lies.
Oracle won't let you do it (I haven't worked with anything other than
4.0 in MySQL, so I can't say regarding 4.1) - complains that a
single-row-subquery returns more than one row.
If you do this in Oracle:
select * from listing_status where listing_status_id = (select
listing_status_id from listing_status where rownum < 2)
it will work, as the sub-query returns exactly one row. Not sure how
MySQL would handle it if you put a "LIMIT" on it. But I don't think
that's what you are trying to do.
(also) David
Oracle, I believe, will
David
David Kagiri wrote:
i get an error when i run the query below though SQLyog.
SELECT memberid,fullname FROM members WHERE memberid
= (select distinct memberid FROM familymembers)
the error is
Error Code : 1064
You have an error in your SQL syntax. Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'select
distinct memberid FROM familymembers)' at line 2
(60 ms taken)
i use MySQL version is 4.1.7 dosent it support subqueries?
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
