CODEWITHSUNDEEP
sql
The Newbie
The Newbie

Reputation: 389

Select from 1 table where conditions are in another table?

Here is my table in my database:

table1             table2    
---------|--------  --------|
UserID   |Type      UserID  |
---------|--------  --------|
user1    |Busy       user1  |
user2    |Free       user2  |
user3    |Free       user3  |
user4    |Busy       user4  |

I would like to select all the userID from table1 where the Type=Free. The only correlation from one table to another is that they have the same UserID in both tables.

What I have so far is :

SELECT UserID
FROM Data.Users
INNER JOIN Data.UserType
ON Data.Users.UserID=Data.UserType.UserID

This only gives me back ALL the userID where the UserID from table 1 = UserID from table 2.

I would like to be able to make it also check for the user type. Something like 

SELECT Username FROM Data.Users WHERE TABLE2.UserID = TABLE1.UserID AND                        
TABLE2.UserType = "free"

I'm new to SQL so I don't think I'm Googling the right thing as well

Upvotes: 0

Views: 867

Answers (2)

cmprogram
cmprogram

Reputation: 1884

You have to ensure you are first selecting the data that you want, in this case "Type". So that's all we need to be concerned with when selecting information. However, for illustration purposes (and general good etiquette) lets also select the userID, but we'll take this from table1.

So we want to select Type from Table 2, and UserID from table 1, but where UserID is the same on both tables.

To do what we need to do the following:

SELECT two.Type, one.UserID FROM table2 two INNER JOIN table1 one ON two.UserID = one.UserID

This selects the information, from table2 and we assign it the reference two. Then we join table1 and assign the reference one. We can then grab the requested information in the select using the references we assigned, whilst also ensuring the data is joined where the two tables have identical values.

The above example will select all values where the userID's match, and is used for illustration purposes. Then, to narrow it down, all you have to do is specify the WHERE value on top of this. So:

SELECT two.Type, one.UserID FROM table2 two INNER JOIN table1 one ON two.UserID = one.UserID WHERE two.Type = "Free"

Upvotes: 1

forpas
forpas

Reputation: 164064

So your query is working you just need to filter it:

SELECT Data.Users.Name
FROM Data.Users
INNER JOIN Data.UserType
ON Data.Users.UserID=Data.UserType.UserID
WHERE Data.UserType.Type = 'Free'

Upvotes: 2

Related Questions