Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Help with subselect

    Hello everybody,

    I'm trying to get the sql below to work but apparently the syntax isn't quite right:

    Code:
    SELECT model, (SELECT MAX(model_id) FROM table_a) AS max_id
    FROM table_b
    WHERE table_b.model_id=max_id
    The error message I got is: Unknown column max_id in WHERE clause.

    Could some point me in the right direction?

    Thanks in anticipation
    Last edited by pearl2; 12-07-07 at 09:08.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please do SHOW CREATE TABLE for both your tables

    couple rows of sample data wouldn't hurt either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Hi r937,

    Thanks, here is the create table code:

    Code:
    CREATE TABLE table_a (
      `model_id` smallint(5) unsigned PRIMARY KEY NOT NULL auto_increment,
      `description` tinytext NOT NULL default '',
      `created` date NOT NULL default '0000-00-00',
    );
    
    CREATE TABLE table_b (
      `user_id` smallint(5) unsigned PRIMARY KEY NOT NULL auto_increment,
      `model` varchar(50) NOT NULL default '',
      `description` text NOT NULL default '',
      `model_id` smallint(5) unsigned NOT NULL,
      `registered` date NOT NULL default '0000-00-00',
    );
    I haven't got any data as I'm still experimenting with the table setup.
    Last edited by pearl2; 12-07-07 at 21:36.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, that's great, thanks

    now, could you please explain what you wanted the query to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    okay, that's great, thanks

    now, could you please explain what you wanted the query to do?
    I wanted to select data from table_b based on the maximum model_id from table_a.

    This works:

    Code:
    SELECT model FROM table_b
    WHERE table_b.model_id=(SELECT MAX(model_id) FROM table_a)
    But the max_id isn't selected...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select user_id
         , model
         , description
         , model_id
         , registered
      from table_b 
     where model_id =
           ( select max(model_id)
               from table_a )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    Code:
    select user_id
         , model
         , description
         , model_id
         , registered
      from table_b 
     where model_id =
           ( select max(model_id)
               from table_a )
    Thanks, r937!



    Added:

    But is it possible to write the sql so that even if no row exist in table b meets the condition, the max_id from table_a is still selected. Is that doable?
    Last edited by pearl2; 12-07-07 at 22:12.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use two queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    use two queries
    Ok thanks

Posting Permissions

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