CODEWITHSUNDEEP
mysqlsql
MD40
MD40

Reputation: 325

How to retrieve data from table with conditions?

I am developing to database view in mysql server to get status of the grns by joining grns to comment table. I want to display grn is rejected or approved. There may be several comments for single grns on comment table.

If there is single "Approval" for grn in comment table status of the grn must be return as "Approved",otherwise "Rejected" Or "Pending" Here is the dummy of two tables.

grn and comment tables

expected results:

G1 - rejected
G2 - approved
G3 - approved
G4 - approved
G5 - approved

Upvotes: 0

Views: 276

Answers (4)

MD40
MD40

Reputation: 325

Finally I found all the functions following above code. Above question was to develop the database view in below.

CREATE OR REPLACE VIEW summery AS

    SELECT
    g.id,
    g.supply_date,
    g.grn_no,
    COUNT(b.bag_no) AS bags,
    CONCAT(s.fname," ", s.lname) AS name,
    SUM(b.weight) AS qty,
    AVG(b.bag_mc) AS mc,
    (g.dust_initial/g.dust_weight)*100 AS dust,
    (g.ubs_initial/g.ubs_weight)*100 AS ubs,
    p.fraction_1 AS fraction_1,
       CASE
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'approved') THEN 'Approved'
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'approved with concession') THEN 'Approved with Concession'
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'concession required') THEN 'Concession Required'
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'rejected') THEN 'Rejected'
       ELSE 'pending' END AS status
FROM grns g
    JOIN suppliers s ON s.id = g.supplier_id
    JOIN psds p ON g.id = p.grn_id
    JOIN bags b ON g.id = b.grn_id
GROUP BY g.id
ORDER BY g.id DESC

Upvotes: 0

asonagra
asonagra

Reputation: 126

You can use group_concat(flatten) function

Select
`grn-no`, 
Case when status like ('%approved%') then 'approved' else 'rejected' end as status 
From
(Select  
`grn-no`, 
 group_concat(status) as status 
 From grn 
 left join comment 
 on grn.id = comment.grn_id  
 group by 1) a

Upvotes: 2

Nick
Nick

Reputation: 147146

You can do this with a test whether rows exist in comment with either approved or rejected status for each grn-no value. If a grn is neither approved or rejected, the status is set to pending.

SELECT g.`grn-no`,
       CASE WHEN EXISTS (SELECT * FROM comment c WHERE c.grn_id = g.id AND c.status = 'approved') THEN 'approved'
            WHEN EXISTS (SELECT * FROM comment c WHERE c.grn_id = g.id AND c.status = 'rejected') THEN 'rejected'
            ELSE 'pending' END AS status
FROM grn g

Output:

grn-no  status
G1      rejected
G2      approved
G3      approved
G4      approved
G5      approved

Demo on dbfiddle

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can use a correlated subquery

select concat(grn_no,
              ' - ',
              coalesce((select status
                         from comment
                        where status = 'approved'
                          and grn_id = g.id
                        group by grn_id),
                       'rejected')) "Result"
  from grn g

Demo

Upvotes: 2

Related Questions