[Resolved] SQL resultset with wrong field values for joined tables

ppowell

SELECT image.id, image.image_name, image.image_path, image.image_alt, image.image_creation_date, image.image_location_city, image.image_location_state, image.image_location_country, REPLACE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REVERSE(image.image_path), '/', 2)), '/', 1), 'images', NULL) AS album_name, album.album_author, album.album_description FROM image image LEFT JOIN album album ON lower(album.album_name) = lower(album_name) AND album_name IS NOT NULL LEFT JOIN album_section_type s ON s.id = album.album_section_type_id AND lower(s.album_section_type_name) = lower('image') GROUP BY image.image.id ORDER BY upper(image.image_name) ASC;

The following SQL query gives me a resultset where I should obtain the correct album_author and album_description based upon the field value of "album_name", however, every time I get literally the very first value found in the "album" table no matter what "album_name" is, thus, I get the wrong information all the time for "album_author" and "album_description" all throughout my resultset.

How can I fix this query to give me the correct album_author and album_description field values that pertain to the generated "album_name" field?

Thanx
Phil

bubblocity

I think it's in here somewhere.

FROM image image LEFT JOIN album album ON lower(album.album_name) = lower(album_name)

At first I thought the query should give you an error, because it seemed like "album_name" is an ambiuguous column.

REPLACE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(RE
VERSE(image.image_path), '/', 2)), '/', 1), 'images', NULL) AS album_name

Maybe it would be better to alias the above expression to some other name like "album_name2".

Then maybe try joining :

FROM image image LEFT JOIN album album ON lower(album.album_name) = lower(album_name2)

ppowell

It turned out to be SQL 101 problem for me. Criteria filtering in either the WHERE clause or the LEFT JOIN requires that the filtering be done on an actual column in the selected table(s). Turns out that

WHERE lower(album.album_name) = lower(album_name)

Wasn't comparing the field column to the alias but rather, the field column to itself.

Changed to:

		 $mainBin = substr(${$section . 'LocationPath'}, strlen($basePath) + 1, strlen(${$section . 'LocationPath'}));
		 $replaceStr = "REPLACE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REVERSE(${section}.${section}_path), '/', 2)), '/', 1), '$mainBin', NULL)";
	 $sql .= "LEFT JOIN album album ON lower(album.album_name) = lower($replaceStr) " .
			      " AND $replaceStr IS NOT NULL ";

And it worked perfectly!

Phil