Need help modifying SQL query 1

[aspdotnetuser]

Hi,

Any comments will be appreciated!

I have 4 tables, [blue]Branch, Client, User[/blue] and [blue]UserPreferredLocation.[/blue] 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?

[blue]SELECT Branch.*, Client.Name AS ClientName, Client.Name +' '+ Branch.Name AS FullName
FROM Client INNERJOIN Branch ON Client.ClientUID=Branch.ClientUID
WHERE Branch.UserRequired = 1
ORDERBY Client.Name, Branch.Name[/blue]
__________________

 

[bborissov]

SELECT Branch.*,
       Client.Name AS ClientName,
       Client.Name +' '+ Branch.Name AS FullName
FROM Client
INNER JOIN Branch 
     ON Client.ClientUID=Branch.ClientUID AND
       (Branch.UserRequired = 1 OR Branch.UserLikeToVisit = 1)
ORDER BY Client.Name, Branch.Name

???

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

boris, i think there might be no column called "UserLikeToVisit"

the branches that a user likes to visit are in the UserPreferredLocation table

r937.com | rudy.ca

 

[bborissov]

O!!!!!!!!!!!!!!!!!!!!
aspdotnetuser talks about users but in that query there is no USER table involved


aspdotnetuser,
Could you post some simple data from ALL tables and what you want as a final result from that data?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[aspdotnetuser]

Thanks for your reply bborissv. Here is my attempt at the query but it doesn't work lol

[blue]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[/blue]

Example data from tables

[red]User[/red]
UserUID, 29
Name, User1

[red]UserPreferredLocation[/red]
PreferredLocationUID, 54323
UserUID, 29
Location, France

[red]Client[/red]
ClientUID, 4
Name, Cafe

[red]Branch[/red]
BranchUID, 5434
ClientUID, 4
Name, South France
[red]UserRequired, 1 (yes)[/red]

I need a query that will get all branches/locations where a user is required

AND

Branches/Locations that the user has listed in [red]UserPreferredLocations[/red]

So, for example even if there isn't a user required at a branch I still want it returned in the query results IF the user has it in their [red]PreferredLocations[/red]

Sorry if I haven't explainned it v well! Thanks for your help so far.

 

[bborissov]

OK, but WHERE is the user table in your SELECT?
You still select from Client not from USERS.
OK, let's try again:
You have these tables:
[tt]
USERS:
UserId Name
--------------------------
29 John
30 Paul
31 George
32 Ringo


UserPreferredLocation:
PreferredLocationUID UserId Location
-------------------------------------
1 29 Liverpool
2 31 London
3 32 Paris
4 33 Everywhere but home


Client:

ClientId Name
---------------------
1 Apple
2 MGM

Branch:
BranchUID ClientUID Name UserRequired
----------------------------------------------------
1 1 South France 1
1 2 Georgia 1
[/tt]

So take a look in this data and tell me what result you want from it.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[aspdotnetuser]

I managed to create a new query that returns the correct result. Thanks!