How to use condition in sql

Question

I want to use sql to save the process of moving goods between 2 shops. I have two tables: A:

id	shop	good_sn
1	1	aaa
2	1	bbb
3	1	ccc
4	2	ddd

B:

id	shop_from	shop_to	good_sn
1	1	2	bbb

and I want to select a table like this:

shop	good_sn
1	aaa
1	ccc
2	ddd
2	bbb

Does anyone have any query ideas for this case?

Do you want to remove the record from Table B and update the record in Table A? — Timothy Alexis Vass, Jul 22 '21 at 06:54
can you create example on http://sqlfiddle.com/ ? It can be done using union query and couple of joins. — Dharmang, Jul 22 '21 at 06:56
I want to use select to get the results when the transfer between 2 shops is completed — nhhthong, Jul 22 '21 at 06:57
Normally you would do this by updating table 1 to show the new state of stock in both locations. Or by holding stock in some sort of ledger. stock movement transactions table. (Location, Item, Quantity CHANGE, etc..) — JeffUK, Jul 22 '21 at 06:57

score -1 · Accepted Answer · answered Jul 22 '21 at 06:58

-1

You could use this

SELECT COALESCE(b.shop_to, a.shop) AS shop, 
    a.good_sn
FROM table_a a 
LEFT JOIN table_b b 
ON a.shop = b.shop_from AND a.good_sn = b.good_sn
ORDER BY 1, 2;

answered Jul 22 '21 at 06:58

Pham X. Bach

5,284
4
28
42

And if the goods subsequently move back from shop 2 to Shop 1 or move from Shop 2 to Shop 3 this stops working immediately, right? The whole architecture needs rethinking. – JeffUK Jul 22 '21 at 07:00
@JeffUK if goods move more than 1 time, my query fails to work. In that case I think ` `table_b` should have a time stamp or order to track moving order, that will make us easier to select/update... to get the last location of goods. – Pham X. Bach Jul 22 '21 at 07:05
@PhamX.Bach Thank you very much! Maybe my question is a bit confusing because my English is not good but this is the result I want. – nhhthong Jul 22 '21 at 07:06
@nhhthong as a Warehouse Management implementer/developer, I'm 99% sure this is not the result you want. Certainly not as the basis of a long-term sustainable solution, if it's a school project you'll probably be fine! https://stackoverflow.com/questions/8059682/database-design-inventory-and-sales-system is a good primer – JeffUK Jul 22 '21 at 07:07
@PhamX.Bach if you did that, then deleted table A, you're much closer to a robust solution actually. Table A becomes entirely redundant it's just a view on Table B. – JeffUK Jul 22 '21 at 07:14

How to use condition in sql

1 Answers1