Need help combining two queries

[schwim]

Hi there everyone!

I've got a problem combining two queries. The situation is this. I've got a list of links and I want to allow the user to get a result list of all, only unread, all in a particular category and then only unread in a particular category.

My query for all:
 

/* No filtering and no unread */
$q_urls = "SELECT * FROM shortenified_urls WHERE is_social = '1' AND active = '1' ORDER BY date_added DESC";

My query for in a particular category:
 

/* All links in a particular category */
$q_urls = "
SELECT * FROM shortenified_urls
WHERE shortenified_urls.primary_cat = $vu_cat_view
OR EXISTS (
SELECT link_id FROM linkcats
WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view
)
AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
ORDER BY date_added DESC"
;

My query for Unread in all cats:
 

/* Unread only for all categories */
$q_urls = "SELECT * FROM shortenified_urls
WHERE shortenified_urls.date_added > $vu_mark_as_read
AND NOT EXISTS (
SELECT lid FROM linkviews
WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
)
AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
ORDER BY date_added DESC"
;

These seem to be working well. It's the next one that isn't working for me. I tried to combine the "All in a particular category" and "Unread in all categories" using the two above as starting points. The result, however, seems to be that I'm getting all in a particular category. It seems to be ignoring the unread status.
 

/* Unread links in a particular category */
$q_urls = "
SELECT * FROM shortenified_urls
WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read
OR EXISTS (
SELECT link_id FROM linkcats
WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view
)
AND NOT EXISTS (
SELECT lid FROM linkviews
WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
)
AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
ORDER BY date_added DESC"
;

I'm not smart enough to know why it's not working, but it seems to me like I need to somehow bracket the first two parts containing the category retrieval to make this work right. The only problem is I don't know how to do that.

Any help on how to get this to work would be greatly appreciated!

[schwim]

Well, my thought of bracketing the first two didn't quite work out as I expected:

    $q_urls = "
    SELECT * FROM shortenified_urls
    WHERE EXISTS (SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read
    OR EXISTS (
        SELECT link_id FROM linkcats
        WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view
    ))
    AND NOT EXISTS (
        SELECT lid FROM linkviews
        WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
    )
    AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
    ORDER BY date_added DESC"
    ;

That seems to give me unread, but in any category.  Kind of the opposite of my first attempt.

[schwim]

I've found a bit more out about my problem.

If the Primary cat is the category matched, it shows regardless of whether it's marked as read.  If, however, it's a secondary category that the match was made on, it disappears properly when it's viewed.

    $q_urls = "
    SELECT * FROM shortenified_urls
    WHERE EXISTS (SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read // This part is persisting in spite of read status
    OR EXISTS (
        SELECT link_id FROM linkcats
        WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view // This part is honoring whether it's been read or not.
    ))
    AND NOT EXISTS (
        SELECT lid FROM linkviews
        WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
    )
    AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
    ORDER BY date_added DESC"
    ;

I would love some insight on what I'm doing wrong, but am plugging away.