CODEWITHSUNDEEP
mysqlsql
Jora
Jora

Reputation: 23

How to compare value from next row?

I have table called Order

   OrderID        CustomerID        IsPaid  
--------------------------------------------
    1              12                 no      
    2              12                 yes
    3              13                 no      
    4              13                 no
    5              14                 yes      
    6              14                 yes

and i have table called Customer

   CustomerID      Cust_name    
--------------------------------
    12              John                       
    13              Nick                 
    14              Paul

I need to dispaly those customers, who has paid for order, but hasnt paid for previous order, so result should be 

   CustomerID      Cust_name    
--------------------------------
    12              John

Upvotes: 2

Views: 84

Answers (5)

jasinth premkumar
jasinth premkumar

Reputation: 1413

this way you will get output only if exist

SELECT *
FROM   customer
       INNER JOIN order
               ON customer.customerid = order.customerid
WHERE  ispaid = 'yes';

Upvotes: 0

Robert Kock
Robert Kock

Reputation: 6018

You might try this:

SELECT   DISTINCT
         cust.CustomerID,
         cust.Cust_name
FROM     customer cust
JOIN     order    paid
  ON     cust.CustomerID = paid.CustomerID
 AND     paid.IsPaid = 'yes'
JOIN     order    unpd
  ON     cust.CustomerID = unpd.CustomerID
 AND     unpd.IsPaid = 'no'
 AND     unpd.OrderID < paid.OrderID
ORDER BY cust.CustomerID,
         cust.Cust_name;

Maybe it's a little less expensive doing it as follows:

SELECT   DISTINCT
         cust.CustomerID,
         cust.Cust_name
FROM     customer cust
JOIN     order    paid
  ON     cust.CustomerID = paid.CustomerID
 AND     paid.IsPaid = 'yes'
WHERE    EXISTS (SELECT *
                 FROM   order unpd
                 WHERE  cust.CustomerID = unpd.CustomerID
                   AND  unpd.IsPaid = 'no'
                   AND  unpd.OrderID < paid.OrderID)
ORDER BY cust.CustomerID,
         cust.Cust_name;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do aggregation :

select c.CustomerID, c.Cust_name
from Customer c inner join
     Order o
     on o.CustomerID = c.CustomerID
group by c.CustomerID, c.Cust_name   
having min(o.IsPaid) <> max(o.IsPaid);

Upvotes: 1

DxTx
DxTx

Reputation: 3357

You could do something like this..

SELECT t1.customerid,
       Max(cust_name) AS cust_name
FROM   table1 t1
       INNER JOIN table2 t2 using (customerid)
GROUP  BY t1.customerid
HAVING Group_concat(ispaid) = "no,yes"

Online Demo: Link

Table Structure and Sample data

CREATE TABLE Table1(
    OrderID int,
    CustomerID int,
    IsPaid varchar(03)
);

CREATE TABLE Table2(
    CustomerID int,
    Cust_name varchar(255)
);

INSERT INTO Table1
VALUES 
(1, 12, 'no'),
(2, 12, 'yes'),
(3, 13, 'no'),
(4, 13, 'no'),
(1, 14, 'yes'),
(2, 14, 'yes');

INSERT INTO Table2
VALUES  
(12, 'John'),
(13, 'Nick'),
(14, 'Paul');

Upvotes: 0

FizzBuzz
FizzBuzz

Reputation: 693

Since your question specifically states "hasn't paid for previous order", window functions should be your goto here!

SELECT *
FROM
(
    SELECT *,
           LAG(IsPaid) OVER (PARTITION BY CustomerID ORDER BY OrderId) AS IsPreviousOrderPaid
    FROM #Orders AS o
) ordersWithPrevious
    INNER JOIN #Customer AS c
        ON c.CustomerId = ordersWithPrevious.CustomerID
WHERE ordersWithPrevious.IsPaid = 1
      AND ordersWithPrevious.IsPreviousOrderPaid = 0;

Upvotes: 0

Related Questions