Carsten Thank you very much for pointing me in that direction, that nearly solved the issue but I still get a duplicate for each record that is in the second table (if WishListItems has 5 records for a Visitor, I get 5 duplicates in the result).
I tried using GROUP BY to eliminate the duplicates and this seems to work, but it seems to too much overhead, or is it? Is writing the query this way correct? SELECT * FROM Visitors LEFT JOIN WishListItems ON Visitors.VisitorID=WishListItems.VisitorID WHERE WishListItems.VisitorID IS NOT NULL AND Visitors.NameLast='Collins' GROUP BY Visitors.NameLast; Michael At 9:33 AM +0200 9/11/02, Carsten Zilch wrote: >Hello, > >the answer for: >RE: Selecting data from one table if it's NOT in another >should also answer your question > >Carsten > >-----Ursprüngliche Nachricht----- >Von: Michael Collins [mailto:[EMAIL PROTECTED]] >Gesendet: Dienstag, 10. September 2002 22:40 >An: [EMAIL PROTECTED] >Betreff: SQL help, search with related record in 2nd table > > >I am looking for some help on a SQL query to perform a search, but >constrain the search to those records which have a related record in >a second table. What I think I need is a subselect, but since this is >not possible in 3.23.47, I am looking for work around. > >I have one table for Visitors (key is VisitorID) and another for >items that a user has selected named WishListItems (foreign key is >VisitorID). > >SELECT Visitors.* FROM Visitors, WishListItems >WHERE Visitors.VisitorID=WishListItems.VisitorID AND >Visitors.NameLast REGEXP 'Collins'; > >I get a result for each record there is in WishListItems. > >I tried using a Join but that does not help: > >SELECT Visitors.* FROM Visitors INNER JOIN WishListItems >On Visitors.VisitorID=WishListItems.VisitorID WHERE Visitors.NameLast >REGEXP 'Collins'; > >I seem to get the same result. > >What I need is to search Visitors, but only find records from >Visitors if there are any records in WishListItems (I am not checking >anythign in WishListItems, there just has to be at least one record). > >-- >Michael >__ -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA http://michaelcollins.net --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
