MySQL case selection

Question

I'm fairly new to using cases in selection statements so I need a bit of help.

I have two tables. Lets call one res and one ser (for resource and serial).

res contains a field id & bar_code_id
ser contains a field id & container_id and they join on the id obv.

Anyway, what I need to do is figure out how to list all of the bar_code_id's of every item who's container_id is equal to a specific bar_code_id's res.id field.

For example:

the container I have bar_code_id is 123456. It is a container and its "container_id" is null, because it cant be placed in the container. the container has several items inside of it, however, the only way I can find the items is by finding every item whos container_id is the same as 123456's res.id

I'm not sure how to tackle this with case statements. I tried something along the lines of:

select case
when res.bar_code_id is '123456' 
then (select res.bar_code_id from res
left outer join ser on ser.id = res.id
where ser.container_id = <somehow the variable of 123456's res.id?>) end
from res

But I know I'm way off. I'm not sure how I would store the res.id as a variable to use later in the comparison of ser.container_id = res.id part...

Anyway I hope thats all fairly clear. If anything is confusing let me know & I appreciate the help.

Edit: Here's what I ended up doing that worked, should anyone need this in the future

set @barcode := 123456;
set @id := (select res.id from res where res.bar_code_id = @barcode);

select res.id, resource_name, res.bar_code_id, container_id from res

left outer join ser
    on ser.item_id = res.id

where container_id = @id

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

First of all, you must declare variables to play with, check out this answer. Then take a look here to build CASE statements in mySQL

PD: If 123456 is a number don't use ' neither " to declare the varable. (At least in mySQL 'n PostgreSQL)

edited May 23 '17 at 10:33

Community

1
1

answered Jan 07 '15 at 18:56

Mark

684
2
9
25

oop, i'm not using any sort of quotes. that was a mistake upon writing this, but thanks! – user3669901 Jan 07 '15 at 18:59
np, did tou check the links? are they usefull? – Mark Jan 07 '15 at 19:00
yeah im looking at them now. they say how to set a variable ~prior~ to the select statement, but what if i need to save a field as a variable? I know bar_code_id before the quary is called, but i dont know res.id of that bar_code_id until i actually look at the table. – user3669901 Jan 07 '15 at 19:03
SET @ var_name = expr [, @var_name = expr] ... http://dev.mysql.com/doc/refman/5.7/en/user-variables.html – Mark Jan 07 '15 at 19:06
I think I got it, thank you! Updating op. If you could double check it for me and see if I made errors that would be appreciated. – user3669901 Jan 07 '15 at 19:32
looks fine from here, don't forget to vote the answer. – Mark Jan 07 '15 at 19:38

score 0 · Answer 2 · answered Jan 07 '15 at 19:06

Let me first start to clarify your "bar_code_id". These are only from the "RES" table... That would be a simple

select r.id, r.bar_code_id from res r where r.bar_code_id = 123456

Now, you want any serial numbers associated with these containers... Add a JOIN (ie: MUST EXIST in Ser table) or a LEFT JOIN (Show the ID REGARDLESS if a match in the SER table)

select
      r.id,
      r.bar_code_id,
      s.container_id
   from
      res r
         JOIN ser s
            on r.id = s.container_id
   where 
      r.bar_code_id = 123456

If you change to a "LEFT JOIN", then all qualified "res" records will be returned and the container_id column will be NULL (ie: did not have a match). The way it is now, it will only show those that match in BOTH tables.

The join is per your sample select where you were trying to describe that the SER's column Container_ID was the join basis to the RES's column ID.

I think I'm a bit confused as to what you are trying to clarify here, but if I'm interpreting this correctly, this isnt quite in the right direction. All you're doing here is selecting the 1 item, 123456. I need to find all of the items who's container_id is equal to 123456's res.id. The join statement doesn't really matter because r.id and s.id have the exact same elements and that is what they join on. — user3669901, Jan 07 '15 at 19:20
@user3669901, then can you please edit your question and put some sample data reflective of each table as basis to follow query. And don't use TABs for alignment, just spaces as TABs do not format well. — DRapp, Jan 07 '15 at 19:31

MySQL case selection

2 Answers2