Joint table search in mysql , please help !

[habbardone]

Hi,

I have 2 tables and want to do a joint search

But is this possible ?

In table subscriptions is record "mess_no" which has a number in it.

That number corresponds to the name of a record in the 2nd table camps

In table camps there are fifty different messages and fifty message titles.
These are named "mess_1" throu "mess_50" and "title_1" throu "title_50".

I only need one pair of those.
So instead of filling up the memory with unnecessary stuff, I'd rather select the correct pair

So if the subscriptions table record "mess_no" has a "6" in it, I only need "mess_6" and "title_6" from the camps table .

Can I put this in the one query ?

Something like this ?

$sql = "SELECT author,   ( mess_.$mess_no) , ( title_.$title_no), sent_date, sub_name, sub_email FROM camps, subscriptions WHERE live = 'y' AND approve = 'y' AND confirm = 'y' AND mess_no != '0' AND subscriptions.camp_no = camps.camp_id AND send_date <= now() ";

I dunno seems it might work ??

Is it logical ?? 😕😕

Thanks for any help on this 🙂

.

[bradgrafelman]

habbardone;10999046 wrote:

In table camps there are fifty different messages and fifty message titles.
These are named "mess_1" throu "mess_50" and "title_1" throu "title_50".

Are these columns in that table?

If so, then your first problem is that you need to normalize your DB schema into a form that's actually usable. 🙂

Can you show us the CREATE TABLE statements for both tables so that we can get a better idea of what your DB schema looks like?

[habbardone]

Yes sure:

$sql = "CREATE TABLE `subscriptions` (
	`sub_id` int(6) NOT NULL AUTO_INCREMENT, 
	`camp_no` smallint(6) unsigned NOT NULL default '0',
	`client_no` varchar(7) NOT NULL default '',
	`confirm` char(1) NOT NULL default 'n',
	`type` varchar(10) NOT NULL default '',
	`mess_no` smallint(6) unsigned NOT NULL default '0',
	`send_date` datetime,
	`sub_name` varchar(50) NOT NULL default '',
	`sub_email` varchar(80) NOT NULL default '',
	`country` varchar(50) NOT NULL default '',
  	`create_date` datetime,
 	 KEY `client_no` (`client_no`),
	 KEY `sub_email` (`sub_email`),
	 PRIMARY KEY (sub_id)

   ) ENGINE=InnoDB DEFAULT CHARSET=latin1";

and


