...

View Full Version : Join multiple tables, then join some more...



pepsi_max2k
04-13-2010, 08:29 PM
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:


SELECT * FROM
( stock
LEFT JOIN products1, products2
ON stock.productid = products1.id OR products2.id )


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... :confused:

Old Pedant
04-13-2010, 08:32 PM
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.

Old Pedant
04-13-2010, 08:34 PM
Hmmm...but maybe what you are really after is

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.

pepsi_max2k
04-13-2010, 08:39 PM
:( 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 :thumbsup: ...

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...


$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. :confused:


EDIT:


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

that works.


$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.


$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

Old Pedant
04-13-2010, 09:00 PM
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:


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.)

Old Pedant
04-13-2010, 09:02 PM
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.

pepsi_max2k
04-13-2010, 09:03 PM
>> 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.


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' :confused: 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.

Old Pedant
04-13-2010, 09:17 PM
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


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.

pepsi_max2k
04-13-2010, 09:19 PM
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 :thumbsup: I think I give up for today now. At the rate of one query a week, I should be finished sometime in 2050... :rolleyes:

Old Pedant
04-13-2010, 09:43 PM
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.

pepsi_max2k
04-16-2010, 11:45 AM
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:


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.


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. :confused:

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


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....

Old Pedant
04-16-2010, 08:57 PM
Yeah, I think URL rewriting might be the way to go. More user friendly, anyway.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum