I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas in posting etiquette. I have read through the related posts, but either I'm not savvy enough to see the connection to my problem(very likely) or there doesn't seem to be an issue quite like mine.

My environment is MySQLi

My issue is that I'm using a JOINED query that isn't working as intended. The query is supposed to give me a result if it finds a category match, either on primary_cat, which is stored in the link's table, or via a secondary cat, which is stored in a linkcat table. The issue that I'm running into is that my query is showing duplicate(always 2) results if it is matched on the primary_cat, but properly shows a single result when matched on the secondary

    $q_urls = "
    SELECT shortenified_urls.*
    FROM shortenified_urls 
        LEFT JOIN linkcats 
            ON linkcats.link_id = shortenified_urls.id
            (shortenified_urls.primary_cat = $vu_cat_view 
            OR linkcats.cat_id = $vu_cat_view)
        AND shortenified_urls.is_social = '1'
        AND shortenified_urls.active = '1'
        ORDER BY date_added DESC

I have a screenshot of my problem, which can be found here: http://en.zimagez.com/zimage/screenshot-10162014-111452am.php

I have tried playing with a DISTINCT nested query(nested because I need all the data from the result, but can't seem to figure out how to get DISTINCT to do that without one), but I'm truly terribad at this and can't figure out the solution on my own.

Any help in getting this fixed would be greatly appreciated. Thanks for your time!


Since I could not comment, I had a similar issue earlier, and I found the solution in this answer. When joining, you should group the results in order to have only one row per result. Besides that, do you need all the columns of the table? It is always a good practice to select columns by their name, instead of select *

  • Just FYI, you shouldn't always group the results when joining tables. Only do that if it makes sense for the requirements. For instance, there are plenty of times where you'll use a join to lookup the human-readable version of a foreign-key reference; adding a group-by clause in this instance will only cause the server to do more work than is necessary. – Max Vernon Oct 13 '15 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.