Querying 2 Tables Returns Multiple Values Instead of Single

-Hi...In your Query, you have to join your  tables because they are related .But after you validate the user you have to include also in your query the user id to filter your quey...
(Where userId=....)
- Try to use Parameters in your statements instead of '" + txtbUserName.Text + "'"

John (Yiannis)

Hey, I think you need to change the query, because if you are validating the user using his name (column-"username"), then it is obvious that you may get more than one result, since there may have more than one user having same name. !!
You can write like this

cmd = New SqlCommand("Select password From users Where userid = '" + txtbUserName.Text + "'", con)

Try this

Select password from users innerjoin agencies on agency.username = users.username and agency.agencyID = users.agencyid where userID = '" + tblUserName.Text + "'"

Since you didn't give column names, I took a guess as to what they might be.

I pretty much tried that (if I understand what you're saying correctly).  I have 2 tables (users & subUsers with FK relationship betwenn users.id and subUsers.agnID.  Users has column name password and subUsers has column name suPassword (of course there are other columns as well, but irrelevant for this query).

I've been doing this in SSMS to  work out the query before placing it in the vb page.  After this runs, it reads the password into the code and then compares against what the user has entered at login.  It has been working great, I just now need to allow it to check a second table column to allow subUsers to login.

I posted one of my attempts below (with actual value for testing) but the problem is that it returns the password for "me" twice in the password column and it return 2 different results in the suPassword column.  I'm only expecting it to return the password for  "me" once, since it's only in the database once in the password list.

We don't allow duplicate usernames by the way, so all of them are unique.

Thanks!

Select users.password, subUsers.supassword 
From users  inner join subUsers on subUsers.agnID = users.id
Where users.username = 'me' or subUsers.suUsername = 'me'

Select allOpen in new window

does either the users table or sbuUsers table have the username 'me' twice?

Where users.username = 'me' or subUsers.suUsername = 'me'

if you have it twice in either table, it will show twice in the query

Select users.password, subUsers.supassword 
From users  inner join subUsers on subUsers.agnID = users.id
Where users.username = 'me' 

Select allOpen in new window

run a select count(*) from users where user.username = 'me'
and
select count(*) from subusers where suusername = 'me'

No, it's only in the users.username 1 time.  That's what's strange.....I thought this would actually be an easy query, but evidently not.

After looking at it more what seems to be happening is that it returns all of the passwords for the subUser table regardless and simply replicates the "me" in the users.password column.

It's something like this:

password              suPassword

me                          dave123
me                          arch8#
me                          clem091^

If I add a new subUser account then it adds another row with "me" and the suPassword.  (currently I only have three new users in the subUser table, so it's returning each user regardless of the filter.

(Hope this is readable....my preview image is not display when I click the preview button)

so the agnID is listed 3 times in the subUsers table.

this would give a crosstab query.
you would need another key to trigger on in the database

such as
Select users.password, subUsers.supassword
From users  inner join subUsers on subUsers.agnID = users.id and subUsers.password = users.password
Where users.username = 'me'

That returns 0 records.  I'm not really sure how the crosstab query works, but the users.password and subUsers.suPassword would never = each other since the passwords (or usernames) never duplicate.  The only common element between the 2 would be the users.id and the subUsers.agnID.

Also, I would think I need to filter (WHERE) on both tables  to ensure I am checking both tables for a username at login.  Like I said though, my logic may be flawed since I've not done crosstab queries before.

Okay...after playing with some of your queries rg20 (and a little more reading) here's what I've come up with that works (below).  This checks both tables, returning a correct response from either table, then displays in a single column and row.

Thanks for the help and getting me started down the right path!!

Select users.password from users where users.username = 'me'
UNION
(select subUsers.suPassword from subUsers where subUsers.suUsername = 'me')

Select allOpen in new window