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 12 of 12
  1. #1
    Regular Coder
    Join Date
    Mar 2004
    Posts
    107
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Join multiple tables, then join some more...

    Hey all, I have 3 tables - stock, products 1, products 2.

    Last two have a similar layout, but both need to be joined to Stock (which links to products1 & 2 by id) to give a single list of products in stock.

    I then need to do further joins to the list of stock.

    And I can't figure out what the correct syntax for joining the first two tables to stock is. I've tried the following:

    Code:
    SELECT * FROM 
    ( stock 
    LEFT JOIN products1, products2
    ON stock.productid = products1.id OR products2.id )
    Code:
    SELECT * FROM 
    ( stock 
    LEFT JOIN products1
    ON stock.productid = products.id
    LEFT JOIN products2
    ON stock.productid = products2.id  )
    And various others, I only seem to get the join results from the first table though, and just blank info where the second product info should be joined. I'm sure you can see what I'm trying to accomplish. Just MySQL isn't so clever...

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    Code:
    SELECT * FROM 
    stock  
    LEFT JOIN products1 ON stock.productid = products1.id
    LEFT JOIN products2 ON stock.productid = products2.id
    Except that you should never do SELECT *, esp. when joining tables that will have the same field names in more than one table.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    Hmmm...but maybe what you are really after is
    Code:
    SELECT * FROM 
    stock  LEFT JOIN (
         SELECT * FROM products1
         UNION 
         SELECT * FROM products2 ) AS U
    ON stock.productid = U.id
    And are you sure you want a LEFT join??

    Again, all three of those SELECTs should *NOT* use SELECT * if it can be avoided.
    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
    Regular Coder
    Join Date
    Mar 2004
    Posts
    107
    Thanks
    5
    Thanked 0 Times in 0 Posts
    first of those is just returning the products1 info, the second is giving an invalid resorce error for the query.

    and thanks again for the help pedant, as you can see i'm still working on the store stuff... slowly... 2 hours and counting just to get one damn query working ...

    and * is just for testing, I'm just trying to get the joins working for now. And as for left joins... still no idea but it should work... just isn't... see for yourself @ http://inaudible.co.uk/games/shop/shop.php (second entry should have a name and packing info too)


    and that second query *really* should work, shouldn't it?

    pic time...

    Code:
    $query="SELECT * FROM 
    stock  LEFT JOIN (
         SELECT * FROM products
         UNION 
         SELECT * FROM store_products) AS U
    ON stock.productid = U.id
     ";

    On the below tables, gives a "supplied argument is not a valid MySQL result resource" error. Why oh why is it not running? Table names are fine. stock's good. id's are good. product tables are good.


    EDIT:

    Code:
    $query="SELECT * FROM 
    stock  LEFT JOIN (
         SELECT * FROM products ) AS U
    ON stock.productid = U.id
     ";
    that works.

    Code:
    $query="SELECT name FROM 
    stock  LEFT JOIN (
         SELECT id, name FROM products 
         UNION
         SELECT id, name FROM store_products ) AS U
    ON stock.productid = U.id
     ";
    does not work

    Maybe becuase the id fields are very slightly different (stock & store_products have 9 chars, products has 8) ?

    ... Would appear so. Kinda works now... But not with products (which actually does have 9 chars, i forgot...).



    Mmm ok deffinately something weird.

    Code:
    $query="SELECT name FROM 
    stock  LEFT JOIN (
         SELECT id, name FROM store_products
         UNION
         SELECT id, name FROM store2_products ) AS U
    ON stock.productid = U.id
     ";
    That works. Both store(2)_products tables are near identicle, with just different ids for different products.

    Changing store2_products to products (my original product table with differing layout, but only selecting an identically standard id and name) does not work, giving a "mysql_numrows(): supplied argument is not a valid MySQL result resource" error. And I can't think why it wouldn't create a union between the first two and not the other two.








    \/ = products, store_products, stock
    Attached Thumbnails Attached Thumbnails Join multiple tables, then join some more...-products.jpg   Join multiple tables, then join some more...-store_products.jpg   Join multiple tables, then join some more...-stock.jpg  
    Last edited by pepsi_max2k; 04-13-2010 at 09:00 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    Need to go try those queries in myphpadmin or similar tool and see what *real* error you get. Those "resource" errors are all PHP and tell you nothing about the underlyin problem in the query.

    NOTE: The UNION *will not work* unless the two tables being unioned have *identical* structure! (Same number of fields in same order.)

    Most of the time when you use a UNION, you *must* be specific in what you SELECT so that the list of fields match on type and order. They don't have to match on names, but type and order, yes.

    Example:
    Code:
    SELECT id, amount, name FROM table1
    UNION
    SELECT productid, price, productname FROM table2
    MySQL is even flexible enough that, for example, "amount" could be an integer and "price" could be decimal. (But not integer and varchar, for example.)
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    Yeah, went and looked at your tables, and of course they aren't at all identical in structured. So you *MUST* select the matching fields, only, to use the UNION.
    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.

  • #7
    Regular Coder
    Join Date
    Mar 2004
    Posts
    107
    Thanks
    5
    Thanked 0 Times in 0 Posts
    >> NOTE: The UNION *will not work* unless the two tables being unioned have *identical* structure! (Same number of fields in same order.)

    Ahh.... does that matter even if you're only selecting a few similar fields from each (ie. just name and id)?

    Any way to create a union or otherwise join the two product tables with stock when they have a different structure? Or any way around that? I guess running two queries, but then I couldn't really sort things together. But then anything in the second products table would probably be a little different anyway and could be listed after the first lot...


    >> Yeah, went and looked at your tables, and of course they aren't at all identical in structured. So you *MUST* select the matching fields, only, to use the UNION.

    Yeah, that still didn't seem to work. I'll try again...

    Nope. And other than one id being auto_increment with no default, and the other having a default with no auto_increment, both name and id (which are the only fields I'm selecting) are identicle.


    SELECT stock.productid, name FROM
    stock LEFT JOIN (
    SELECT id, name FROM store2_products
    UNION
    SELECT id, name FROM store_products ) AS U
    ON stock.productid = U.id
    /\ works.

    Code:
    SELECT stock.productid, name FROM 
    stock  LEFT JOIN (
         SELECT id, name FROM products
         UNION
         SELECT id, name FROM store_products ) AS U
    ON stock.productid = U.id
    = #1271 - Illegal mix of collations for operation 'UNION' i should google this i think...

    wait... that doesn't mean... *checks languages*...


    "name tinytext latin1_swedish_ci "

    "name tinytext latin1_general_ci "


    3 hours. 100 queries. 1 idiot.

    bound to be something simple, eh? Now they both use the same collation, it works.
    Last edited by pepsi_max2k; 04-13-2010 at 09:17 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    It means that two of your supposedly matching fields are using different "COLLATIONS", which is the type of comparison used when matching/comparing strings. For example, one table might have been declared using LATIN1 and the other using UTF8 or who knows what.

    If you use phpmyadmin or some similar tool and do
    Code:
    SHOW CREATE TABLE products;
    SHOW CREATE TABLE store_products;
    that will show you the DEFAULT CHARSET for each table and, if any are declared explicitly, the COLLATION for the fields.
    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.

  • #9
    Regular Coder
    Join Date
    Mar 2004
    Posts
    107
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Yeah, I forgot the new store_products table had decided to use swedish when I set it up. Didn't think it would cause any issues, and didn't notice the difference when checking everything else was the same.... Still, it wouldn't have worked anyway without your help I think I give up for today now. At the rate of one query a week, I should be finished sometime in 2050...

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    You *can* change the collation dynamically, in the query. I forget the exact syntax, but could figure it out if you can't get it from the docs.
    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.

  • #11
    Regular Coder
    Join Date
    Mar 2004
    Posts
    107
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Right, I have the basic stock list working, thanks for the help I'd start a new thread for this next question but... it's become the pepsi forum if I did that so...

    I now need to combine multiple stock entries for one product in to a single (with exceptions) product listing on a php index page. Right now I don't even know whether to do this in MySQL or PHP, let alone how to do it in either. The current (relevant) results are:

    Code:
    stockid 	productid 	new	sealed	condition_item 
    1		000001500	0	0	4
    2		100000001	0	0	NULL
    3		100000001	0	0	5
    4		100000001	1	0	NULL
    5		100000001	1	1	N/A
    6		100000001	1	0	N/A
    On an index page (see http://inaudible.co.uk/games/shop/shop.php currently) I need just one entry for every product id + new + sealed match.

    Ie.

    Code:
    100000001		0		0		= used
    100000001		1		0		= new
    100000001		1		1		= new & sealed
    I've been thinking some aditional select statements in the first query. I don't really need anything other than one occurane of each new/newseal/used item, just the ones with the highest stock id... and that are unsold... i'll get to that later. So, theoretically:

    select (where new = 0 and sealed = 0
    or where new = 1 and sealed = 0
    or where new = 1 and sealed = 1)
    max stockid

    then I have to figure out how to run that for each unique productid. Cept I'm sure that's not right anyway. And it may be better to do in PHP.

    Woohoo, worked this one out all on my own (almost):

    Code:
    SELECT * FROM 
    
    /* Put table in reverse order so group by only selects latest entries. */
    (SELECT * FROM stock
    ORDER BY stockid DESC) AS stock
    
    /* Select only one, latest, entry for each used / new /sealed product */
    GROUP BY productid, new, sealed
    
    
    
    stockid 	productid 	new	sealed	condition_item 
    1		000001500	0	0	4
    3		100000001	0	0	5
    6		100000001	1	0	N/A
    5		100000001	1	1	N/A
    Now on accessing a stock page for a given item (php gen'ed pages named after stockIDs, i think), I can use stockid to reference productid, new and sealed, and then query the table for all entries matching the same productid / new / sealed grouping, and output a list (or stock count) of all similar items. Getting closer...

    Does sort of mean the page name for any given product will keep changing, so no one can hot-link to a product page. Or they could... but they may bring up a sold item... which if I'm not carefult will still present itself for sale... ok, anyway, maybe I can do something with changing http addresses to mask the actual address somehow (eg. inaudible.co.uk/shop/product-name instead of inaudible.co.uk/shop/item.php?id=5(or 6, or 10, or 1045). I'm sure there's a way....
    Last edited by pepsi_max2k; 04-16-2010 at 01:44 PM.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,450 Times in 4,415 Posts
    Yeah, I think URL rewriting might be the way to go. More user friendly, anyway.
    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.


  •  

    Posting Permissions

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