0

Background

We show a portfolio of'projects' which each have a single category, but 1 to many different services.

When the information is retrieved from the database it will populate a total of 15 different project divs which is 3 project divs for each of the 5 different categories.

While each of these projects divs has only one category, they have 1 to many different services.

Relevant Tables

Project
* projectID : smallint(5) unsigned
* businessID : smallint(5) unsigned
* categoryID : smallint(5) unsigned
* personID : smallint(5) unsigned
* reviewID : smallint(5) unsigned
* title : varchar(255)
* startDate : date
* endDate : date
* summary : varchar(255)
* img1 : varchar(255)
* img2 : varchar(255)
* img3 : varchar(255)


Service
* serviceID : smallint(5) unsigned
* serviceType : varchar(255)
* categoryID : smallint(5) unsigned


ProjectService
* projectID : smallint(5) unsigned
* serviceID : smallint(5) unsigned

The Problem

I need a query to return a table which has 15 projects, 3 from each category.. This fine and I can achieve but i also need to return the services from the projectservice table that go with each of these projects..

So far I have

(SELECT ps.*, pro.* 
FROM `project` AS pro
JOIN `projectservice` AS ps
ON ps.projectID=pro.projectID
WHERE pro.categoryID = 1  
ORDER BY pro.projectID DESC LIMIT 3)
UNION DISTINCT
(SELECT ps.*, pro.* 
FROM `project` AS pro 
JOIN `projectservice` AS ps
ON ps.projectID=pro.projectID  
WHERE pro.categoryID = 2  
ORDER BY pro.projectID DESC LIMIT 3)
..etc..

This correctly returns 3 projects for each category... however it will return the same project more than once for each service.

I need is;

  1. To return 3 (and only 3) different projects (projectID) for each of the 5 different categories (categoryID)

  2. To add the 1 or more services (serviceID) to the end of each row. So one row could be projectID=3, categoryID=3, serviceID=5 .... while the second row could be projectID=1, categoryID=3, serviceID=15, serviceID=7, serviceID=4 (note each row has a different amount of serviceID)

As far as i'm aware, '2.' is not possible as there must be a set amount of cells in each row... So what i'm after is the best query to achieve this goal...

The way I see it its either a result where rows with the same projectID and serviceID are repeated for every different serviceID recorded (in the projectservice table that matches that projectID)

or two seperate queries where the first just finds x3 distinct projectID for each of the x5 categoryID, while the second query uses those projectID to return all the records in the projectservice table that match that projectID....

or is there a better way???

web app designer
  • 1
  • 2
  • Please read about asking questions & MCVEs in the help center.. – philipxy Jan 27 '16 at 01:41
  • It's not clear what your requirement is for the 15 projects and 5 categories. Do you mean that that will be the content of the tables? Or that projects and/or categories will be input as lists or tables in addition to the tables Project, ProjectService & Service? Or that you want to get the results in batches of certain size? Or what? Please give a complete example of inputs and outputs (of each format) for a complete use case. – philipxy Jan 28 '16 at 00:49
  • Your comments re 3/5/15 are still unclear. What is a "project div"? What is a "spare div" and how does it differ from "created divs". You seem to be talking about some UI that you have not explained but also seem to be mixing terms for graphics with terms for content. Your edited question now asks for what format is best but that is a separate question and it's not clear that we can help you with that UI decision. I still don't get anything from your comments helpful for determining what form would be best for your results. – philipxy Jan 28 '16 at 01:21
  • Are you planning to iterate returning & displaying projects three at a time? – philipxy Jan 28 '16 at 18:29

0 Answers0