I need assistance on making a join

Question

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 21-Sep-11 10:18am

king'ori

Updated 21-Sep-11 10:26am

fjdiewornncalwe

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

AlexBanu · Answer 1 · 2011-09-22T03:18:00

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.