🔒 Locked

[web] Select troubles

Started by kooktroop Sep 19, 2005 at 6:18 PM 1 replies 1.4k views

Original Post

Sep 19, 2005 at 6:18 PM

I'm trying to select all the stores that are recommended by the customers friends, based on some criteria. I also want to include in the results all the stores that are not recommended by a friend but meet the criteria So far ive tried: SELECT DISTINCT customers.Fname, stores.StoreID, stores.Name FROM stores, friends, recommendations, customers WHERE ( friends.CustomerID = '7' AND friends.FriendID = recommendations.CustomerID AND customers.CustomerID = friends.FriendID AND stores.StoreID = recommendations.BusinessID AND stores.Suburb LIKE '%gymea%' ) OR ( stores.Suburb LIKE '%gymea%' ) GROUP BY stores.StoreID returns Scott 1 The Big Mexican Rancho Scott 2 Caruso's Gourmet Pizza & Italian Restaurant Scott 3 The Hairy Canary Scott 4 King Palace Chinese & Malaysian Restaurant ------------------------------------------------------------------------------- and SELECT DISTINCT customers.Fname, stores.StoreID, stores.Name FROM stores, friends, recommendations, customers WHERE ( friends.CustomerID = '7' AND friends.FriendID = recommendations.CustomerID AND customers.CustomerID = friends.FriendID AND stores.StoreID = recommendations.BusinessID AND stores.Suburb LIKE '%gymea%' ) OR ( stores.Suburb LIKE '%gymea%' ) returns Scott 1 The Big Mexican Rancho Scott 2 Caruso's Gourmet Pizza & Italian Restaurant Scott 3 The Hairy Canary Scott 4 King Palace Chinese & Malaysian Restaurant lydia 1 The Big Mexican Rancho lydia 2 Caruso's Gourmet Pizza & Italian Restaurant lydia 3 The Hairy Canary lydia 4 King Palace Chinese & Malaysian Restaurant The thing is, Scott has only made one recommendation and thats for storeID = 2, and Lydia is the Fname of the customer that has made the search (her customerID = 7) I want the results to look something like: Scott 2 Caruso's Gourmet Pizza & Italian Restaurant NULL 1 The Big Mexican Rancho NULL 3 The Hairy Canary NULL 4 King Palace Chinese & Malaysian Restaurant It should show the stores that match the criteria and are recommended by friends and then return all the stores that match the criteria minus those that have already been found in the recommendations search.

frob

Sep 20, 2005 at 3:06 PM

You need an outer join.

I'm also not sure what you're trying to accomplish with that 'group by' in your first query since you don't have any aggregates in your query.

Depending on your SQL dialect, it will look something like:

SELECT {fields}
FROM {table1} LEFT OUTER JOIN {table2} ON {table1.field = table2.field}
WHERE {other criteria}
ORDER BY {whatever}

The left join will use all the values on the left, and if there is a match will join the value on the right; if there is no match it will use NULL.

It would look something like:

SELECT  recommendedby.Fname, s.StoreID, s.NameFROM  -- Get all stores, join with either NULL or the recommendations  stores s LEFT JOIN recommendations rec ON s.StoreID = rec.BusinessID  -- JOIN the result with friends who made the recommendation, or NULL    LEFT JOIN friends f ON ( rec.CustomerID = f.FriendID AND f.CustomerID = '7' )  -- JOIN the results with the friend's name, or NULL    LEFT JOIN customers recommendedby on f.FriendID = recommendedby.CustomerIDWHERE  stores.Suburb LIKE '%gymea%'ORDER BY FName, Name

If you had a lot of recommendations then you might need to optimize it a bit, but I believe it would work well enough based on the description you gave.

frob.

kooktroop

Sep 22, 2005 at 4:26 PM

thanks mate that was exactly what i was looking for.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.