SQL - Select multiple fields from multiple tables

Question

Imagine this:

table1

ID  Type    Priority
1212    A   CRITICAL
1213    B   MAJOR
1214    B   MINOR
1215    A   MAJOR
1216    A   CRITICAL
1217    A   CRITICAL

table2

ID         STATE        CHANGEDATE
1212       Pending      03-06-2015 17:47
1212       Closed       04-06-2015 05:47
1212       InProgress   03-06-2015 15:32
1212       Start        03-06-2015 15:07
1212       Opened       03-06-2015 13:47

• table1 contains all the ID's information
• table2 contains all individual ID's information

What I need: I need to get all ID's information from both tables.

Why am I struggling: Besides my n00b level (lets just say I'm not Valhalla material just yet), I'm unable to correlate the information from the first table with the second.

What I visualized:

ID   Type   Priority    Opened              Closed
1212    A   CRITICAL    03-06-2015 13:47    -
1215    A   MAJOR       06-06-2015 18:00    07-06-2015 18:00
1216    A   CRITICAL    03-05-2015 13:10    04-06-2015 18:00
1217    A   CRITICAL    01-06-2015 11:03    05-06-2015 18:00

UPDATE:

This is what I've tried, but focusing in one particular ID (1212):

SELECT
  MAX (ID_A) AS "ID",
  MAX (STATE_A) AS "ACTION NAME",
  MAX (CHANGEDATE_A) AS "START",
  MAX (STATE_B) AS "ACTION NAME",
  MAX (CHANGEDATE_B) AS "END"
FROM (
SELECT
  ID AS ID_A,
  STATE AS STATE_A,
  CHANGEDATE AS CHANGEDATE_A,
  NULL AS ID_B,
  NULL AS STATE_B,
  NULL AS CHANGEDATE_B
FROM table2
WHERE table2.ID = '1212' AND table2.STATE = 'Start'
UNION ALL
SELECT
  NULL AS ID_A,
  NULL AS STATE_A,
  NULL AS CHANGEDATE_A,
  ID AS ID_B,
  STATE AS STATE_B,
  CHANGEDATE AS CHANGEDATE_B
FROM table2
WHERE table2.ID = '1212' AND table2.STATE = 'Close');

Running this I'll get the following result:

ID   ACTION NAME    START         ACTION NAME   END
1212    Start   03-06-2015 13:47    Close        -

What I need is this same, but this time for all the IDs, generating a list (without specifying an ID).

Answer 1

select  t1.ID,
        t1.Type,
        t1.Priority,
        max(case when t2.State = 'Opened' then ChangeDate end) as Opened,       
        max(case when t2.State = 'Closed' then ChangeDate end) as Closed
from    table1 t1 join table2 t2 on t1.id = t2.id
group by t1.ID,
         t1.Type,
         t1.Priority

Answer 2

You need to use a JOIN in SQL. This allows you to correlate such information.

The basic syntax is below.

You refer to OPENED and CLOSED in your ideal solution, but as this is not in either of your base tables, I am not sure how to calculate this.

select t1.ID,  
       t1.Type,    
       t1.Priority,
       t2.CHANGEDATE
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.REQUESTID