Joins versus Sub Selects - converting multi table sub select to join query.

I have created this query for an RSS Reader/Aggregator I am working on
which lists the Id of the Feed and the number of items within the feed
that the user (MemberID) has yet to read.

select Feeds.Id as FeedID,NewsItems.Id as Count from Feeds
inner join FeedItemLink on Feeds.Id=FeedItemLink.FeedID
inner join NewsItems on NewsItems.Id=FeedItemLink.ItemID
where (NewsItems.Id NOT IN
(select MemberItemLink.ItemID
From MemberItemLink
where MemberItemLink.MemberID=8))
or
(NewsItems.Id NOT IN (select MemberItemLink.ItemID
from MemberItemLink
where (MemberItemLink.Status&4=4 or MemberItemLink.Status&1=1 and
MemberItemLink.MemberID=8)))
group by Feeds.Id

To give a bit of background: Feed descriptions are stored in a table
called Feeds. News Items are stored in a table called NewsItems. There
is a linking table allowing many-to-many relationships between Feeds
and NewsItems.

User activity on particular items is stored in a table called
MemberItemLink (this can include having read the item (bit 1 in a
Status field is set), saving the item (bit 2 in Status field is set)
or deleting the item (bit 4 is set). Consequently the status of unread
can be indicated by: the user hasn't done anything to the news item
(so it will not have an entry at all in the MemberItemLink), the
member has done something but not read it e.g. saved it and marked it
as unread (bit 1 unset, bit 2 set). If the user has deleted it (bit 4
is set) then it is not counted as unread even if it IS unread.

I tried using a join query:

SELECT FeedItemLink.FeedID,Count(NewsItems.Id) as Count
FROM NewsItems inner JOIN FeedItemLink on
NewsItems.ID=FeedItemLink.ItemID
inner join FeedMemberLink on FeedItemLink.FeedID=FeedMemberLink.FeedID
left join MemberItemLink on NewsItems.Id=MemberItemLink.ItemID
where (!(MemberItemLink.Status & 4) OR MemberItemLink.Status is null)
and ( MemberItemLink.Status &1 = 0 or MemberItemLink.Status is null)
and FeedMemberLink.MemberID=8
Group by FeedItemLink.FeedID

but it didn't seem to matter which MemberID was specified. The results
were the same.

Would this run quicker as a Join query rather than an sub select. Any
thoughts about how this would look as a join query?


Many thanks, in anticipation,
Steve

On 22 Jun, 10:35, Steve <StevePBurg...@gmail.com> wrote:
> I have created this query for an RSS Reader/Aggregator I am working on
> which lists the Id of the Feed and the number of items within the feed
> that the user (MemberID) has yet to read.
>
> select Feeds.Id as FeedID,NewsItems.Id as Count from Feeds
> inner join FeedItemLink on Feeds.Id=FeedItemLink.FeedID
> inner join NewsItems on NewsItems.Id=FeedItemLink.ItemID
> where (NewsItems.Id NOT IN
> (select MemberItemLink.ItemID
> From MemberItemLink
> where MemberItemLink.MemberID=8))
> or
> (NewsItems.Id NOT IN (select MemberItemLink.ItemID
> from MemberItemLink
> where (MemberItemLink.Status&4=4 or MemberItemLink.Status&1=1 and
> MemberItemLink.MemberID=8)))
> group by Feeds.Id
>
> To give a bit of background: Feed descriptions are stored in a table
> called Feeds. News Items are stored in a table called NewsItems. There
> is a linking table allowing many-to-many relationships between Feeds
> and NewsItems.
>
> User activity on particular items is stored in a table called
> MemberItemLink (this can include having read the item (bit 1 in a
> Status field is set), saving the item (bit 2 in Status field is set)
> or deleting the item (bit 4 is set). Consequently the status of unread
> can be indicated by: the user hasn't done anything to the news item
> (so it will not have an entry at all in the MemberItemLink), the
> member has done something but not read it e.g. saved it and marked it
> as unread (bit 1 unset, bit 2 set). If the user has deleted it (bit 4
> is set) then it is not counted as unread even if it IS unread.
>
> I tried using a join query:
>
> SELECT FeedItemLink.FeedID,Count(NewsItems.Id) as Count
> FROM NewsItems inner JOIN FeedItemLink on
> NewsItems.ID=FeedItemLink.ItemID
> inner join FeedMemberLink on FeedItemLink.FeedID=FeedMemberLink.FeedID
> left join MemberItemLink on NewsItems.Id=MemberItemLink.ItemID
> where (!(MemberItemLink.Status & 4) OR MemberItemLink.Status is null)
> and ( MemberItemLink.Status &1 = 0 or MemberItemLink.Status is null)
> and FeedMemberLink.MemberID=8
> Group by FeedItemLink.FeedID
>
> but it didn't seem to matter which MemberID was specified. The results
> were the same.
>
> Would this run quicker as a Join query rather than an sub select. Any
> thoughts about how this would look as a join query?
>
> Many thanks, in anticipation,
> Steve

The LEFT JOIN equivalent of a sub-query's "NOT IN" is "AND somefield
IS NULL"
Since you are doing OR with your IS NULL condition, I suspect that may
be the problem.

An yes, using a JOIN will be MUCH quicker than using a sub-query.

On 22 Jun, 11:05, Captain Paralytic <paul_laut...@yahoo.com> wrote:

> The LEFT JOIN equivalent of a sub-query's "NOT IN" is "AND somefield
> IS NULL"
> Since you are doing OR with your IS NULL condition, I suspect that may
> be the problem.

Many thanks for the help.

I tried changing to an AND but it didn't return any results at all.

Anyone got any ideas?

Steve

On 22 Jun, 15:01, Steve <StevePBurg...@gmail.com> wrote:
> On 22 Jun, 11:05, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > The LEFT JOIN equivalent of a sub-query's "NOT IN" is "AND somefield
> > IS NULL"
> > Since you are doing OR with your IS NULL condition, I suspect that may
> > be the problem.
>
> Many thanks for the help.
>
> I tried changing to an AND but it didn't return any results at all.
>
> Anyone got any ideas?
>
> Steve

I note in your original query that the second subquery contains
records where 'MemberItemLink.Status&4=4' regardless of the MemberID,
but I cannot see this in the JOIN query.
Also the criteria 'MemberItemLink.Status&4=4' seems to have changed in
the JOIN query to
!(MemberItemLink.Status & 4)

Since you have 2 separate NOT IN criterias in the subquery version, I
would expect 2 LEFT JOINS in the JOIN style one.

The problem is that the original query is a bit of a mess and there is
no explanation of what all the values mean, so it is not easy to
advise you on what the correct JOIN based query should be. For
instance it is not clear why 'MemberItemLink.Status&4=4' does not
depend on memberid?

If you can post the SQL export of the tables with a few sample records
giving the expected output, plus how each criteria is relevant, we
might be able to help further.

On 22 Jun, 15:48, Captain Paralytic <paul_laut...@yahoo.com> wrote:

> If you can post the SQL export of the tables with a few sample records
> giving the expected output, plus how each criteria is relevant, we
> might be able to help further.


Hi there. Thanks for the post.

I thought I had explained all of the relationships in my original post
and how the values were relevant (i.e. what &4 means etc). In my
ignorance I thought that &4 and &4=4 were the same (they certainly
have the same effect).

The sub select query works and gives the resultset I want which is the
FeedID and the number of unread NewsItems so basically my request is -
can someone help me to convert this subselect query

select Feeds.Id as FeedID,NewsItems.Id as Count from Feeds
inner join FeedItemLink on Feeds.Id=FeedItemLink.FeedID
inner join NewsItems on NewsItems.Id=FeedItemLink.ItemID
where (NewsItems.Id NOT IN
(select MemberItemLink.ItemID
From MemberItemLink
where MemberItemLink.MemberID=8))
or
(NewsItems.Id NOT IN (select MemberItemLink.ItemID
from MemberItemLink
where (MemberItemLink.Status&4=4 or MemberItemLink.Status&1=1 and
MemberItemLink.MemberID=8)))
group by Feeds.Id

into a more efficient and quicker Join based query.

Best regards,

Steve

Steve wrote:
> On 22 Jun, 15:48, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > If you can post the SQL export of the tables with a few sample records
> > giving the expected output, plus how each criteria is relevant, we
> > might be able to help further.
>
>
> Hi there. Thanks for the post.
>
> I thought I had explained all of the relationships in my original post
> and how the values were relevant (i.e. what &4 means etc). In my
> ignorance I thought that &4 and &4=4 were the same (they certainly
> have the same effect).
>
> The sub select query works and gives the resultset I want which is the
> FeedID and the number of unread NewsItems so basically my request is -
> can someone help me to convert this subselect query
>
> select Feeds.Id as FeedID,NewsItems.Id as Count from Feeds
> inner join FeedItemLink on Feeds.Id=FeedItemLink.FeedID
> inner join NewsItems on NewsItems.Id=FeedItemLink.ItemID
> where (NewsItems.Id NOT IN
> (select MemberItemLink.ItemID
> From MemberItemLink
> where MemberItemLink.MemberID=8))
> or
> (NewsItems.Id NOT IN (select MemberItemLink.ItemID
> from MemberItemLink
> where (MemberItemLink.Status&4=4 or MemberItemLink.Status&1=1 and
> MemberItemLink.MemberID=8)))
> group by Feeds.Id
>
> into a more efficient and quicker Join based query.
>
> Best regards,
>
> Steve

Can you adapt this to your pupose?:

SELECT * FROM table_a a
LEFT JOIN table_b b
ON a.id = b.a_id
WHERE b.id IS NULL