Need to write a multiple SELECT statement in MySQL

Question

I try to create a MySQL Statement with multiple SQL Statements.

What i trying to do:
I get two tables, project & jiraissue.

Table: project

Columns:
ID  decimal(18,0) PK 
pname   varchar(255) 
URL varchar(255) 
LEAD    varchar(255) 
DESCRIPTION text 
pkey    varchar(255) 
pcounter    decimal(18,0) 
ASSIGNEETYPE    decimal(18,0)

Table: jiraissue

Columns:
ID  decimal(18,0) PK 
pkey    varchar(255) 
PROJECT decimal(18,0) 
REPORTER    varchar(255) 
ASSIGNEE    varchar(255) 
issuetype   varchar(255) 
SUMMARY varchar(255) 
DESCRIPTION longtext 
ENVIRONMENT longtext 
PRIORITY    varchar(255) 
RESOLUTION  varchar(255) 
issuestatus varchar(255) 
CREATED datetime 
UPDATED datetime 
DUEDATE datetime 
VOTES   decimal(18,0) 
TIMEORIGINALESTIMATE    decimal(18,0) 
TIMEESTIMATE    decimal(18,0) 
TIMESPENT   decimal(18,0) 
WORKFLOW_ID decimal(18,0) 
SECURITY    decimal(18,0) 
FIXFOR  decimal(18,0) 
COMPONENT   decimal(18,0)

My goal is to get the names of the projects (project.pname) which newest issue is updated before 2012 (jiraissue.UPDATED).

Example:
I get a Project ABC which newest issue was update in 21.11.2012. The other Project XYZ's newest issue was updated last in 08.12.2011.
So my SQL Script should give me the name of the second project XYZ but not the first ABC

A working script to find out which issue is the newest with a STATIC project ID comes here:

SELECT 
    pkey
FROM
    jiraissue
WHERE
    UPDATED = (SELECT 
            max(UPDATED)
        FROM
            jiraissue
        WHERE
            PROJECT = 10472)

But how is the script now, when i want the project names of ALL these projects? All of my trys take a lot of processing time and give a undefinied error back...

EDIT: Now i get the following code:

select 
    p.pname, j.pkey
from
    project p
        inner join
    jiraissue j ON j.ID = (select 
            PROJECT
        from
            jiraissue
        where
            UPDATED = (SELECT 
                    max(UPDATED)
                from
                    jiraissue))
        AND p.ID = j.PROJECT

The Result is but just the first project with the lowest key... How can i browse ALL Projects?

EDIT:

select 
    p.pname, j.pkey, j.UPDATED
from
    project p
        inner join
    jiraissue j
where
    j.ID = (select 
            PROJECT
        from
            jiraissue
        where
            UPDATED = (SELECT 
                    max(UPDATED)
                from
                    jiraissue
                where
                    UPDATED < '2012-01-01 00:00:00'))
        and p.ID = j.PROJECT

It displays all what i want. But just the first project. So i need ALL projects!! How can i select not just 1 project but all projects?

pkey is the contraction of a project. in table project is it e.g for the project AKS-Infra is the pkey AKSI. The pkey of a issue in this project is the pkey + ID so the issue with the id 7 has the pkey ASKI-7 — Michael Schmidt, Nov 30 '12 at 12:35

score 1 · Accepted Answer · answered Nov 30 '12 at 14:56

Start by first WHAT do you want. On a per project basis, what is the latest issue that was updated BEFORE 2012. Ignore the project table at the moment..

select
      JI.Project,
      MAX( JI.Updated ) as LastUpdated
   from
      JiraIssue JI
   group by
      JI.Project
   having 
      LastUpdated < '2012-01-01'

Now, this will give you all your primary information that you want. The HAVING clause is applied AFTER the group by to exclude any projects that are 2012 and future. You can't apply it as the WHERE clause as your sample described you didn't want anything that had a current (such as your ABC project) and only wanted the XYZ project.

Use this as a basis for getting the rest of your data by joining to the project table and joining AGAIN back to the JiraIssue table by that project/update combination

select
      P.*,
      JI2.*
   from
      ( select
              JI.Project,
              MAX( JI.Updated ) as LastUpdated
           from
              JiraIssue JI
           group by
              JI.Project
           having 
              LastUpdated < '2012-01-01' ) PreQuery

         JOIN Project P
            on PreQuery.Project = P.ID

         JOIN JiraIssue JI2
            on PreQuery.Project = JI2.Project
           AND PreQuery.LastUpdated = JI2.Updated

Absolutly great! Thank you so much! It works fine! +1 & Accept ! And now i also know 'PreQuery'! Thank you again! — Michael Schmidt, Dec 03 '12 at 07:32

echo_Me · Answer 2 · 2012-11-30T13:23:23.450

0

give it a try with this

  SELECT pname FROM project p
  INNER JOIN jiraissue j
   ON p.ID = (SELECT  ID
                FROM jiraissue
                WHERE
               UPDATED = (SELECT  max(UPDATED)
                FROM jiraissue ))

edited Nov 30 '12 at 13:23

answered Nov 30 '12 at 10:51

echo_Me

37,078
5
58
78

Thank you for your script. But i need a script which shows me all Projects. Here it shows only the project with the ID 10472... And i got 378 Projects :/ – Michael Schmidt Nov 30 '12 at 12:41
look now my updated , just removed the last where of project, and it should show all projects – echo_Me Nov 30 '12 at 12:44
Now i get 100 rows of the same Project... Just the same project. I try to review your code. Thank you – Michael Schmidt Nov 30 '12 at 12:48
Just edited your code. I need to compare the project ID with the id of the project in issue – Michael Schmidt Nov 30 '12 at 12:52
Okay, i can't edit your code... replace p.key with p.ID & pkey with PROJECT – Michael Schmidt Nov 30 '12 at 13:12
(SELECT PROJECT ... But the problem is, it select all issues of a project. so i get 100 rows of the same project – Michael Schmidt Nov 30 '12 at 13:55
I need something like: (SELECT max(UPDATED) from jiraissue where PROJECT = p.ID)). But this doesn't work... – Michael Schmidt Nov 30 '12 at 13:56

Need to write a multiple SELECT statement in MySQL

2 Answers2