nested select query in sql 2005

Question

0.00/5 (No votes)

See more:

I have two table.
1.Admin(username,password)
2.User(username,type)
I have given username and password and I want retrieve the type of user from User table.

Posted 17-Nov-11 21:43pm

Member 7909353

Add a Solution

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2011-11-17T21:48:00

Solution 1

SQL

select type from [User] where username = 'the_username_wanted'

Posted 17-Nov-11 21:48pm

Mehdi Gholam

Reiss · Answer 2 · 2011-11-17T21:49:00

Solution 2

a simple join is what you are after

SQL

SELECT
    *
FROM
    Admin a
JOIN
    User u
ON
    a.username = u.username
WHERE
    a.username = @username
AND
    a.password = @password

There is also a good article on CP about joins here[^]

Posted 17-Nov-11 21:49pm

Reiss

Updated 17-Nov-11 21:50pm

v2

Comments

Mehdi Gholam 18-Nov-11 3:53am

You don't need to join when you already have the username and it is in the user table.

Reiss 18-Nov-11 4:47am

True, but why make two calls to the database when you can do it in one - to me the title suggests that this is what the user is doing

MrLonely_2 · Answer 3 · 2011-11-17T21:50:00

Solution 3

SQL

select type from user where username = @username

@username is a parameter that you have to pass it to stored procedure or sql statement.

Posted 17-Nov-11 21:50pm

MrLonely_2

v2

member60 · Answer 4 · 2011-11-17T21:57:00

Solution 4

better to use identity column in Admin table and make it primary key .
In the User table take a friegn key (eg.UserId)then try the following query :

SQL

select type from UserT inner join Admin on  UserT.UserId=Admin.UserId
 where UserT.UserName='xyz'

Posted 17-Nov-11 21:57pm

member60

Updated 17-Nov-11 22:03pm

v3

Mukund Thakker · Answer 5 · 2011-11-17T22:13:00

Solution 5

SQL

SELECT type FROM Admin a
INNER JOIN User u ON a.username = u.username
WHERE
    a.username = @username
AND
    a.password = @password

Posted 17-Nov-11 22:13pm

Mukund Thakker

Prince Antony G · Answer 6 · 2011-11-17T22:14:00

Solution 6

SQL

select type from user u, admin a where a.username=b.username and a.username='demo' and a.password='demo'

Posted 17-Nov-11 22:14pm

Prince Antony G