Select only records with linked records

Question

I am seeking a way in mySQL to select only records that have corresponding records in a linked table. I am likely suffering tunnel vision, or otherwise missing something simple.

I have the following query which currently works besides this requirement above:

SELECT P.ID, P.NAME, P.SEO_NAME, CI.City, R.Region, C.Country
FROM PROPERTIES P 
LEFT JOIN Cities CI ON P.CITY_ID = CI.CityId
LEFT JOIN Regions R ON P.REGION_ID = R.RegionID
LEFT JOIN Countries C ON P.COUNTRY_ID = C.CountryId 
WHERE APPROVED = '1' AND REGION_ID = '5400' 
ORDER BY RAND() LIMIT 1;

This is related to a previous question of mine, here: Select rows Having Count in linked table

While the answer in this linked thread worked for me at the time, I now require the additional information in the query above. Is there any way I can limit it so that only records with records in the linked table PROPERTY_PHOTOS PP (which links ON P.ID = PP.PROPERTY_ID)

Thanks in advance for any input.

You're not linking the PROPERTY_PHOTOS table. If you update your query, I'll fix my answer. — MattBelanger, Aug 31 '11 at 14:43
Thanks to everyone for pointing out the elephant I wasn't seeing. Heh. — DaveL, Aug 31 '11 at 14:59

score 4 · Answer 1 · answered Aug 31 '11 at 14:43

4

Try using INNER JOIN instead of LEFT JOIN. According to the SQL specifications for INNER JOIN:

The INNER JOIN keyword return rows when there is at least one match in both tables.

For the LEFT JOIN, this becomes:

The LEFT JOIN keyword returns all rows from the left table (table_name1), 
even if there are no matches in the right table (table_name2).

answered Aug 31 '11 at 14:43

Tom Knapen

2,257
15
30

@abatishchev: And that's exactly what INNER JOIN does: exclude missing links – Tom Knapen Aug 31 '11 at 14:46

score 4 · Answer 2 · edited Aug 31 '11 at 14:55

4

An INNER JOIN should do this for you:

INNER JOIN PROPERTY_PHOTOS PP ON P.ID = PP.PROPERTY_ID

This will only return records where there is a match in both tables.

edited Aug 31 '11 at 14:55

abatishchev

95,331
80
293
426

answered Aug 31 '11 at 14:44

Fenton

224,347
65
373
385

score 3 · Answer 3 · answered Aug 31 '11 at 14:44

SELECT P.ID, P.NAME, P.SEO_NAME, CI.City, R.Region, C.Country
FROM PROPERTIES P 
LEFT JOIN Cities CI ON P.CITY_ID = CI.CityId
LEFT JOIN Regions R ON P.REGION_ID = R.RegionID
LEFT JOIN Countries C ON P.COUNTRY_ID = C.CountryId 
**INNER JOIN PROPERTY_PHOTOS PP ON P.ID = PP.PROPERTY_ID**
WHERE APPROVED = '1' AND REGION_ID = '5400' 
ORDER BY RAND() LIMIT 1;

score 3 · Answer 4 · answered Aug 31 '11 at 14:44

3

You will need an "INNER JOIN".

answered Aug 31 '11 at 14:44

feathj

2,909
2
21
22

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

One more JOIN, but not LEFT

SELECT ...
FROM PROPERTIES P
...
INNER JOIN PROPERTY_PHOTOS PP ON P.ID = PP.PROPERTY_ID

or just

SELECT ...
FROM PROPERTIES P
...
JOIN PROPERTY_PHOTOS PP ON P.ID = PP.PROPERTY_ID

because they are the same.

edited May 23 '17 at 11:59

Community

1
1

answered Aug 31 '11 at 14:43

abatishchev

95,331
80
293
426

What's the point of this? First comment on my answer that it's not correct, and then post the exact same thing? – Tom Knapen Aug 31 '11 at 14:51
abatischev was actually the first answer I tried, and it immediately worked. OF COURSE it is that simple. I was just looking at this an entirely different way. Thank you. – DaveL Aug 31 '11 at 14:55

score -1 · Answer 6 · answered Aug 31 '11 at 14:42

Note: This query doesn't actually include a photos table, but I will assume you are joining it as PP

SELECT P.ID, P.NAME, P.SEO_NAME, CI.City, R.Region, C.Country
FROM PROPERTIES P 
LEFT JOIN Cities CI ON P.CITY_ID = CI.CityId
LEFT JOIN Regions R ON P.REGION_ID = R.RegionID
LEFT JOIN Countries C ON P.COUNTRY_ID = C.CountryId 
WHERE APPROVED = '1' AND REGION_ID = '5400' AND PP.PROPERTY_ID IS NOT NULL
ORDER BY RAND() LIMIT 1;

Select only records with linked records

6 Answers6