CODEWITHSUNDEEP
mysqlsqlloops
ag_1812
ag_1812

Reputation: 159

Loop proceeding each row in query

There are two tables:

TABLE 1: My books

BOOK     PRICE
book1    45
book2    21
book3    7
book4    95
book5    32
etc

TABLE 2: Not my books

OWNER     BOOK     PRICE
owner1    book1    32
owner2    book1    14
owner2    book2    3
owner3    book3    7
owner4    book4    3
etc

So, in table 1 we have only one book1, only one book2, etc. In the table2 we can have one or many books1, or books2, etc.

I want to know lowest difference in price between my books (table1) and not my books (table2). (This order is important, doesn’t matter if I obtain a negative value in price).

In other words, I want to do something like this:

price (of books1 from table1) - price (of books1 from table2)

Given that we can have many prices for books1 in the table2, I have to do something like this

price (of book1 from table1) - price1 (of book1 from table2)
price (of book1 from table1) - price2 (of book1 from table2)
….

And after, select the minimum value between obtained results.

The logic of my current query is:

SELECT
table1.price - table2.price
FROM
table1
JOIN table2 ON table1.book = table2.book

But it doesn’t work because it returns more than 1 row (for table2).

So my question is: Do I have to use a loop (cursor??? can I use it here?) or there is any other way to do this?

Many thanks for any advice!

EDIT

I tried 

SELECT
min(table1.price - table2.price) AS ‘price difference’
FROM
table1
JOIN table2 ON table1.book = table2.book

It doesn’t work.

Expected result:

BOOK    PRICE DIFFERENCE
book1    13
book2    18
book3    0
    etc

Upvotes: 0

Views: 38

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Try with min grouping operator

SELECT my_books.BOOK,
min(abs(table1.price - table2.price))
FROM table1
JOIN table2 ON table1.book = table2.book
group by my_books.BOOK

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

I think you need this query, but hard to say for sure without expected results.
The CASE END is there to make the result always positive with (my_books.PRICE - not_my_books.PRICE) * -1 if books.PRICE - not_my_books.PRICE is a negative number.
I know MySQL supports ABS() to do the same, but this query is ANSI SQL so it will work in most database systems.

Query

SELECT 
   my_books.BOOK
 , MIN(
     CASE 
       WHEN my_books.PRICE - not_my_books.PRICE < 0
       THEN (my_books.PRICE - not_my_books.PRICE) * -1 
       ELSE my_books.PRICE - not_my_books.PRICE 
     END
   ) AS price_difference
FROM 
 my_books
INNER JOIN
 not_my_books
ON
 my_books.BOOK = not_my_books.BOOK
GROUP BY 
  my_books.BOOK

Result

| BOOK  | price_difference |
| ----- | ---------------- |
| book1 | 13               |
| book2 | 18               |
| book3 | 0                |
| book4 | 29               |

see demo

Upvotes: 1

Related Questions