Amend SQL MAX Query to Find Corresponding Field's Value
I have the following query:
$imgDimensions_query = "SELECT MAX(imgWidth) maxWidth, MAX(imgHeight) maxHeight FROM (
SELECT imgHeight, imgWidth FROM primary_images WHERE imgId=$imgId
UNION
SELECT imgHeight, imgWidth FROM secondary_images WHERE primaryId=$imgId) as MaxHeight";
It's working fantastic, but I would like to know how I can find the va开发者_开发技巧lue of the column imgId
, as well as the table name, for both the maxWidth
and maxHeight
values?
The reason I want this is I need to know if the maxWidth
and maxHeight
values belong to the same item in the database.
I'm wondering if this is possible by amending the current SQL query?
What would be perfect is if, along with querying the maxWidth
and maxHeight
values, a boolean could be set up to output true
if both the maxWidth
and maxHeight
belong to the same entry (at least once).
I'm thinking, since the image data in primary_images
is unique from the data in secondary_images
(and vice versa), a boolean could be set up in each of the queries, and as long as one true
exists, true
is output. Does that make sense? Is that possible?
I have managed to put together a second query which uses the values of maxWidth
and maxHeight
from the first query to output the number of images in a specific set that hold both values. All I really care about is if there is or if there isn't one or more images that meet the above requirement, so again, a boolean would be better than the total number. If you have an idea of how to amend the following to show a boolean instead of the number of results, let me know!
I have been reassured that with a maximum number of entries in both tables being under 1000, using two queries instead of one shouldn't cause a hit to speed. If you think so as well, and if combining these queries into one is ridiculous, then let me know that as well.
The second query:
$haveDimensions_query = "SELECT sum(rows) AS total_rows FROM (
SELECT count(*) AS rows FROM primary_images WHERE imgId = $imgId and imgWidth = $maxImageWidth and imgHeight = $maxImageHeight
UNION ALL
SELECT count(*) AS rows FROM secondary_images WHERE primaryId = $imgId and imgWidth = $maxImageWidth and imgHeight = $maxImageHeight
) as union_table";
[Original answer is below - this should be better]
I think I misunderstood your question. The following query should give a result set that includes one row for each image that has greatest height or greatest width compared to all images, and it will have the ID and table name in the row along with the height and width.
Since the maximum height and width may not be from the same image, there can't necessarily be just one row in the result with a single image ID and table source.
I hope this is at least closer to what you were looking for.
SELECT
imgId,
tName,
imgWidth,
imgHeight
FROM (
SELECT imgId, 'Primary' tName, imgHeight, imgWidth
FROM primary_images WHERE imgId=$imgId
UNION
SELECT imgId, 'Secondary' tName, imgHeight, imgWidth
FROM secondary_images WHERE primaryId=$imgId
) as T
WHERE
(T.imgHeight >= (SELECT MAX(imgHeight) FROM primary_images)
AND
T.imgHeight >= (SELECT MAX(imgHeight) FROM secondary_images)
)
OR
(T.imgWidth >= (SELECT MAX(imgWidth) FROM primary_images)
AND
T.imgWidth >= (SELECT MAX(imgWidth) FROM secondary_images)
)
-- ORIGINAL ANSWER BELOW --
Try something like this:
SELECT
imgId,
tName,
MAX(imgWidth) maxWidth,
MAX(imgHeight) maxHeight FROM (
SELECT imgId, 'Primary' tName, imgHeight, imgWidth
FROM primary_images WHERE imgId=$imgId
UNION
SELECT imgId, 'Secondary' tName, imgHeight, imgWidth
FROM secondary_images WHERE primaryId=$imgId
) as T
GROUP BY imgId, tName;
(MaxHeight wasn't a good alias name for the derived table, because it isn't a table of maximum heights. I changed it to T.)
You could do this in two queries, it's probably easier to read that way. First query is what you have to get to get the max height and width.
You then can issue the second query which looks like:
SElECT primaryId FROM ( SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images UNION SELECT imgHeight, imgWidth, primaryId FROM secondary_images ) as union_table WHERE imgWidth = [maxWidth] and imgHeight = [maxHeight];
Where [maxWidth]
and [maxHeight]
are the two values that you get from previous query. If they belong to the same image ID, you will have query result greater than zero, if not this query will have no result.
If you need to know which id is belong to which table, you could create artificial column (e.g. source) and your query would become:
SElECT primaryId, source FROM ( SELECT imgHeight, imgWidth, imgId AS primaryId, 1 as source FROM primary_images UNION SELECT imgHeight, imgWidth, primaryId, 2 as source FROM secondary_images ) as union_table WHERE imgWidth = [maxWidth] and imgHeight = [maxHeight];
Note that there is now artificial column called source. So if your result from query is
primaryId source 4 1 4 2 5 2
You know that imgId
4 from primary_images
as well as
primaryId
4,5 from secondary_images
match with the max height and max width of the previous query
And finally, if you just want to know whether there is image that is matching or not, per our comments and discussion below, you could do:
SElECT count(*) AS imgCount FROM ( SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images UNION ALL SELECT imgHeight, imgWidth, primaryId FROM secondary_images ) as union_table WHERE primaryId = $imgId and imgWidth = [maxWidth] and imgHeight = [maxHeight];
Where imgCount
will be zero if there is no matching image or greater than zero otherwise
Here's what I've got so far. It looks (and probably is) heavily suboptimal. Nevertheless, I'm posting it primarily to present the general idea. Hopefully, someone might develop it so as to make the result more efficient:
SELECT
m.maxWidth,
m.maxHeight,
(u.imgWidth IS NOT NULL) AS OneImageHasBoth
FROM (
SELECT
MAX(imgWidth) AS maxWidth,
MAX(imgHeight) AS maxHeight
FROM (
SELECT imgWidth, imgHeight
FROM primary_images
UNION
SELECT imgWidth, imgHeight
FROM secondary_images
) u
) m
LEFT JOIN (
SELECT imgWidth, imgHeight
FROM primary_images
UNION
SELECT imgWidth, imgHeight
FROM secondary_images
) u ON m.maxWidth = u.imgWidth AND m.maxHeight = u.imgHeight
Obviously, one way to optimise this could be to store the result of the repeating union into a temporary table. I'm not sure if your particular environment allows you to issue multi-statement queries, but if it does, that would definitely help to speed up the query.
Your idea of finding the results for each table separately and then combining them is actually not bad. But I think the logic behind combining the results should be a bit more complex. One obvious example would be, the result for one table contains true
and the other false
but the latter has bigger width and height, so returning true
would be incorrect. Or consider this example:
Suppose, the result for primary_images
is
maxWidth maxHeight OneImageHasBoth
-------- --------- ---------------
1152 864 true
and for secondary_images
it's
maxWidth maxHeight OneImageHasBoth
-------- --------- ---------------
1280 800 true
Both tables have images with both attributes maximal, but it is clear that if the query was applied to the unioned set, OneImageHasBoth
would be false
.
So, as you can see, combining the two results should be more intricate than merely relying on the presence of true
in one of them.
Here's my attempt at implementing the method:
SELECT
CASE WHEN p.maxWidth > s.maxWidth THEN p.maxWidth ELSE s.maxWidth END AS maxWidth,
CASE WHEN p.maxHeight > s.maxHeight THEN p.maxHeight ELSE s.maxHeight END AS maxHeight,
(
p.maxWidth >= s.maxWidth AND p.maxHeight >= s.maxHeight AND p.OneImageHasBoth OR
p.maxWidth <= s.maxWidth AND p.maxHeight <= s.maxHeight AND s.OneImageHasBoth
) AS OneImageHasBoth
FROM (
SELECT DISTINCT
m.maxWidth, m.maxHeight,
(i.imgWidth IS NOT NULL) AS OneImageHasBoth
FROM (
SELECT
MAX(imgWidth) AS maxWidth,
MAX(imgHeight) AS maxHeight
FROM primary_images
) m
LEFT JOIN primary_images i ON m.maxWidth = i.imgWidth AND m.maxHeight = i.imgHeight
) p
CROSS JOIN (
SELECT DISTINCT
m.maxWidth, m.maxHeight,
(i.imgWidth IS NOT NULL) AS OneImageHasBoth
FROM (
SELECT
MAX(imgWidth) AS maxWidth,
MAX(imgHeight) AS maxHeight
FROM secondary_images
) m
LEFT JOIN secondary_images i ON m.maxWidth = i.imgWidth AND m.maxHeight = i.imgHeight
) s
精彩评论