Professional Web Applications Themes

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

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 ...

  1. #1

    Default 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

    Steve Guest

  2. #2

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

    On 22 Jun, 10:35, Steve <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.

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

    Captain Guest

  3. #3

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

    On 22 Jun, 11:05, Captain Paralytic <com> wrote:
     

    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

    Steve Guest

  4. #4

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

    On 22 Jun, 15:01, Steve <com> wrote: 
    >
    > 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[/ref]

    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.

    Captain Guest

  5. #5

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

    On 22 Jun, 15:48, Captain Paralytic <com> wrote:
     


    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 Guest

  6. #6

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


    Steve wrote: 
    >
    >
    > 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[/ref]

    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

    strawberry Guest

Similar Threads

  1. Replies: 4
    Last Post: July 30th, 02:02 AM
  2. multi table select
    By Chuck Anderson in forum MySQL
    Replies: 3
    Last Post: February 10th, 06:39 AM
  3. Multi select to individual records in a table
    By tdecoste in forum Coldfusion - Advanced Techniques
    Replies: 8
    Last Post: October 3rd, 07:40 PM
  4. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM
  5. Cant Bind a data from a multi-table Inner Join using C#
    By Gareth Stretch in forum ASP.NET General
    Replies: 0
    Last Post: June 26th, 12:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139