Jump to content

How do i query these lines in one single line someone help please??


Ronel

Recommended Posts

SELECT `itemname`,`poqty` FROM `history` WHERE action = 'Added qty'
SELECT `received_qty` FROM `history` WHERE action = 'Stock Received'
SELECT `outgoing_qty` FROM `history` WHERE action = 'Outgoing Record Recorded'

How do i query these lines in one single line someone help please??

Below is my image for database history.

save.png

Link to comment
Share on other sites

What requinix is possibly saying is to use a WHERE clause that includes all of your 'ifs' in one query statement.  You may want to use an ORDER BY clause at the same time.   Suh as:  Where fld='xxx' or fld == 'yyy' or fld  ='zzz' order by fld

 

Link to comment
Share on other sites

13 minutes ago, Ronel said:

it has several entries with same itemnames?

Well, yes it does, because that's what's in your data! 

What you're missing is the action that caused each row to appear in your results.  Include the action column and your data should make a little more sense. 

What are you trying to achieve?  Calculation of total stock levels based on actions against each item? 

For that you'd want something like this: 

select 
  id
, sum( 
  case action
  when 'Added Qty' 
  then poqty
  when 'Stock Received' 
  then received_qty
  when 'Outgoing Record Recorded' 
  then - outgoing_qty 		/* negate value to deduct from total */
  end 
  ) qty 
from table1 
group by id 
order by action ; 

Bear in mind that ordering by a text field (whose values might change over time) could give you headaches. 
It might be better to codify these values (into a "Lookup" Table) so that you can sequence them reliably.  

Regards, 
   Phill  W.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.