CODEWITHSUNDEEP
mysqlleft-join
necross
necross

Reputation: 157

MySQL Join with the wrong results

I have a quick question regarding a JOIN. Here is my query:

SELECT
products.name AS name,
products.last_scanned AS last_scanned,
products.last_scanned AS last_changed,

ph.price AS price,
ph.mirror_price AS mirror_price,
ph.change_date AS change_date,

ph.dioPlusZapPrice AS dioPlusZapPrice,
ph.dioPlusSitePrice AS dioPlusSitePrice,

ph.inkDepotZapPrice AS inkDepotZapPrice,
ph.inkDepotSitePrice AS inkDepotSitePrice,

ph.misradiaZapPrice AS misradiaZapPrice,
ph.misradiaSitePrice AS misradiaSitePrice,

ph.netPrintZapPrice AS netPrintZapPrice,
ph.netPrintSitePrice AS netPrintSitePrice,

img1.image_blob AS dioPlusImage,
img2.image_blob AS inkDepotImage,
img3.image_blob AS misradiaImage,
img4.image_blob AS netPrintImage

FROM
price_history ph

LEFT JOIN products ON products.productID=ph.productID

LEFT JOIN imagedata img1 ON img1.id = ph.imagename_dioPlus
LEFT JOIN imagedata img2 ON img2.id = ph.imagename_inkDepot
LEFT JOIN imagedata img3 ON img3.id = ph.imagename_misradia
LEFT JOIN imagedata img4 ON img4.id = ph.imagename_netPrint

ORDER BY ph.change_date DESC

The result of this query is:

Brother MFC-L2700DW|2015-06-26 15:47|2015-06-26 15:47|949|949|2015-06-26 15:47|949|949|969|999|0|0|0|0|[BLOB - 217.9 KiB]|[BLOB - 217.9 KiB]|NULL|NULL

which displays the same blob.

This is my imagadata table:

+----+-----------+------------+---------------------------------------+
| ID | image_blob         | ID | imagename                            |
+----+-----------+-----+----------------------------------------------+
|  1 | [BLOB - 19.5 KiB]  |  1 | a5052c6e-1120-4ece-994d-2cdac7e7baa4 |
|  2 | [BLOB - 217.9 KiB] |  2 | 1ed6f935-a44a-4f07-9c6b-fa51da5d4bdb |
|  3 | [BLOB - 217.9 KiB] |  3 | 8acced70-17ae-4a65-8466-6b910c674869 |
+----+-----------+----+-----------------------------------------------+

As you can see from the image, This is wrong since the ID is different in imagedata.

Upvotes: 0

Views: 29

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You have 2 rows with identical values:

2 - [BLOB - 217.9 KiB]; 
3 - [BLOB - 217.9 KiB];

So if row in price_history table containt such data:

imagename_dioPlus imagename_inkDepot
2                 3

it will output

[BLOB - 217.9 KiB] [BLOB - 217.9 KiB]

no matter that they have different ids. Trouble is they have same descriptions.

Upvotes: 1

Related Questions