SELECT QUERY

[kool_samule]

Hi Chaps,

 

I have a MySQL table: tbl_gantt:

CREATE TABLE `tbl_gantt` (
  `ganttid` int(11) NOT NULL auto_increment,
  `gantteventtype` varchar(100) default NULL,
  `FK_projid` varchar(100) default NULL,
  `FK_jobid` int(6) default NULL,
  `FK_userid` int(6) default NULL,
  `gantttaskno` varchar(20) default NULL,
  `ganttname` varchar(100) default NULL,
  UNIQUE KEY `ganttid` (`ganttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

insert  into `tbl_gantt`(`ganttid`,`gantteventtype`,`FK_projid`,`FK_jobid`,`FK_userid`,`gantttaskno`,`ganttname`) 
values
(1,'group','4001',NULL,NULL,0,'4001 - Project Title'),
(2,'group','4001',182,NULL,1,'Job Sheet 1'),
(4,'task','4001',182,7,4,'Translation'),
(5,'task','4001',182,2,8,'Typesetting'),
(6,'group','4001',183,NULL,1,'Job Sheet 2'),
(8,'task','4001',183,1,4,'Translation'),
(9,'task','4001',183,7,8,'Typesetting'),
(10,'group','4002',NULL,NULL,0,'4002 - Project Title'),
(11,'group','4002',184,NULL,1,'Job Sheet 1'),
(13,'task','4002',184,1,4,'Translation'),
(14,'task','4002',184,2,8,'Typesetting'),
(15,'group','4002',185,NULL,1,'Job Sheet 2'),
(17,'task','4002',185,11,4,'Translation'),
(18,'task','4002',186,7,8,'Typesetting');

 

What I'm trying to do is a SELECT query:

SELECT 		tbl_gantt.ganttid,
		tbl_gantt.FK_projid,
		tbl_gantt.gantttaskno,
		tbl_gantt.gantteventtype, 
		tbl_gantt.ganttname, 
		tbl_language.langtname,
		tbl_user.useralias
FROM 		tbl_gantt 
LEFT OUTER JOIN	tbl_user
		ON tbl_user.userid=tbl_gantt.FK_userid
WHERE 		FK_userid = 7
ORDER BY	FK_projid ASC,
		FK_jobid ASC,
		gantttaskno ASC

 

This produces the correct results, but I'm after some sort of 'add-on' to this query where I can see:

FK_userid=7;
select ganttaskno 
from tbl_gantt 
where gantttaskno= 0 
AND FK_Projid = ('FK_userid=7' gantttaskno.FK_projid)

 

I hope that is clear and makes some sort of sense.

 

Cheers

[ignace]

Do you mean something like?

 

AND FK_Projid = (SELECT FK_Projid FROM tbl_gantt WHERE ..)

[kool_samule]

Uhh, maybe. . . basically, this is the result I'm currently getting:

SELECT 		tbl_gantt.ganttid,
		tbl_gantt.FK_projid,
		tbl_gantt.gantttaskno,
		tbl_gantt.gantteventtype, 
		tbl_gantt.ganttname
FROM 		tbl_gantt 
LEFT OUTER JOIN	tbl_user
		ON tbl_user.userid=tbl_gantt.FK_userid
WHERE 		FK_userid = 7
ORDER BY	FK_projid ASC,
		FK_jobid ASC,
		gantttaskno ASC

 

But what I'm after is (photoshop'ed):

 

[fenway]

Sorry, I don't follow.