help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with query

Author

Topic

kamesh
Starting Member

3 Posts

Posted - 2007-11-12 : 08:56:29

Hi

Im trying to create a SQL query which query's the same table twice.

tables:
exp_category_posts

CREATE TABLE `exp_category_posts` (
`entry_id` int(10) unsigned NOT NULL default '0',
`cat_id` int(10) unsigned NOT NULL default '0',
KEY `entry_id` (`entry_id`),
KEY `cat_id` (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

exp_category_titles

CREATE TABLE `exp_weblog_titles` (
`entry_id` int(10) unsigned NOT NULL auto_increment,
`site_id` int(4) unsigned NOT NULL default '1',
`weblog_id` int(4) unsigned NOT NULL default '0',
`author_id` int(10) unsigned NOT NULL default '0',
`pentry_id` int(10) NOT NULL default '0',
`forum_topic_id` int(10) unsigned NOT NULL default '0',
`ip_address` varchar(16) NOT NULL default '',
`title` varchar(100) NOT NULL default '',
`url_title` varchar(75) NOT NULL default '',
`status` varchar(50) NOT NULL default '',
`versioning_enabled` char(1) NOT NULL default 'n',
`view_count_one` int(10) unsigned NOT NULL default '0',
`view_count_two` int(10) unsigned NOT NULL default '0',
`view_count_three` int(10) unsigned NOT NULL default '0',
`view_count_four` int(10) unsigned NOT NULL default '0',
`allow_comments` char(1) NOT NULL default 'y',
`allow_trackbacks` char(1) NOT NULL default 'y',
`sticky` char(1) NOT NULL default 'n',
`entry_date` int(10) NOT NULL default '0',
`dst_enabled` char(1) NOT NULL default 'n',
`year` varchar(4) NOT NULL default '',
`month` varchar(2) NOT NULL default '',
`day` varchar(3) NOT NULL default '',
`expiration_date` int(10) NOT NULL default '0',
`comment_expiration_date` int(10) NOT NULL default '0',
`edit_date` bigint(14) default NULL,
`recent_comment_date` int(10) NOT NULL default '0',
`comment_total` int(4) unsigned NOT NULL default '0',
`trackback_total` int(4) unsigned NOT NULL default '0',
`sent_trackbacks` text NOT NULL,
`recent_trackback_date` int(10) NOT NULL default '0',
PRIMARY KEY (`entry_id`),
KEY `weblog_id` (`weblog_id`),
KEY `author_id` (`author_id`),
KEY `url_title` (`url_title`),
KEY `status` (`status`),
KEY `entry_date` (`entry_date`),
KEY `expiration_date` (`expiration_date`),
KEY `site_id` (`site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=185 ;

My SQL is:
select *
from exp_category_posts
inner join exp_weblog_titles
on exp_category_posts.entry_id = exp_weblog_titles.entry_id where exp_category_posts.cat_id = "43" and exp_weblog_titles.title LIKE "%Woolwich%"

now after where exp_category_posts.cat_id like "43 I need the query to look in exp_category_posts.cat_id again and then find another variable, for example sake well say exp_category_posts.cat_id="65" and then it would search exp_weblog_titles.title for a title

I tried:
select *
from exp_category_posts
inner join exp_weblog_titles
on exp_category_posts.entry_id = exp_weblog_titles.entry_id where exp_category_posts.cat_id = "43" and where exp_category_posts.cat_id = "65" and exp_weblog_titles.title LIKE "%Woolwich%"

anyone have any ideas?

thanks for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 09:09:39

Turn to MySQL forums on the INTERNET. This is a MICROSOFT SQL SERVER forum.

Try www.dbforums.com.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 02:58:46

1 Avoid having * when join multple tables
2 You used WHERE twice in your query
3 As said post at correct forums. www.mysql.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources