Select query

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');

Code (markup):

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

Code (markup):

This produces the correct results:


What I'm after is something like this, where the gantttaskno '0' for every FK_projid is included too:
[Photoshop'ed]

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

Cheers

 

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?

 

Why did you declare:

?
Should be:

`gantttaskno` INT NOT NULL DEFAULT 0,

Code (markup):

Then add OR

.......................................................
WHERE FK_userid=7 OR gantttaskno=0
.......................................................

Code (markup):

Regards

 

Thanks for the reply,

OR won't work, when more entries are added to the table, the query will return all gantttaskno=0.
The idea is to filter the data by FK_user, then add the gantttask=0 record with the same FK_projid as the returned item.
Does that make sense?
See previous post

 

You didn't provide table structure for tbl_language and tbl_user but this should be something like this:

(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)
UNION
(SELECT tbl_gantt.ganttid,
tbl_gantt.FK_projid,
tbl_gantt.gantttaskno,
tbl_gantt.gantteventtype, 
tbl_gantt.ganttname, 
NULL,
NULL
FROM tbl_gantt 
WHERE gantttaskno=0 AND FK_projid IN(
SELECT tbl_gantt.FK_projid
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
)
)

Code (markup):

In your query is missing relation with tbl_language (resp. on clause) but you'll fix this.
Not tested

 

Sorted:
WHERE FK_userid = 7
OR (gantttaskno=0 and FK_projid in (select FK_projid from tbl_gantt where FK_userid = 7))