Help with subselect

pearl2 · #1 (**permalink**) 12-07-07, 07:57

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

r937 · #2 (**permalink**) 12-07-07, 14:00

could you please do SHOW CREATE TABLE for both your tables

couple rows of sample data wouldn't hurt either

pearl2 · #3 (**permalink**) 12-07-07, 19:48

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.

r937 · #4 (**permalink**) 12-07-07, 20:21

okay, that's great, thanks

now, could you please explain what you wanted the query to do?

pearl2 · #5 (**permalink**) 12-07-07, 20:39

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...

r937 · #6 (**permalink**) 12-07-07, 20:50

Code:

select user_id
     , model
     , description
     , model_id
     , registered
  from table_b 
 where model_id =
       ( select max(model_id)
           from table_a )

pearl2 · #7 (**permalink**) 12-07-07, 21:03

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?

r937 · #8 (**permalink**) 12-07-07, 22:23

use two queries

pearl2 · #9 (**permalink**) 12-07-07, 22:55

Quote:

Originally Posted by r937

use two queries

Ok thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech