Professional Web Applications Themes

Using IN with subquery when columns aren't named the same - MySQL

I am trying to write a query that uses IN but there seems to be a problem. The syntax should look something like this: SELECT * FROM table_one WHERE some_column IN (SELECT some_column FROM table_two WHERE other_column='someValue'); However, it appears that the column name in the outter query and the column that you select in the inner (sub) query must have the same name. Otherwise, an error occurs. At first, I thought maybe there was an error in my subquery but I tried it by itself and it works fine. In all of the examples I have found of using ...

  1. #1

    Default Using IN with subquery when columns aren't named the same

    I am trying to write a query that uses IN but there seems to be a
    problem. The syntax should look something like this:

    SELECT * FROM table_one WHERE some_column IN (SELECT some_column FROM
    table_two WHERE other_column='someValue');

    However, it appears that the column name in the outter query and the
    column that you select in the inner (sub) query must have the same
    name. Otherwise, an error occurs.

    At first, I thought maybe there was an error in my subquery but I tried
    it by itself and it works fine. In all of the examples I have found of
    using IN with a subquery, both tables had the same column names. Is
    there any syntax for using a subquery with a different column name?
    ....Like to create an alias for a column name maybe?

    -Brandon

    bcr07548@creighton.edu Guest

  2. #2

    Default Re: Using IN with subquery when columns aren't named the same

    <bcr07548creighton.edu> wrote in message
    news:1137031554.100681.57950g44g2000cwa.googlegro ups.com...
    >I am trying to write a query that uses IN but there seems to be a
    > problem. The syntax should look something like this:
    >
    > SELECT * FROM table_one WHERE some_column IN (SELECT some_column FROM
    > table_two WHERE other_column='someValue');
    Try using table aliases.

    SELECT *
    FROM table_one AS t1
    WHERE t1.some_column IN (SELECT t2.some_column
    FROM table_two AS t2
    WHERE t2.other_column='someValue');

    It helps the SQL pr if the tokens are unambiguous with respect to which
    table they come from.

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Using IN with subquery when columns aren't named the same

    > Try using table aliases.
    >
    > SELECT *
    > FROM table_one AS t1
    > WHERE t1.some_column IN (SELECT t2.some_column
    > FROM table_two AS t2
    > WHERE t2.other_column='someValue');
    >
    > It helps the SQL pr if the tokens are unambiguous with respect to which
    > table they come from.
    I tried that before posting and it didn't seem to help. Does this look
    right to you?

    SELECT * FROM positionPosting AS p WHERE p.id IN(SELECT t.postID FROM
    postingTimeOfDay AS t WHERE t.timeID='1');

    I had learned to declare aliases without AS so my original attempt
    looked like this

    SELECT * FROM positionPosting p WHERE p.id IN(SELECT t.postID FROM
    postingTimeOfDay t WHERE t.timeID='1');

    It works just as poorly and I get the following error:

    ERROR 1064: You have an error in your SQL syntax near 'SELECT t.postID
    FROM postingTimeOfDay t WHERE t.timeID='1')' at line 1

    Any ideas?

    bcr07548@creighton.edu Guest

  4. #4

    Default Re: Using IN with subquery when columns aren't named the same

    The weird thing is that the subquery works by itself and the outer
    query works if you replace the subquery with a value or set of values.
    I looked up error 1064 and it's a p error which makes me think that
    maybe the column names MUST be the same. Without renaming the columns,
    is there any way to tell MySQL to use the column returned from the
    subquery even if the name is different? What I had in mind was
    somewhat of a *column* alias but I don't know if there is such a thing.

    bcr07548@creighton.edu Guest

  5. #5

    Default Re: Using IN with subquery when columns aren't named the same

    <bcr07548creighton.edu> wrote in message
    news:1137049827.605347.151240g14g2000cwa.googlegr oups.com...
    > SELECT * FROM positionPosting p WHERE p.id IN(SELECT t.postID FROM
    > postingTimeOfDay t WHERE t.timeID='1');
    >
    > ERROR 1064: You have an error in your SQL syntax near 'SELECT t.postID
    > FROM postingTimeOfDay t WHERE t.timeID='1')' at line 1
    Aha -- I infer that you are using MySQL 4.0 or earlier version, which do not
    support subquery syntax. To use the query above, you must upgrade to MySQL
    4.1 or later version.

    Alternately, one can rewrite many types of queries so that they don't use
    subqueries, but produce the same results. See this page for starters:

    [url]http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html[/url]

    Your query can easily be written as an inner join.

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: Using IN with subquery when columns aren't named the same

    You are right. I am using Fedora Core 3 and looks like it comes with
    MySQL 3.x. How ridiculous.

    bcr07548@creighton.edu Guest

  7. #7

    Default Re: Using IN with subquery when columns aren't named the same

    <bcr07548creighton.edu> wrote in message
    news:1137090247.715494.50730f14g2000cwb.googlegro ups.com...
    > You are right. I am using Fedora Core 3 and looks like it comes with
    > MySQL 3.x. How ridiculous.
    Not completely ridiculous, given that FC3 was released 2004-11-08
    ([url]http://fedora.redhat.com/About/schedule/[/url]), and MySQL 4.1 was released only
    a couple of weeks earlier, 2004-10-23
    ([url]http://dev.mysql.com/doc/refman/4.1/en/news.html[/url]). Though I wonder why
    they didn't include at least MySQL 4.0.

    FWIW, Fedora Core 4 appears to include MySQL 4.1, according to the release
    notes([url]http://fedora.redhat.com/docs/release-notes/fc4/#sn-overview[/url]).

    Fedore Core 5 test1 includes MySQL 4.1.12 at this time. I wonder if they'll
    adopt MySQL 5.0 before FC5 is released.

    Regards,
    Bill K.


    Bill Karwin Guest

  8. #8

    Default Re: Using IN with subquery when columns aren't named the same

    Actually I am finishing the update from FC3 to FC4 right now. :)
    Let's just hope that the upgrade didn't break EVERYTHING on my system.
    This seems like a great example of a reason to use a distro that
    doesn't have these cutoff points where only minor updates are offered.
    After I am done with this project, I am switching to a different
    distribution.

    As for FC5, I doubt it. Since Fedora is sort of Red Hat's test bed,
    they seem to favor software that's somewhere in between "tried and
    true" and "cutting edge." They never seem to be too concerned with
    pushing the latest and greatest just for the sake of pushing of the
    latest and greatest. However, it looks like the development branch of
    Fedora will be using MySQL 5.

    bcr07548@creighton.edu Guest

Similar Threads

  1. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  2. variable named field calculated from variable named fields
    By Chris Kemp in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 24th, 07:36 PM
  3. Replies: 0
    Last Post: July 2nd, 06:18 AM
  4. subquery with more than 1 fields
    By Calvin in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 05:53 AM
  5. A subquery returning two columns
    By Vsevolod Afanassiev in forum Oracle Server
    Replies: 1
    Last Post: June 28th, 06:45 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