SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How do I generate a list from 2 Tables based on conditions in both

    Hi,

    I want to generate a list from 2 Tables based on some conditions in each table.
    In Table 1 the primary Key is user_id
    In Table 2 this user_id is the foreign Key.
    So user_is is the field that these 2 tables have in common.

    Now I want to generate a list which is list of articles in Table 1 but only if these articles based on user_id have no password in Table 2.

    I have tired this but it is not producing correct results:

    Code MySQL:
    SELECT article_id, blog_name, title FROM articles, blogs
    WHERE articles.user_id = xyz AND posted2blog = 0 AND articles.user_id = blogs.user_id
    AND (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL) ORDER BY article_id DESC LIMIT 10;

    The problem is that it is pulling up articles from those Blogs that do have a password which it should not as per: (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)

    What is even worst, or an indication of the problem is, it is indicating, via "blog_name", that it has pulled an article which belongs to a Blog that does not have a password but that article belongs to another Blog all together which does have password!

    Thanks for your assistance.

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you show a "CREATE TABLE" for the two tables?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I cannot do that.
    These are commercial public Tables so for various security reasons we could not do that.

    I hope you can find the answer without showing "CREATE TABLE".

    Quote Originally Posted by SpacePhoenix View Post
    Can you show a "CREATE TABLE" for the two tables?

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    I assume that title is in the articles table, try this:

    Code SQL:
    SELECT
          articles.article_id
        , articles.title
        , blogs.blog_name
    FROM
        articles
    INNER JOIN
        blogs
    ON 
        articles.user_id = blogs.user_id
    WHERE
        articles.user_id = xyz
    AND
        articles.posted2blog = 0
    AND
        (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
    ORDER BY
        articles.article_id DESC
    LIMIT
        10;
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If your join matches articles and blogs that don't have anything to do with eachother, it means you need another field (instead of, or in addition to 'user_id') to join the two tables (blog_id for example).

    By the way, user_id shouldn't be the primary key of the 'articles' table. Or does each user write only one article?

  6. #6
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Still the same error is there.
    That is the command which you suggested below is still pulling those articles that belong to blogs which have a password, that means this is not true for them:
    (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
    so no articles from these blogs should be listed but are being listed!

    Ayayayay!

    Regards,

    Quote Originally Posted by SpacePhoenix View Post
    I assume that title is in the articles table,
    try this:
    Code SQL:
    SELECT
          articles.article_id
        , articles.title
        , blogs.blog_name
    FROM
        articles
    INNER JOIN
        blogs
    ON 
        articles.user_id = blogs.user_id
    WHERE
        articles.user_id = xyz
    AND
        articles.posted2blog = 0
    AND
        (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
    ORDER BY
        articles.article_id DESC
    LIMIT
        10;

    Anoox search engine volunteer

    www.anoox.com

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    FYI, user_id is not the primary Key in articles table, the primary key for
    that table is article_id. FYI, The users Table and articles table are linked together via the user_id, where user_id is the primary key in users table.


    Quote Originally Posted by guido2004 View Post
    If your join matches articles and blogs that don't have anything to do with eachother, it means you need another field (instead of, or in addition to 'user_id') to join the two tables (blog_id for example).

    By the way, user_id shouldn't be the primary key of the 'articles' table. Or does each user write only one article?

    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    conditions on the blogs table belong in the ON clause and not the WHERE clause. Doing the latter effectively changes your join from a LEFT OUTER to an INNER join.

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I do not understand what you mean!
    Can you please write the actual code you have in mind that will address this problem?

    Regards,

    Quote Originally Posted by guelphdad View Post
    conditions on the blogs table belong in the ON clause and not the WHERE clause. Doing the latter effectively changes your join from a LEFT OUTER to an INNER join.

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I tried your new code and it is still not working
    That is it is still pulling article from those blogs that have password which
    it is not supposed to as per
    AND
    (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)

    FYI, here is your new code:

    Code MySQL:
    SELECT
          articles.article_id
        , articles.title
        , blogs.blog_name
    FROM
        articles
    INNER JOIN
        blogs
    ON
        articles.user_id = blogs.user_id
    AND
        (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
    WHERE
        articles.user_id = xyz
    AND
        articles.posted2blog = 0
    ORDER BY
        articles.article_id DESC
    LIMIT
        10;

    Quote Originally Posted by SpacePhoenix View Post
    I assume that title is in the articles table, try this:

    Code SQL:
    SELECT
          articles.article_id
        , articles.title
        , blogs.blog_name
    FROM
        articles
    INNER JOIN
        blogs
    ON 
        articles.user_id = blogs.user_id
    WHERE
        articles.user_id = xyz
    AND
        articles.posted2blog = 0
    AND
        (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
    ORDER BY
        articles.article_id DESC
    LIMIT
        10;

    Anoox search engine volunteer

    www.anoox.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    In Table 1 the primary Key is user_id
    In Table 2 this user_id is the foreign Key.
    So user_is is the field that these 2 tables have in common.

    Now I want to generate a list which is list of articles in Table 1 but only if these articles based on user_id have no password in Table 2.
    i think you have that backwards

    the articles are in Table 2 and the password is in Table 1

    no wonder we're confused
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    No the articles are in Table 1 which is the articles table.
    The passwords are in Table 2 which is the Blog table that contains
    all sort of customization info about an article such as whether the blog
    that it belongs to is password protected.


    Quote Originally Posted by r937 View Post
    i think you have that backwards

    the articles are in Table 2 and the password is in Table 1

    no wonder we're confused

    Anoox search engine volunteer

    www.anoox.com

  13. #13
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    instead of us guessing, show us some sample rows of both tables and show us which ones do not work.

    and sorry yes my post earlier was not clear. i read your original post as needing a left join but you are using an inner join.


    also have you tried this:
    blogs.blog_passwd = ""
    as this with a space in it:
    blogs.blog_passwd = " "

    to make sure that isn't what is causing the incorrect rows to show up?

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would this help point you to a solution, where you make an inner join but restrict the resultant dataset based on you other 'password' condition?

    Code MySQL:
    SELECT article_id
             , blog_name
             , title
      FROM articles AS a
    INNER
       JOIN blogs AS b
          on b.user_id = a.user_id
         AND ( 
                  ( a.password = ''
                 OR
                    a.password = NULL
                  )
                )

    You may have to change the alias around because I too am confused about what is in each table.

    I hope this points you to a solution

    bazz

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by WorldNews View Post
    Hi,

    FYI, user_id is not the primary Key in articles table, the primary key for
    that table is article_id. FYI, The users Table and articles table are linked together via the user_id, where user_id is the primary key in users table.
    There is no users table in your query.
    If you give info about tables you don't use in your query, and don't tell anything about those that you do use, then I can't help you.

    I'm interested in how the users table and articles table are linked. I want to know how the articles table and the blogs table are linked.

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by IBazz View Post
    I hope this points you to a solution

    bazz
    Bazz, I'm almost certain his problem is due to the fact that he's joining the two tables on the wrong field. My guess is that the right field is 'blogid', not 'userid'

  17. #17
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    WorldNews, does the Blog table have an ID field and does the Articles table have both an ID field and also a BlogID field (the ID of the blog that it is a member of). Can an article feature in more than one blog?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  18. #18
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Bazz, I'm almost certain his problem is due to the fact that he's joining the two tables on the wrong field. My guess is that the right field is 'blogid', not 'userid'
    lol. I knew it couldn't be so simple when the rest of you hadn't found the solution.

    bazz

  19. #19
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by guido2004 View Post
    There is no users table in your query.
    If you give info about tables you don't use in your query, and don't tell anything about those that you do use, then I can't help you.

    I'm interested in how the users table and articles table are linked. I want to know how the articles table and the blogs table are linked.
    Oops, there is NOT missing:

    I'm NOT interested in how the users table and articles table are linked...



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
  •