if statement with subqueries

[emehrkay]

I have an object table that I use to create unique values across tables. So the person table has a person_id field that is unique to the table and an object_id field that is unique to the whole database (I thought that this would be an elegant solution for my commenting/rating system).

 

In the object table I only store the object_id and object_type, in this case "person." My question is if I have the object_id, what would be the best way for me to run a query against the right table based on the object_type?

 

So if object_id 1 has object_type person, how would I run the subquery - SELECT * FROM person WHERE object_id = 1 ?

 

Thanks

[trq]

Sorry, could you post you db schema for these tables? I'm just having a little trouble picturing what your trying to do.

[emehrkay]

Object table

DROP TABLE IF EXISTS `8trk`.`object`;
CREATE TABLE  `8trk`.`object` (
  `object_id` int(15) NOT NULL auto_increment,
  `object_type` varchar(50) collate latin1_general_ci NOT NULL,
  `date_created` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY  (`object_id`)
) ENGINE=MyISAM AUTO_INCREMENT=81 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

Other tables

DROP TABLE IF EXISTS `8trk`.`users`;
CREATE TABLE  `8trk`.`users` (
  `user_id` int(15) NOT NULL auto_increment,
  `object_id` int(15) NOT NULL,
  `user_name` varchar(50) collate latin1_general_ci NOT NULL,
  `user_pass` text collate latin1_general_ci NOT NULL,
  `user_email` varchar(100) collate latin1_general_ci NOT NULL,
  `user_role` int(10) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

The object_id is a secondary key in almost every other table, I just want to run a query on the object table with that id and return the correct result set

[trq]

Really not sure this will work (Can't see why not). I can't get to my servers to test.

 

SELECT * FROM (SELECT object_type FROM object WHERE object_id = 1);

 

You could also probably do something simular with a JOIN.

 

[emehrkay]

SELECT * FROM (SELECT object_type FROM object WHERE object_id = 1) AS object_type;

 

that returns one column, object_type [the alias was required], not run a query on that object type. Even though that seems like it should work

[trq]

What about?

 

SELECT * FROM (SELECT object_type AS tbl FROM object WHERE object_id = 1) tbl;

[emehrkay]

Same thing - it returns column tbl with result 'user' (the object_type)

[emehrkay]

It seems like this should work, but 'o.object_type doesnt exist'

 

SELECT * FROM object o, (SELECT * from o.object_type) AS subtable WHERE o.object_id = 1;

[fenway]

Same thing - it returns column tbl with result 'user' (the object_type)

Don't you mean SELECT * in the subquery?