SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Select query

  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select query

    Hi Chaps,

    I have a MySQL table: tbl_gantt:
    Code:
    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 		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:
    Code:
    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by kool_samule View Post
    but I'm after some sort of 'add-on' to this query where I can see:
    Code:
    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

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I may have confused myself with that . . . .
    Basically, if you run the current select script:
    Code:
    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:

  4. #4
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, thanks for the advice,

    did what you mentioned, all sorted now.

    Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •