Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Select from multiple tables with different columns

    I'm looking for some help - I don't even know if it's possible.

    Here's basically what I want to do:

    Code:
    SELECT * FROM stock_adjustment, goods_in, completed_order_items WHERE sku = '$sku' ORDER BY datetime DESC
    The tables have different columns in them - but all contain at least sku and datetime.

    I tried to do a LEFT and RIGHT OUTER on both - like this:

    Code:
    SELECT A.*, B.* FROM stock_adjustment AS A LEFT JOIN goods_in AS B ON A.sku = B.sku UNION ALL SELECT A.*, B.* FROM stock_adjustment AS A RIGHT JOIN goods_in AS B ON A.sku = B.sku WHERE A.sku = '$sku' OR B.sku = '$sku'
    But the results seem to be random (they're not random - they're exactly the same set of results no matter the sku provided - I just haven't worked this out yet).

    If anyone could help it'd be appreciated

  • #2
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I should probably add a reason for why I want this - I'm building a stock management system and would like a 'sku audit' page like so:

    [Date ] [Username ] [Type ] [qty] [on hand]
    [01/01/2013] [Jon Jarman] [Adjustment] [-20] [16]
    [02/01/2013] [Jon Jarman] [Sale ] [ -2 ] [14]
    [03/01/2013] [Warehouse] [Goods In ] [ 10 ] [24]

    Does this make sense?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I don't understand why you are trying to do both LEFT and RIGHT joins.

    And in any case your use of WHERE at the end there just converted your RIGHT join into an INNER join, unbeknownst to you.

    It would help a lot if you would show all the *RELEVANT* fields in each table and, if you think it is not obvious, explain what each field is used for.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And you should clearly *NOT* be doing SELECT * or SELECT A.*, B.*. You *NEED* to SELECT only the fields required to create the final display.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Hey, wait a minute!!!! Where in the world are you going to get the onhand numbers???

    stock_adjustment, goods_in, completed_order_items: Aren't those *ALL* ways of CHANGING the stock on hand? So where is your "current_inventory" table or equivalent??? Or do we have to assume that the inventory for each SKU starts at zero?

    Because if that's true, then your actual output would have to be something like
    Code:
    [Date ]      [Username ]  [Type ]      [qty] [on hand]
    [01/01/2013] [Jon Jarman] [Adjustment] [-20] [-20]
    [02/01/2013] [Jon Jarman] [Sale ]      [ -2] [-22]
    [03/01/2013] [Warehouse]  [Goods In ]  [+10] [-12]
    since we have no starting value for [on hand] so we have to assume zero.
    Last edited by Old Pedant; 09-04-2013 at 08:03 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi sorry for the late reply -

    I've found a way to do it - I'm just not sure it's the most efficient way of doing it.

    Current inventory is already loaded into a variable stored elsewhere.

    Code:
    SELECT sku, adjusted, username, datetime, 'stock_adjustment' source FROM stock_adjustment WHERE sku = $sku UNION ALL SELECT sku, adjusted, username, datetime, 'goods_in' source FROM goods_in WHERE sku = $sku UNION ALL SELECT sku, picked * -1, picker, datetime, 'picklist' source FROM picklists WHERE sku = $sku ORDER BY datetime DESC
    I've got three methods of adjusting stock;
    • Stock Adjustment - sku, adjusted, username, datetime
    • Goods In - sku, adjusted, username, datetime
    • Pick List - sku, picked, picker, datetime



    My goal is to select all records for a certain sku, and sort them by date time showing all stock movements for that sku.

    I'll show an image depicting what is happening right now...

    Thanks for your help so far!



  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •