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

[guido2004]

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

Ehm... no... not to me

[kool_samule]

OK, I may have confused myself with that . . . .
Basically, if you run the current select script:

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

You get this:


Which is correct, but I want ganttid's 1 and 10, as they are the 'parent' project info for results to appear. . . .so you get this (photoshop'ed):
You get this:

[kool_samule]

I'm having trouble finding a solution to this,

QUERY 1. I need all gantttaskno's for FK_userid=7,
QUERY 2. Also each gantttaskno=0 that relates to the FK_projid returned in QUERY 1.
RESULT. QUERY 1 + QUERY 2

I've had a play around with subqueries, but QUERY 1 returns multiple FK_projid's and gantttaskno doesn't have a FK_userid, so I can't get it to work.

Any ideas?

[r937]

i'm lost too

your table has a mishmash of INTEGERs and VARCHARs

it's really hard to understand how those columns work

my advice: actually declare the FKs using FOREIGN KEY syntax

you're likely to hit several error messages getting them straightened out, but the results will be well worth the trouble

[kool_samule]

Cool, thanks for the advice,

did what you mentioned, all sorted now.

Thanks