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
select s.phone from student s
inner join photos p
on p.student_id=s.id
where p.photo is not null
photos.photo is nullable.
only if it has a photo...from student, not students. You also need the where name = 'Tom'.photos with his id. But adding where p.photo is not null doesn't hurt in either case.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'
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'
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'
SELECT phone, not SELECT photo.INNER JOIN is the same as JOIN. Neither gets away with the need for DISTINCT, if we want to display only phone and not phone+photos.JOIN was a LEFT JOIN.