Geek asks and answers

Nested query and grouping by joins (SQL)

I need help writing a request to get some information, but I am having trouble writing it.

[table_People]
int id
var name

[table_Tools]
int id
var name

[table_Activity1]
int person_id
int tool_id
date delivery_date

[table_Activity2]
int person_id
int tool_id
date installation_date

The request should return a list of all people and the name of the most recent tool that they used in activity 1 or 2 (the last action that occurred between them). A.

SELECT
    people.id   AS personId,
    people.name AS personName,
    (
        SELECT
            tools.name AS toolName
        FROM
            activity1
        JOIN
            tools ON tools.id=activity1.tool_id
        WHERE
            activity1.id=people.id
        UNION ALL
        SELECT
            tools.name AS toolName
        FROM
            activity2
        JOIN
            tools ON tools.id=activity2.tool_id
        WHERE
            activity2.id=people.id
        ORDER BY
            installationDate,deliveryDate
    ) AS toolName
FROM
    people
ORDER BY
    people.name
ASC

The problem I am facing is that I cannot sort by date (delivery or installation), because I get errors because they are different column names.

+3
source share
7 answers

UNION . , , , , SELECT.

UNION , SELECT, ( , ).

SELECT.

LIMIT, :

SELECT
    people.id   AS personId,
    people.name AS personName,
    (
        SELECT
            tools.name AS toolName, delivery_date
        FROM
            activity1
        JOIN
            tools ON tools.id=activity1.tool_id
        WHERE
            activity1.id=people.id
        UNION ALL
        SELECT
            tools.name AS toolName, installation_date
        FROM
            activity2
        JOIN
            tools ON tools.id=activity2.tool_id
        WHERE
            activity2.id=people.id
        ORDER BY
            deliveryDate
        LIMIT 1
    ) AS toolName
FROM
    people
ORDER BY
    people.name
ASC

, :

SELECT fish FROM sea
UNION
SELECT dog FROM land
ORDER BY fish

, :

SELECT fish AS animal FROM sea
UNION
SELECT dog AS animal FROM land
ORDER BY animal

, , , , sticks.

+4

, . , limit ( rownum = 1 Oracle MSSQL):

SELECT people.id   AS personId,
       people.name AS personName, 
       (SELECT toolname
        FROM ((SELECT tools.name AS toolName, delivery_date as thedate
               FROM activity1 a
               WHERE a.PersonId = people.id
              ) union all
              (SELECT tools.name AS toolName, installation_date as thedate
               FROM activity2 a
               WHERE a.PersonId = people.id
              )
             ) a join
             tools t
             on a.toolsid = t.toolsid
        order by 2 desc
        limit 1
       ) AS toolName
FROM people
ORDER BY people.name ASC

, .

+2

, select.

:

select name from people order by name

select a1.delivery_date, t.name from activity1 a1, tools t 
order by a1.delivery_date,t.name

order by. select tools.name as toolname, .

0

Do

SELECT
    people.id   AS personId,
    people.name AS personName,
    IF (
        (SELECT
            deliveryDate AS dDate
        FROM
            activity1
        WHERE
            person_id=personId) --assuming you have only one row returned here, else limit by some condition
        >
        (SELECT
            installationDate AS iDate
         FROM
            activity2
         WHERE
            person_id=personId) --assuming you have only one row returned here, else limit by some condition
         , (SELECT
                tools.name AS toolName
            FROM
                activity1
            JOIN
                tools ON tools.id=activity1.tool_id
            WHERE
                activity1.person_id=personId)
         , (SELECT
                tools.name AS toolName
            FROM
                activity2
            JOIN
                tools ON tools.id=activity2.tool_id
            WHERE
                activity2.person_id=personId)
           ) AS toolName
FROM
    people        
ORDER BY
    people.name
ASC

, . , , .

0

MySQL (, ):

Select * from 
(
  Select toolName, person_id, Max(TargetDate) as MaxDate
  From
        (
            SELECT tools.name AS toolName, activity1.person_id, activity1.delivery_date as targetDate
            FROM
                activity1
            JOIN
                tools ON tools.id=activity1.tool_id
            UNION ALL
            SELECT
                tools.name AS toolName, activity2.person_id, activity2.installation_date as TargetDate
            FROM
                activity2
            JOIN
                tools ON tools.id=activity2.tool_id
        )
 Group by toolName, person_id

) preselect
    join
    (
    Select toolName, person_id, Max(TargetDate)
    From
            (
                SELECT tools.name AS toolName, activity1.person_id, activity1.delivery_date as targetDate
                FROM
                    activity1
                JOIN
                    tools ON tools.id=activity1.tool_id
                UNION ALL
                SELECT
                    tools.name AS toolName, activity2.person_id, activity2.installation_date as TargetDate
                FROM
                    activity2
                JOIN
                    tools ON tools.id=activity2.tool_id
            )) result on result.toolName = preselect.toolName and result.person_id = preselect.person_id and result.TargetDate = preselect.MaxDate
0
SELECT
    p.id             AS person_id
  , p.name           AS person_name
  , CASE WHEN COALESCE(a1.delivery_date, '1000-01-01')
              > COALESCE(a2.installation_date, '1000-01-01')
           THEN t1.name
           ELSE t2.name
    END              AS tool_name
FROM 
    People AS p
  LEFT JOIN 
    Activity1 AS a1
        ON (a1.tool_id, a1.delivery_date) =
           ( SELECT tool_id, delivery_date
             FROM Activity1 AS a
             WHERE a.person_id = p.id
             ORDER BY delivery_date DESC
             LIMIT 1
           )
  LEFT JOIN 
    Tools AS t1
        ON t1.id = a1.tool_id
  LEFT JOIN 
    Activity2 AS a2
        ON (a2.tool_id, a2.installation_date) =
           ( SELECT tool_id, installation_date
             FROM Activity2 AS a
             WHERE a.person_id = p.id
             ORDER BY installation_date DESC
             LIMIT 1
           )
  LEFT JOIN 
    Tools AS t2
        ON t2.id = a2.tool_id
0

select people.id as person_id, people.name as person_name, tools.name as toolsname from table_people people left the connection (Select case wheninstall_date> delivery_date, then act2.tool_id else act1.tool_id end as recent_most_tool_id, case when install_date> delivery_date, then act2 .person_id else act1.person_id end as recent_most_person_id from table_activity1 act1 inner join table_activity2 act2 on act1.person_id = act2.person_id) X on people.id = X.recent_most_person_id Tools inner join table_tools on tools.id = X.recent_most_tool_id

0
source

More articles:


All Articles