asp.net SelectCommand SQL JOIN problem

Question

I'm a database noob so please bear with me. I'm trying to join three tables together in the SelectCommand attribute of a SqlDataSource. Here's my select command at the moment:

SelectCommand="SELECT DISTINCT a1.[CreateDate], a2.[UserName], c1.[name], c1.[organisation], c1.[address], a1.[IsApproved], a1.[UserId] FROM [aspnet_Membership], [aspnet_Users] a2, [clients] c1 INNER JOIN [aspnet_Membership] a1 ON c1.userid = a1.UserId INNER JOIN [clients] c2 ON c2.userid = a2.UserId AND a1.IsApproved = 'False'"

It's kinda mucky because I've been playing with it. I'm trying to display the registered date from aspnet_Membership (CreateDate), username from aspnet_Users (UserName) and other details from my custom clients table. But my joins aren't working. I managed to get aspnet_Membership to join fine with clients to display CreateDate, but I can't join aspnet_Users to show the username. I keep getting errors saying "aspnet_Users.UserId could not be bound".

Any ideas? I'm pretty sure the problem is in the joining, but not matter how many tweaks I make to the command I can't get any results.

Any help would be much appreciated :)

smirkingman · Accepted Answer · 2010-11-22 13:07:16Z

2

Why are you using clients twice? (Maybe explain what you're trying to achieve)

Try this:

SELECT DISTINCT 
    am.[CreateDate], ru.[UserName], cl.[name], cl.[organisation], 
    cl.[address], am.[IsApproved], am.[UserId] 
FROM 
    [aspnet_Membership] am
    INNER JOIN clients cl
        ON am.userid = cl.userid
    INNER JOIN [RolesAndAllUsers] ru
        ON cl.userid = ri.userid
WHERE
    cl.IsApproved = 'False'

answered Nov 22, 2010 at 13:07

smirkingman

6,4365 gold badges37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

melat0nin Over a year ago

That worked after some tweaking, thanks! RolesAndAllUsers should have been aspnet_Users (I got the former from some other example) but after changing that it works great :)

Anthony Faull · Accepted Answer · 2010-11-22 12:53:32Z

0

This is what you have posted. I suspect the cross joins are where your problem lies.

SELECT DISTINCT a1.[CreateDate], a2.[UserName], c1.[name], c1.[organisation], 
    c1.[address], a1.[IsApproved], a1.[UserId] 
FROM [aspnet_Membership]
CROSS JOIN [RolesAndAllUsers] a2
CROSS JOIN [clients] c1 
INNER JOIN [aspnet_Membership] a1 ON c1.userid = a1.UserId 
INNER JOIN [clients] c2 ON c2.userid = a2.UserId 
WHERE a1.IsApproved = 'False'

answered Nov 22, 2010 at 12:53

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

Collectives™ on Stack Overflow

asp.net SelectCommand SQL JOIN problem

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related