CODEWITHSUNDEEP
phpmysql
user1038814
user1038814

Reputation: 9647

mysql select within select

this is the first time I'm dabbling in Databases and was wondering if it was possible to do the following: I have two tables 

student (id, name, phone)
photos  (id, student_id, photo)

I wanted to retrieve the student phone who's name is say 'Tom' but only if he has a photo. Is it possible at all to have a condition like this?

Many thanks

Upvotes: 3

Views: 1477

Answers (4)

Icarus
Icarus

Reputation: 63956

select s.phone from student s 
inner join photos p 
on p.student_id=s.id
where p.photo is not null

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Yes. Using EXISTS:

SELECT phone
FROM student
WHERE name = 'Tom'
  AND EXISTS
      ( SELECT *
        FROM photos
        WHERE photos.student_id = student.id
      )

or using JOIN. You need to use either DISTINCT or GROUP BY so you don't get the same phone 20 times if the user has 20 photos:

SELECT DISTINCT student.phone
FROM student
  JOIN photos
    ON photos.student_id = student.id
WHERE student.name = 'Tom'

Upvotes: 0

breen
breen

Reputation: 111

In this case, you can use a join. Try this:

SELECT student.phone
FROM students
INNER JOIN photos
ON student.id=photos.student_id
WHERE student.name LIKE 'Tom'

Upvotes: 0

Daniel A. White
Daniel A. White

Reputation: 190907

That is called an INNER JOIN.

select top 1 name from student s 
inner join photos p
on s.id = p.student_id
where name like 'Tom'

Upvotes: 0

Related Questions