#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    33
    Rep Power
    6

    Error #1241 Operand should contain 1 column


    Hello everyone, I'm new here and could use some help.

    I have a phpMyAdmin database and I wrote this query, which WORKS:

    SELECT *
    FROM `tr_ut4`
    WHERE `Process ID` IN
    (SELECT `Process ID`
    FROM `tr_ut4`
    WHERE `RR` = 'Rotor'
    AND `Process ID` IN
    (SELECT `Process ID`
    FROM `tr_ut4`
    WHERE `RR` = 'Ring'))
    AND `H` <> 0


    Now I need to expand it a bit and get data out of another table (`tr_ut4`) where the `Process ID` in the `tr_ut4` table matches the `Process ID` from the results of that first query.

    I wrote this query, which does NOT work:

    SELECT *
    FROM `tr_ut4`
    WHERE `Process ID` IN
    (SELECT `Process ID`
    FROM `tr_ut4`
    WHERE `RR` = 'Rotor'
    AND `Process ID` IN
    (SELECT `Process ID`
    FROM `tr_ut4`
    WHERE `RR` = 'Ring'))
    AND `H` <> 0

    When I run this query, phpMyAdmin doesn't give me an error code, it just runs for about 5 minutes and give me a blank page.

    I then tried this query:

    SELECT *
    FROM `tr_ut4_old`
    WHERE `Process ID` IN
    (SELECT `Process ID`
    FROM `tr_ut4_old`
    WHERE `RR` = 'Rotor'
    AND `Process ID` IN
    (SELECT `Process ID`
    FROM `tr_ut4_old`
    WHERE `RR` = 'Ring'))
    AND `H` <> 0

    This gave me error code #1241 Operand should contain 1 column

    I tried to read up on this and it seems like there may be an issue with my parenthesis?

    Help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    West Yorkshire, England
    Posts
    236
    Rep Power
    16
    Originally Posted by kmcamara
    ...
    When I run this query, phpMyAdmin doesn't give me an error code, it just runs for about 5 minutes and give me a blank page.
    I'm not surprised about the 5 minutes. You're using too many sub queries. Every subquery needs to be evaluated into a set of results before the query above can start processing. This prevents any optimisation and hence it is taking forever to run.

    You need to be looking at using the join statement and aliasing the tables when you're joining the same table together twice. Taking this into account, your first expression would read something like:
    Code:
    select t1.* 
    from `tr_ut4` as t1
      join `tr_ut4` as t2
        using(`Process ID`)
      join `tr_ut4` as t3
        using(`Process ID`)
    where t3.`RR` = 'Ring'
      and t2.`RR` = 'Rotor'
      and t1.`h` <> 0
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    33
    Rep Power
    6
    fivesidecube, thanks for your help.

    I used most of that code and ran this:

    select distinct t1.*
    from `tr_ut4` as t1
    join `tr_ut4` as t2
    using(`Process ID`)
    join `tr_ut4` as t3
    using(`Process ID`)
    where t3.`RR` = 'Ring'
    and t2.`RR` = 'Rotor'
    and t1.`h` <> 0

    This gave me the same results as my first working query, perhaps a bit speedier.


    Then I tried to get the next step by running this:

    select *
    from `afis`
    where `Process ID` in
    (select distinct t1.*
    from `tr_ut4` as t1
    join `tr_ut4` as t2
    using(`Process ID`)
    join `tr_ut4` as t3
    using(`Process ID`)
    where t3.`RR` = 'Ring'
    and t2.`RR` = 'Rotor'
    and t1.`h` <> 0)


    I still got the same error code #1241 - Operand should contain 1 column(s)

    Any ideas?

    Thanks again for your help!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I think you might have more success if you provided a small but representative dataset (as a set of CREATE and INSERT statements) together with the resultset you'd expect from your query.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    33
    Rep Power
    6
    Sorry, I'm pretty new to all this.

    I didn't use and CREATE or INSERT statements to build the database, I used the GUI in phpMyAdmin.

    If this helps: I'm trying to get data from all fields from the `afis` table (25 fields, one of which is `Process ID`) where the `Process ID` matches the `Process ID`'s generated by the original query:

    select distinct t1.*
    from `tr_ut4` as t1
    join `tr_ut4` as t2
    using(`Process ID`)
    join `tr_ut4` as t3
    using(`Process ID`)
    where t3.`RR` = 'Ring'
    and t2.`RR` = 'Rotor'
    and t1.`h` <> 0

    This query returned data from all of the fields from the `tr_ut4` table (83 fields, one of which is also `Process ID`) where the individual Process ID had entries for both 'Ring' and 'Rotor' and had a value for `H`.

    So basically, I need this new query to get all of the data from the `tr_ut4` table where the Process ID's match those from the results of the first query.

    Do this make sense? I'm not really sure that a representative data set would fit on here.

    Thanks again for all the help!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    123
    Rep Power
    13
    Use SHOW CREATE TABLE

    Code:
    SHOW CREATE TABLE tbl_users
    on our DB gives:

    CREATE TABLE `tbl_users` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `admin` tinyint(1) NOT NULL DEFAULT '0',
    `username` varchar(20) COLLATE latin1_general_ci NOT NULL,
    `password` varchar(40) COLLATE latin1_general_ci NOT NULL,
    `email` varchar(50) COLLATE latin1_general_ci NOT NULL,
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
    ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
    I built this with phpmyadmin as well. SHOW CREATE TABLE shows what create statement would give the existing table.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    33
    Rep Power
    6
    Thanks everybody, I just figured it out. I was staring at the query all day and missed what was in front of my face.

    What worked was:

    Code:
    select *
    from `afis`
    where `Process ID` in
    (select distinct t1.`Process ID` 
    from `tr_ut4` as t1
      join `tr_ut4` as t2
        using(`Process ID`)
      join `tr_ut4` as t3
        using(`Process ID`)
    where t3.`RR` = 'Ring'
      and t2.`RR` = 'Rotor'
      and t1.`h` <> 0)

IMN logo majestic logo threadwatch logo seochat tools logo