Click here to Skip to main content
15,905,325 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;

SQL
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


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
Posted
Updated 21-Sep-11 10:26am
v2

1 solution

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


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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900