SELECT QUERY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

SELECT QUERY

Post by koolsamule »

Hi Chaps,

I have a MySQL table: tbl_gantt:

Code: Select all

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:

Code: Select all

SELECT 		tbl_gantt.ganttid,
			tbl_gantt.FK_projid,
			tbl_gantt.gantttaskno,
			tbl_gantt.gantteventtype, 
			tbl_gantt.ganttname
FROM 		tbl_gantt 
WHERE 		FK_userid = 7
ORDER BY	FK_projid ASC,
			FK_jobid ASC,
			gantttaskno ASC


This produces the correct results:
Image

What I'm after is something like this, where the gantttaskno '0' for every FK_projid is included too:
[Photoshop'ed]
Image
I hope that is clear and makes some sort of sense.

Cheers
Last edited by koolsamule on Wed Mar 31, 2010 10:55 am, edited 1 time in total.
jbulaswad
Forum Newbie
Posts: 14
Joined: Tue Mar 30, 2010 2:37 pm
Location: Detroit, Michigan, USA

Re: SELECT QUERY

Post by jbulaswad »

Reason all your results are not returning is due to WHERE FK_userid = 7.

Either remove that statement or replace it with WHERE FK_usersid IS NOT NULL if you would only like tasks that have users assigned.

You also reference a table that is not joined so you will need to either join the tbl_language table or remove the tbl_language.langtname field from the SELECT statement.
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: SELECT QUERY

Post by koolsamule »

Hi, thanks for the reply.

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?
jbulaswad
Forum Newbie
Posts: 14
Joined: Tue Mar 30, 2010 2:37 pm
Location: Detroit, Michigan, USA

Re: SELECT QUERY

Post by jbulaswad »

In that case, try this query:

Code: Select all

SELECT          tbl_gantt.ganttid,
                        tbl_gantt.FK_projid,
                        tbl_gantt.gantttaskno,
                        tbl_gantt.gantteventtype,
                        tbl_gantt.ganttname
FROM            tbl_gantt
WHERE           FK_userid = 7 OR gantttaskno = 0
ORDER BY        FK_projid ASC,
                        FK_jobid ASC,
                        gantttaskno ASC
Hopefully that gives you the result set you are looking for.
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: SELECT QUERY

Post by koolsamule »

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.

Otherwise, once more data is entered, all gantttaskno=0 will be returned, even though it's FK_projid is not in any row containing (FK_userid=7)

Does that make sense? Is that possible?

[See previous post]
jbulaswad
Forum Newbie
Posts: 14
Joined: Tue Mar 30, 2010 2:37 pm
Location: Detroit, Michigan, USA

Re: SELECT QUERY

Post by jbulaswad »

Yes, it does make sense and it is possible, let's try this again:

Code: Select all

SELECT
  tbl_gantt.ganttid,
  tbl_gantt.FK_userid,
  tbl_gantt.FK_projid,
  tbl_gantt.gantttaskno,
  tbl_gantt.gantteventtype,
  tbl_gantt.ganttname
FROM tbl_gantt
WHERE tbl_gantt.FK_userid = 7 OR tbl_gantt.ganttid IN (
  SELECT ganttid FROM tbl_gantt WHERE FK_projid IN (
    SELECT FK_projid FROM tbl_gantt WHERE tbl_gantt.FK_userid = 7
  )
)
ORDER BY tbl_gantt.FK_projid ASC,
  tbl_gantt.FK_jobid ASC,
  tbl_gantt.gantttaskno ASC;
I added two subselects to the where clause to include all tasks within a project that user 7 is involved in. Keep in mind that this pulls all tasks assigned to other users, to combat that just add AND tbl_gantt.FK_userid IS NULL to your where statement.
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: SELECT QUERY

Post by koolsamule »

Hi, thanks for the pointers, problem now sorted using:
WHERE FK_userid = 7
OR (gantttaskno=0 and FK_projid in (select FK_projid from tbl_gantt where FK_userid = 7))
Post Reply