I have a question I am looking for proper way to say it...I currently have a formula:
SELECT *
FROM useritems JOIN (iteminfo)
ON (iteminfo.id=useritems.itemid)
WHERE active='Y' AND userid!='$userid'
ORDER BY itemid DESC LIMIT 40";
That is designed to show all the items that a USER(A) does not have.
Suppose USER A has item: A, B, C, D, E, F,
In theory the formula will show G-Z only...
My problem is that if USER B has Item A, B, C, those items show up in this list because isn't not that USER A has them...USER B has them (therefor it's not user A). How do I keep those items from showing up in USER A's FEED because USER B has them???
What you need to do is get all possible items that exist in your itemInfo table then find all the ones that A doesnt have by using a LEFT JOIN and selecting the ones that come out as NULL
SELECT *
FROM useritems AS 'u'
LEFT JOIN (SELECT * FROM itemInfo GROUP BY item) AS 'i'
ON u.id=i.itemid
WHERE active='Y' AND userid='$userid' AND i.item IS NULL
ORDER BY itemid DESC LIMIT 40;
Your query is showing all the items held by users other than A. You can use a subquery to return all the items held by the user, and then exclude them with NOT IN.
SELECT *
FROM iteminfo
WHERE id NOT IN (
SELECT itemid
FROM useritems
WHERE active = 'Y' AND userid = '$userid'
)
ORDER BY id DESC LIMIT 40
Or the equivalent LEFT JOIN
SELECT i.*
FROM iteminfo AS i
LEFT JOIN useritems AS u ON i.id = u.itemid AND u.active = 'Y' AND u.userid = '$userid'
WHERE u.itemid IS NULL
ORDER BY i.id DESC LIMIT 40
This looks like a good case for a left join. I'd do something like:
SELECT iteminfo.itemid
FROM iteminfo
LEFT JOIN useritems ON (iteminfo.id=useritems.itemid) AND (useritems.userid = '$userid')
WHERE active='Y' AND userid IS NULL
ORDER BY itemid DESC LIMIT 40";
This is assuming that userid is in the useritems table. Left joining on the userid will grab one row for each of the iteminfo entries, and checking whether they're null in the WHERE will give you all iteminfo entries where the userid wasn't a match.