I am trying to create a join that would in effect join the following two queries. As it stands, I have to issue one query to the database and do some elaborate functions inside PHP with the in_array() function, then perform the second query for each of the results in the first query. I would like to be able to just select what I want and omit the half-baked PHP scripting. So... a challenge:
The first query selects details for individual items, the second query selects a corresponding photograph for that item. A map of the table structure is below as well as a query that does something simular to what I want to do below.
SELECT
listings.id_num,
listings.mls,
listings.status,
listings.title
FROM listings
WHERE listings.status != 4
ORDER BY listings.mls DESC
SELECT photos.filename
FROM photos, photos_ind
WHERE photos.size = 3
AND photos.des = 1
AND photos_ind.filename = photos.filename
TABLES:
listings
- id_num
- mls
- status
- title
photos
- filename
- size
- des
photos.ind
- id_num
- filename
The way it is set up now I have to query the "photos.ind" table to reference which photosgraphs belong to which items in the database using "id_num" as a reference.
I was able to write the following query successfully that will go and count the number of photographs for a particular "id_num", but now I just want to return the filename field and I am stumped.
Any help would be greatly appreciated. Thanks in advance.
SELECT listings.id_num,
listings.mls,
listings.status,
COUNT(photos_ind.id_num) as num_photos
FROM listings
LEFT JOIN photos_ind
ON listings.id_num = photos_ind.id_num
GROUP BY listings.id_num
ORDER BY mls DESC
The first query selects details for individual items, the second query selects a corresponding photograph for that item. A map of the table structure is below as well as a query that does something simular to what I want to do below.
SELECT
listings.id_num,
listings.mls,
listings.status,
listings.title
FROM listings
WHERE listings.status != 4
ORDER BY listings.mls DESC
SELECT photos.filename
FROM photos, photos_ind
WHERE photos.size = 3
AND photos.des = 1
AND photos_ind.filename = photos.filename
TABLES:
listings
- id_num
- mls
- status
- title
photos
- filename
- size
- des
photos.ind
- id_num
- filename
The way it is set up now I have to query the "photos.ind" table to reference which photosgraphs belong to which items in the database using "id_num" as a reference.
I was able to write the following query successfully that will go and count the number of photographs for a particular "id_num", but now I just want to return the filename field and I am stumped.
Any help would be greatly appreciated. Thanks in advance.
SELECT listings.id_num,
listings.mls,
listings.status,
COUNT(photos_ind.id_num) as num_photos
FROM listings
LEFT JOIN photos_ind
ON listings.id_num = photos_ind.id_num
GROUP BY listings.id_num
ORDER BY mls DESC