Hi,

I have 4 tables, Branch, Client, user and userPreferredLocation. Each client has multiple branches and a user has preferred branches they like to visit.

Currently, the query returns branches that require users and doesn't include branches that users like to visit. Is there any way I can modify the query to return all of the branches a user has selected?

Here's my attempt but it doesn't work, can anyone see why?

SELECT Branch.*, Client.Name AS ClientName, Client.Name +' '+ Branch.Name AS Fullname,
PreferredLocation.Location AS Location, User.UserUID As User
From Client INNERJOIN Branch ON Client.ClientUID=Branch.ClientUID
AND Location INNERJOINON Location.UserUID=User.DineerUID
Where Branch.DinerRequired = 1
Orderby Client.Name, Branch.Name

Your best bet in simple terms is to use PhpMyAdmin as it has all the search tools for SQL queries that you will ever need...You will have to get used to them at first. Good Luck

Hth

Should we guess at the table structure and how the PKs and FKs are set up?

Originally Posted by drew22299

Currently, the query returns branches that require users and doesn't include branches that users like to visit. Is there any way I can modify the query to return all of the branches a user has selected?

Change your joins. Requires means an inner join - if you'd like correlated data, but for it to be optional, use an outer (left or right) join instead. As Chris pointed out, you're the only one here who knows your schema, so you'll have to work out how to query the data as it's modeled.

Looks like I forgot to check for replies to this question! Thanks for your replies even though I posted this well over a month ago lol