Question regarding SELECT query

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

Question regarding SELECT query

Author

Topic

orribl
Starting Member

2 Posts

Posted - 2010-05-08 : 09:08:25

Hi,

I have a question with regards to a SELECT query. I have the following tables:
- task with task_details
- tag table with tags
- task_has_tag specifies which task has which tag

My aim is now to get all tasks which have specfic tags. I tried the following, but I now get all tasks which have one of the tags, I would like to create an SQL where all tasks are returned which have all tags (AND relatioN).

Here's the query to get all tasks which have one of the tags. I thought that it should be somehow possible to get only those records where the number of results within this table is equal to the number of tags I'm searching for. Please note that the sql is created genericaly and that the number of tags is not fix.


SELECT t . *
FROM STATUS AS s, task AS t
LEFT JOIN task_has_tag AS tht ON tht.task_id = t.task_id
LEFT JOIN tag AS tg ON tht.tag_id = tg.tag_id
WHERE (
tg.tag_name = 'projectA'
OR tg.tag_name = 'project'
)
AND t.task_status = s.status_id
AND (

SELECT COUNT( * )
FROM STATUS AS s2
WHERE s2.parent_status = s.status_id
) >0
ORDER BY IF( ISNULL( task_latest_due_date ) , 1, 0 ) , task_latest_due_date

Here are the create statements of the tables:


--
-- Table structure for table `tag`
--

CREATE TABLE IF NOT EXISTS `tag` (
  `tag_id` int(11) NOT NULL auto_increment,
  `tag_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`tag_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `task`
--

CREATE TABLE IF NOT EXISTS `task` (
  `task_id` int(11) NOT NULL auto_increment,
  `task_subject` varchar(50) NOT NULL,
  `task_description` text,
  `task_planned_due_date` date default NULL,
  `task_latest_due_date` date default NULL,
  `task_status` int(11) NOT NULL,
  PRIMARY KEY  (`task_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

-- --------------------------------------------------------

--
-- Table structure for table `task_has_tag`
--

CREATE TABLE IF NOT EXISTS `task_has_tag` (
  `task_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Any help/idea is appreciated.

Thanks

orribl
Starting Member

2 Posts

Posted - 2010-05-08 : 09:16:54

Hi,

please find some more details of the tables / expecations


task:
task_id 	task_subject 	task_description 	task_planned_due_date 	task_latest_due_date 	task_status
1 	a_subj 	<p>a_desc</p> 	2010-05-03 	2010-05-04 	1
2 	b_subj 	<p>b_desc</p> 	2010-05-03 	2010-05-05 	1


tag:
tag_id 	tag_name
1 	project
2 	projectA


task_has_tag:
task_id 	tag_id
1 	1
1 	2
2 	1

Result with the above mentioned SELECT query:


task_id 	task_subject 	task_description 	task_planned_due_date 	task_latest_due_date 	task_status
1 	a_subj 	<p>a_desc</p> 	2010-05-03 	2010-05-04 	1
1 	a_subj 	<p>a_desc</p> 	2010-05-03 	2010-05-04 	1
2 	b_subj 	<p>b_desc</p> 	2010-05-03 	2010-05-05 	1

Result I need (those who have both tags, projectA & project):


task_id 	task_subject 	task_description 	task_planned_due_date 	task_latest_due_date 	task_status
1 	a_subj 	<p>a_desc</p> 	2010-05-03 	2010-05-04 	1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 10:30:19

just put a DISTINCT in SELECT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources