MySQL Forums
Forum List  »  Views

Help with conceptualizing a query
Posted by: Bodi K
Date: November 14, 2013 03:24PM

Hi, I hope I posted in the right forum.

I have a small problem that I need help conceptualizing...hopefully from there I can make the actual SQL statement.

I have a project management database that keeps track of web pages needed to be done in each phase of the project.


--This is a list of all the phases. For our purposes assume there is only ever 1 project.
CREATE TABLE `list_phase` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ProjectID` int(11) NOT NULL,
`Phase` varchar(25) NOT NULL,
`Number` tinyint(3) unsigned NOT NULL,
`DueDate` date DEFAULT NULL,
`StartDate` date DEFAULT NULL,
`EndDate` date DEFAULT NULL,
`Description` varchar(150) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ProjectPhase` (`ProjectID`,`Phase`),
KEY `ProjectID` (`ProjectID`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1

--This is a list of all possible pages in the entire project.
CREATE TABLE `list_page` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ProjectID` int(11) NOT NULL,
`Page` varchar(30) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Page` (`Page`),
KEY `ProjectID` (`ProjectID`)
) ENGINE=MyISAM AUTO_INCREMENT=86 DEFAULT CHARSET=latin1

--This links Pages with Phases. It's possible that the Login page will appear in both Phase 1 and Phase 2 and then again in Phase 5.
CREATE TABLE `phase-page` (
`PhaseID` int(10) unsigned NOT NULL,
`PageID` int(10) unsigned NOT NULL,
`Description` varchar(150) NOT NULL,
`DateDue` date DEFAULT NULL,
`DateStart` date DEFAULT NULL,
`DateFinish` date DEFAULT NULL,
PRIMARY KEY (`PhaseID`,`PageID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



This is what I currently have, which works well, but now I'd like to make a change to what data is returned and I can't figure out if it's even possible in SQL or if I'll have to use PHP to further manipulate the data. Currently it returns each page in each phase where the phase number is less than what I request. For example, the Login page would appear 3 times in this result.

SELECT pp.*,p.Page,lp.Number FROM `phase-page` pp JOIN list_page p ON pp.PageID=p.ID JOIN (SELECT ID,Number FROM list_phase WHERE ProjectID=18 AND Number<=5) lp ON PhaseID=lp.ID ORDER BY Number DESC,Page


What I want is to return each page only once but I want it to tell me if it exists in the current phase (phase 5 in this case). Essentially I need to get the first phase a pages appears in (as determined by a sorted phase number) and also have a column that indicates what the max phase number it appears in.

Clear?

Thanks for any thoughts...



Edited 1 time(s). Last edit at 11/14/2013 03:26PM by Bodi K.

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with conceptualizing a query
4931
November 14, 2013 03:24PM
1569
November 17, 2013 12:10AM
1598
November 18, 2013 01:20PM
1699
November 18, 2013 09:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.