我需要协助加入 [英] I need assistance on making a join

问题描述

大家好，
我正在努力加入Querry，如果可以的话，请为我提供帮助.
现在，这是我的问题；
考虑下表.

stock_movement具有字段
Movement_id(PK)，
date_，
类别，
foreign_id，
数量);

该表记录了所有从商店搬出的库存；
类别，确定库存去向，
例如如果为0，则返回柜台，如果为1，则返回车间，如果为2，则返回另一个分支，以此类推
所以每个类别我还有另外三个表；
0)表计数器(counter_id，counter_name，counter_description)；
1)表格工作坊(workshop_id，workshop_name，supervisor)；
2)表分支(branch_id，branch_name，branch_manager)；

现在，我需要每天报告库存变动情况；
因此我的查询需要选择data = selected_date的位置，并且每个记录都有一个叙述性列来显示记录的去向(counter_name或workshop_name或branch_name)，具体取决于记录的类别.

我认为解决方案应该像这样；

HI everyone,
I am struggling to make a join querry, kindly assist me if you can.
Now, this is my problem;
Consider the following table;

stock_movement has fields
movement_id (PK),
date_,
category,
foreign_id,
quantity);

This table records all stock moving out of a store;
category, determines where the stock is going,
e.g. if 0, to the counter, if 1 to workshop, if 2 to another branch etc
so i have three other tables for each category;
0) table counters (counter_id, counter_name, counter_description);
1) table workshops (workshop_id, workshop_name, supervisor);
2) table branches (branch_id, branch_name, branch_manager);

Now, i need to make a daily report of stock moving out;
so my query need to select where data = selected_date, and for each record have a narrative column showing where it went (counter_name or workshop_name, or branch_name) depending on the category of the record.

i think the solution should like to something like this;

select sm.date_, sm.quantity, if(category = 0) then (select counter_name from counters where counter_id = sm.foreign_id) else if (category = 1) then 
(select workshop_name from workshops where workshop_id = sm.foreign_id) else 
(select branch_name from branches where branch_id = sm.foreign_id) as target_name from stock_movement

但它不起作用，您能帮我使它起作用吗，

有没有一种方法可以将if放在连接部分上以使其起作用.

在此先感谢

but it does not work, can you help me make it work,

is there a way we can put the ifs on the join part to make it work.

Thanks in advance

推荐答案

您可以尝试一下

YOU CAN TRY THIS

SELECT sm.date_, sm.quantity, alltarget.target_name FROM 
stock_movement as sm  JOIN 
(
SELECT 1 as category, counter_id as id,counter_name as target_name FROM counters
UNION ALL
SELECT 2 as category, workshop_id as id,workshop_name as target_name FROM workshops
UNION ALL
SELECT 3 as category, branch_id as id, branch_name as target_name FROM branches) 
AS alltargets 
ON sm.foreign_id=alltargets.id and sm.category=alltargets.category

如果您想要更快的代码，可以尝试将并集选择放入一个临时表中，在该表上建立一个索引并与之联接，但是那样工作会很费劲.

PS:我没有发表声明，所以您可能会发现一些拼写错误的内容.

If you want a much faster code you can try put the union select into a temporary table make an index on it an join with that, but it will work fain just like that.

PS: I didn''t run the statment so you might find some misspelled stuff.

这篇关于我需要协助加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！