CODEWITHSUNDEEP
sql
user3702392
user3702392

Reputation: 21

Get the answer from a table using data from other table

How to get the answer to this question using SQL?

What is the name of gatekeeper(s), who are at the gate of a mouse?

(the tables are not in the best format, but imagine that they have 3 columns and the row with "ID ..." is the headline)

the whole "gatekeeper" table

ID  gatekeeper  gate    
1   TE          4    
2   ER          1    
3   PU          2    
4   WD          3    
5   TR          5

the whole "gates" table

ID  animal  gate    
1   cat     3    
2   dog     1    
3   mouse   2

Is it like this?

SELECT gatekeeper
FROM gatekeeper
WHERE gate = (SELECT gate from gates where animal = mouse);

Upvotes: 0

Views: 52

Answers (2)

waka
waka

Reputation: 3407

How do you define 'easy'? The easiest way should be the way you are most comfortable with. However, a subselect can really slow down the performance of a query. It's better to use joins, like this: 

SELECT gk.gatekeeper
FROM gatekeeper AS gk
    JOIN gate AS g on g.gate = gk.gate
WHERE g.animal = 'mouse'

Upvotes: 1

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2376

SELECT gatekeeper.gatekeeper 
FROM gatekeeper 
JOIN gates on gates.gate = gatekeeper.gate AND gates.animal = 'mouse'

Upvotes: 3

Related Questions