CODEWITHSUNDEEP
mysql
El Tigre
El Tigre

Reputation: 182

Joining a Table to Itself to Compare Data of Same Column

I have a table that lists items and the item's status in multiple stages. For instance a PIPE goes through Cutting, Welding, Painting. The way the table is set up it lists the PIPE three times and under column STAGE it will have Cutting, Welding, and Painting respectively.

I'd like to compare the table to itself on this column to see what, if any inconsistencies exist between those three stages.

So if a PIPE is still in the STAGE of Cutting it will show that particular row, but if the PIPE is in the STAGE of Welding it will show that particular row instead.

Current data is:

Unique ID      Job #     Stage      Due Date     Status
120473         Job 12    Cutting    1/17/17      Not Complete
120473         Job 12    Welding    1/18/17      Not Complete
120473         Job 12    Painting   1/18/17      Not Complete
120475         Job 17    Cutting    1/26/17      Complete
120475         Job 17    Welding    1/27/17      Not Complete
120475         Job 17    Painting   1/28/17      Not Complete

Desired output would be:

Unique ID      Job #     Stage     Due Date
120473         Job 12    Cutting   1/17/17
120475         Job 17    Welding   1/27/17

Unique ID 120475 is completed in Cutting, so that's why it would show Welding instead.

Upvotes: 0

Views: 61

Answers (2)

1000111
1000111

Reputation: 13519

Here's what I understood:

You want the next incomplete stage for each unique_id.

One way you can solve this problem is using INNER JOIN

SELECT 
t.*
FROM t 
INNER JOIN 
(
    SELECT 
     A.unique_id,
     MIN(COALESCE(B.id, A.id)) AS next_stage_id
    FROM t A 
    LEFT JOIN t B ON A.unique_id = B.unique_id AND A.id < B.id 
              AND A.status='Complete' AND B.status='Not Complete'
    GROUP BY A.unique_id
) AS alias
ON t.id = alias.next_stage_id;

This is a simplified version of your original problem

SEE DEMO

Upvotes: 1

El Tigre
El Tigre

Reputation: 182

Clearly today is a Friday because I was over thinking this.

The following solves my problem:

select a.jobdescription, a.category, a.sequence, a.lotnumber, a.duedate, a.dpm, a.ps, a.stage, a.completed

from qiw_powerbi a

where (a.stage = "approved by client" and a.completed = "not complete") or (a.stage = "released to shop" and a.completed = "not complete")

group by a.catkey
order by a.category

Upvotes: 0

Related Questions