Professional Web Applications Themes

Combing two simple select statements. There must be a way? - MySQL

Hi I have two select statements that work correctly. They are as follows: SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab AS pref ON user.id=pref.userid and pref.questionid = '20' SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab AS pref ON user.id=pref.userid WHERE pref.userid IS NULL The first simply returns all rows that have the same userid in the user and preferences table that also have the column "questionid" as a value of 20. The second just returns every row that doesn't have a matching userid in both tables. The problem is I desperately need to combine ...

  1. #1

    Default Combing two simple select statements. There must be a way?

    Hi

    I have two select statements that work correctly. They are as
    follows:

    SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab
    AS pref ON user.id=pref.userid and pref.questionid = '20'

    SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab
    AS pref ON user.id=pref.userid WHERE pref.userid IS NULL

    The first simply returns all rows that have the same userid in the
    user and preferences table that also have the column "questionid" as a
    value of 20.

    The second just returns every row that doesn't have a matching userid
    in both tables.

    The problem is I desperately need to combine these two statements in
    to ONE. It seems like it should be possible but I have tried about a
    100 different combination of joins and nested selects and can't get the
    result I want.

    Can anyone help me here? Thanks - Ukrbend

    ukr_bend@yahoo.com Guest

  2. #2

    Default Re: Combing two simple select statements. There must be a way?

    <ukr_bend> wrote in message
    news:1143157218.419817.99930e56g2000cwe.googlegro ups.com...
    > SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab
    > AS pref ON user.id=pref.userid and pref.questionid = '20'
    >
    > SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab
    > AS pref ON user.id=pref.userid WHERE pref.userid IS NULL
    >
    > [help me combine the two queries above]
    Does this do what you want?

    SELECT u.*
    FROM user_tab AS u LEFT OUTER JOIN user_pref_tab AS p
    ON u.id = p.userid AND p.questionid = '20'

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Combing two simple select statements. There must be a way?

    Thanks for the help Bill. Unfortunately that statement returns all the
    rows, even the ones that have a matching userid between the two tables.
    But again, thanks for the post.

    ukr_bend@yahoo.com Guest

  4. #4

    Default Re: Combing two simple select statements. There must be a way?


    <ukr_bend> wrote in message
    news:1143157218.419817.99930e56g2000cwe.googlegro ups.com...
    > Hi
    >
    > I have two select statements that work correctly. They are as
    > follows:
    >
    > SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab
    > AS pref ON user.id=pref.userid and pref.questionid = '20'
    >
    > SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab
    > AS pref ON user.id=pref.userid WHERE pref.userid IS NULL
    >
    > The first simply returns all rows that have the same userid in the
    > user and preferences table that also have the column "questionid" as a
    > value of 20.
    >
    > The second just returns every row that doesn't have a matching userid
    > in both tables.
    >
    > The problem is I desperately need to combine these two statements in
    > to ONE. It seems like it should be possible but I have tried about a
    > 100 different combination of joins and nested selects and can't get the
    > result I want.
    And what result would that be?


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  5. #5

    Default Re: Combing two simple select statements. There must be a way?

    <ukr_bend> wrote in message
    news:1143190349.799462.135730g10g2000cwb.googlegr oups.com...
    > Thanks for the help Bill. Unfortunately that statement returns all the
    > rows, even the ones that have a matching userid between the two tables.
    > But again, thanks for the post.
    Can you describe in more detail -- or show an example -- of what the desired
    results would be of combining the two queries into one? There are multiple
    ways it can be done. We need some more clues in order to suggest the right
    solution.

    Here's another possibility, simply jamming the two queries together in a
    UNION:

    SELECT user1.* FROM user_tab AS user1 INNER JOIN user_pref_tab
    AS pref1 ON user1.id=pref1.userid and pref1.questionid = '20'
    UNION
    SELECT user2.* FROM user_tab AS user2 LEFT JOIN user_pref_tab
    AS pref2 ON user2.id=pref2.userid WHERE pref2.userid IS NULL

    Also it would help if you told us what brand and version of RDBMS you're
    using. The solution may be different depending on which SQL features are
    supported by the RDBMS.

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: Combing two simple select statements. There must be a way?

    "Bill Karwin" <billkarwin.com> wrote in message
    news:e01b920dp9enews2.newsguy.com...
    > Also it would help if you told us what brand and version of RDBMS you're
    > using. The solution may be different depending on which SQL features are
    > supported by the RDBMS.
    Woops! Of course, you must be using MySQL since this is the MySQL
    newsgroup. I thought I was replying in the comp.databases newsgroup and I
    posted too quickly. My mistake.

    Anyway, it would still be helpful to know which version of MySQL you're
    using.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Replies: 4
    Last Post: February 27th, 01:22 PM
  2. Use MS Access to write SQL select statements
    By StylusDesigns in forum Macromedia ColdFusion
    Replies: 2
    Last Post: June 1st, 11:37 PM
  3. Replies: 2
    Last Post: May 14th, 12:55 PM
  4. recordset.recordcount return allways -1 on select statements
    By Sérgio Almeida in forum ASP Database
    Replies: 2
    Last Post: October 24th, 11:17 PM
  5. Using IF and IsNull statements in SELECT
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 03:43 AM

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