I have been studying and experimenting about this all weekend. But I can not make it work. I have tried GROUP BY, CONCAT, and DISTINCT - but I can not get the whole query. Can anyone help me sort this out?
What I want: I only want to view each article once, even if it has been selected by multiple users.
Here is my query:
<?php
$WADAbuckets = new WA_MySQLi_RS("WADAbuckets",$bucketListHub,0);
$WADAbuckets->setQuery("SELECT bucBuckets.buc_id, bucBuckets.buc_name, bucBuckets.buc_description, bucBuckets.buc_minAge, bucBuckets.buc_maxAge, bucCategories_translation.cattra_name AS bucCategories_translation_cattra_name, bucBudget.bud_name AS bucBudget_bud_name, bucCountry_translation.coutra_name AS bucCountry_translation_coutra_name, bucCity.cit_name AS bucCity_cit_name, bucDifficulty.dif_ID AS bucDifficulty_dif_ID, bucDifficulty.dif_name AS bucDifficulty_dif_name, bucBucketCollections.*, bucCollections.col_name, bucPercitipatingDegree.per_name, bucImages.ima_link, bucMylist.* FROM bucBuckets LEFT OUTER JOIN bucCategories_translation ON bucBuckets.buc_cattraID = bucCategories_translation.cattra_id LEFT OUTER JOIN bucBudget ON bucBuckets.buc_budID = bucBudget.bud_id LEFT OUTER JOIN bucCountry_translation ON bucBuckets.buc_couCode = bucCountry_translation.coutra_couCode LEFT OUTER JOIN bucCity ON bucBuckets.buc_citID = bucCity.cit_id LEFT OUTER JOIN bucDifficulty ON bucBuckets.buc_difID = bucDifficulty.dif_ID LEFT OUTER JOIN bucBucketCollections ON bucBuckets.buc_id = bucBucketCollections.collectionBucketBucket_ID LEFT OUTER JOIN bucCollections ON bucBucketCollections.collectionBucketCollection_ID = bucCollections.col_id LEFT OUTER JOIN bucPercitipatingDegree ON bucBuckets.buc_perID = bucPercitipatingDegree.per_ID LEFT OUTER JOIN bucImages ON bucBuckets.buc_id = bucImages.ima_bucID LEFT OUTER JOIN bucMylist ON bucBuckets.buc_id = bucMylist.myl_bucID ORDER BY bucBuckets.buc_name ASC");
$WADAbuckets->execute();
?>
------
Then a message to WebAssist: There seems to be a bug in QueryBuilder when using DISTINCT. I get an error message:
Error Inspecting SQL
• table alias not found: DISTINCT bucMylist
These values will be automatically removed the next time you use the visual editor or advanced tab to edit your SQL.
I get this error after writing this:
SELECT GROUP_CONCAT (DISTINCT bucMylist.myl_bucID) AS myl_bucID, bucBuckets.buc_id, bucBuckets.buc_name
FROM bucMylist INNER JOIN bucBuckets ON bucMylist.myl_bucID = bucBuckets.buc_id
GROUP BY bucMylist.myl_bucID