$sql = "CREATE TABLE `camps` (
	`camp_id` smallint(5) unsigned NOT NULL auto_increment,
	`client_no` varchar(7) NOT NULL default '',
	`live` char(1) NOT NULL default 'n',
	`approve` char(1) NOT NULL default '',
	`reason` varchar(100) NOT NULL default 'n',
	`title`  varchar(60) NOT NULL default '',
	`gift`  varchar(60) NOT NULL default '',
	`gift_file`  varchar(60) NOT NULL default 'n',
	`author`  varchar(30) NOT NULL default '',
	`niche` varchar(50) NOT NULL default '',
	`return_url` varchar(100) NOT NULL default '',
	`return_tag` varchar(100) NOT NULL default '',
	`next_camp` smallint(5) unsigned NOT NULL default '0',
	`camp_key` varchar(20) NOT NULL default '',
	`title_1` varchar(40) NOT NULL default '',
	`title_2` varchar(40) NOT NULL default '',
	`title_3` varchar(40) NOT NULL default '',
	`title_4` varchar(40) NOT NULL default '',
	`title_5` varchar(40) NOT NULL default '',
	`title_6` varchar(40) NOT NULL default '',
	`title_7` varchar(40) NOT NULL default '',
	`title_8` varchar(40) NOT NULL default '',
	`title_9` varchar(40) NOT NULL default '',
	`title_10` varchar(40) NOT NULL default '',
	`title_11` varchar(40) NOT NULL default '',
	`title_12` varchar(40) NOT NULL default '',
	`title_13` varchar(40) NOT NULL default '',
	`title_14` varchar(40) NOT NULL default '',
	`title_15` varchar(40) NOT NULL default '',
	`title_16` varchar(40) NOT NULL default '',
	`title_17` varchar(40) NOT NULL default '',
	`title_18` varchar(40) NOT NULL default '',
	`title_19` varchar(40) NOT NULL default '',
	`title_20` varchar(40) NOT NULL default '',
	`title_21` varchar(40) NOT NULL default '',
	`title_22` varchar(40) NOT NULL default '',
	`title_23` varchar(40) NOT NULL default '',
	`title_24` varchar(40) NOT NULL default '',
	`title_25` varchar(40) NOT NULL default '',
	`title_26` varchar(40) NOT NULL default '',
	`title_27` varchar(40) NOT NULL default '',
	`title_28` varchar(40) NOT NULL default '',
	`title_29` varchar(40) NOT NULL default '',
	`title_30` varchar(40) NOT NULL default '',
	`title_31` varchar(40) NOT NULL default '',
	`title_32` varchar(40) NOT NULL default '',
	`title_33` varchar(40) NOT NULL default '',
	`title_34` varchar(40) NOT NULL default '',
	`title_35` varchar(40) NOT NULL default '',
	`title_36` varchar(40) NOT NULL default '',
	`title_37` varchar(40) NOT NULL default '',
	`title_38` varchar(40) NOT NULL default '',
	`title_39` varchar(40) NOT NULL default '',
	`title_40` varchar(40) NOT NULL default '',
	`title_41` varchar(40) NOT NULL default '',
	`title_42` varchar(40) NOT NULL default '',
	`title_43` varchar(40) NOT NULL default '',
	`title_44` varchar(40) NOT NULL default '',
	`title_45` varchar(40) NOT NULL default '',
	`title_46` varchar(40) NOT NULL default '',
	`title_47` varchar(40) NOT NULL default '',
	`title_48` varchar(40) NOT NULL default '',
	`title_49` varchar(40) NOT NULL default '',
	`title_50` varchar(40) NOT NULL default '',
	`mess_1` text  NOT NULL default '',
	`mess_2` text  NOT NULL default '',
	`mess_3` text  NOT NULL default '',
	`mess_4` text  NOT NULL default '',
	`mess_5` text  NOT NULL default '',
	`mess_6` text  NOT NULL default '',
	`mess_7` text  NOT NULL default '',
	`mess_8` text  NOT NULL default '',
	`mess_9` text  NOT NULL default '',
	`mess_10` text  NOT NULL default '',
	`mess_11` text  NOT NULL default '',
	`mess_12` text  NOT NULL default '',
	`mess_13` text  NOT NULL default '',
	`mess_14` text  NOT NULL default '',
	`mess_15` text  NOT NULL default '',
	`mess_16` text  NOT NULL default '',
	`mess_17` text  NOT NULL default '',
	`mess_18` text  NOT NULL default '',
	`mess_19` text  NOT NULL default '',
	`mess_20` text  NOT NULL default '',
	`mess_21` text  NOT NULL default '',
	`mess_22` text  NOT NULL default '',
	`mess_23` text  NOT NULL default '',
	`mess_24` text  NOT NULL default '',
	`mess_25` text  NOT NULL default '',
	`mess_26` text  NOT NULL default '',
	`mess_27` text  NOT NULL default '',
	`mess_28` text  NOT NULL default '',
	`mess_29` text  NOT NULL default '',
	`mess_30` text  NOT NULL default '',
	`mess_31` text  NOT NULL default '',
	`mess_32` text  NOT NULL default '',
	`mess_33` text  NOT NULL default '',
	`mess_34` text  NOT NULL default '',
	`mess_35` text  NOT NULL default '',
	`mess_36` text  NOT NULL default '',
	`mess_37` text  NOT NULL default '',
	`mess_38` text  NOT NULL default '',
	`mess_39` text  NOT NULL default '',
	`mess_40` text  NOT NULL default '',
	`mess_41` text  NOT NULL default '',
	`mess_42` text  NOT NULL default '',
	`mess_43` text  NOT NULL default '',
	`mess_44` text  NOT NULL default '',
	`mess_45` text  NOT NULL default '',
	`mess_46` text  NOT NULL default '',
	`mess_47` text  NOT NULL default '',
	`mess_48` text  NOT NULL default '',
	`mess_49` text  NOT NULL default '',
	`mess_50` text  NOT NULL default '',
	`freq_1` smallint(1) unsigned NOT NULL default '3',
	`freq_2` smallint(1) unsigned NOT NULL default '3',
	`freq_3` smallint(1) unsigned NOT NULL default '3',
	`freq_4` smallint(1) unsigned NOT NULL default '3',
	`freq_5` smallint(1) unsigned NOT NULL default '3',
	`freq_6` smallint(1) unsigned NOT NULL default '3',
	`freq_7` smallint(1) unsigned NOT NULL default '3',
	`freq_8` smallint(1) unsigned NOT NULL default '3',
	`freq_9` smallint(1) unsigned NOT NULL default '3',
	`freq_10` smallint(1) unsigned NOT NULL default '3',
	`freq_11` smallint(1) unsigned NOT NULL default '3',
	`freq_12` smallint(1) unsigned NOT NULL default '3',
	`freq_13` smallint(1) unsigned NOT NULL default '3',
	`freq_14` smallint(1) unsigned NOT NULL default '3',
	`freq_15` smallint(1) unsigned NOT NULL default '3',
	`freq_16` smallint(1) unsigned NOT NULL default '3',
	`freq_17` smallint(1) unsigned NOT NULL default '3',
	`freq_18` smallint(1) unsigned NOT NULL default '3',
	`freq_19` smallint(1) unsigned NOT NULL default '3',
	`freq_20` smallint(1) unsigned NOT NULL default '3',
	`freq_21` smallint(1) unsigned NOT NULL default '3',
	`freq_22` smallint(1) unsigned NOT NULL default '3',
	`freq_23` smallint(1) unsigned NOT NULL default '3',
	`freq_24` smallint(1) unsigned NOT NULL default '3',
	`freq_25` smallint(1) unsigned NOT NULL default '3',
	`freq_26` smallint(1) unsigned NOT NULL default '3',
	`freq_27` smallint(1) unsigned NOT NULL default '3',
	`freq_28` smallint(1) unsigned NOT NULL default '3',
	`freq_29` smallint(1) unsigned NOT NULL default '3',
	`freq_30` smallint(1) unsigned NOT NULL default '3',
	`freq_31` smallint(1) unsigned NOT NULL default '3',
	`freq_32` smallint(1) unsigned NOT NULL default '3',
	`freq_33` smallint(1) unsigned NOT NULL default '3',
	`freq_34` smallint(1) unsigned NOT NULL default '3',
	`freq_35` smallint(1) unsigned NOT NULL default '3',
	`freq_36` smallint(1) unsigned NOT NULL default '3',
	`freq_37` smallint(1) unsigned NOT NULL default '3',
	`freq_38` smallint(1) unsigned NOT NULL default '3',
	`freq_39` smallint(1) unsigned NOT NULL default '3',
	`freq_40` smallint(1) unsigned NOT NULL default '3',
	`freq_41` smallint(1) unsigned NOT NULL default '3',
	`freq_42` smallint(1) unsigned NOT NULL default '3',
	`freq_43` smallint(1) unsigned NOT NULL default '3',
	`freq_44` smallint(1) unsigned NOT NULL default '3',
	`freq_45` smallint(1) unsigned NOT NULL default '3',
	`freq_46` smallint(1) unsigned NOT NULL default '3',
	`freq_47` smallint(1) unsigned NOT NULL default '3',
	`freq_48` smallint(1) unsigned NOT NULL default '3',
	`freq_49` smallint(1) unsigned NOT NULL default '3',
	`freq_50` smallint(1) unsigned NOT NULL default '3',
	KEY `camp_key` (`camp_key`),
	PRIMARY KEY `camp_id` (`camp_id`)
	 ) ENGINE=InnoDB DEFAULT CHARSET=latin1";
	

But my real question is , is what I was trying to do possible ?

If not I can just do it with a 2nd query statement after the one I have mentioned.

Thanks.

.