Comparing two tables

[jfreak53]

Ok here is what I'm trying to do. I have this code that retrieves a list of products from one table and displays them, here:

// Build Product Assoc List
$qry="SELECT pr_name FROM products";
$result=mysql_query($qry,$db);
if (mysql_errno()!=0) echo "<p>SQL Error:<br>QRY: $qry<br>ERR: ". mysql_error(). "</p>\n";
while ($myrow=mysql_fetch_assoc($result)) {
foreach ($myrow as $myvarname=>$myvarvalue) $$myvarname=$myvarvalue;
$options .= "<option>$pr_name</option>\n";
}

That works fine, the problem is I want to make sure that it doesn't list any product that is already added to another table. So table number two is messages, I want to make sure that the messages table doesn't already have one of the products I'm listing, if it does I don't want to show that product in the list I am making. How do I do this?

Thanks in advance.

[Fou-Lu]

The easiest way is to add a subquery to you're where clause ignoring ID's in another field. I presume they are linked via index somewhere:

SELECT pr_name FROM products WHERE pr_name NOT IN (SELECT pr_name FROM messages)


Note that this will take a lot more memory to do, so use it sparingly. Another option is to use a join and check on the values. I don't do this often myself, but try this:

SELECT p.pr_name
FROM products p
RIGHT JOIN messages m USING(pr_name)
WHERE m.pr_name != p.pr_name


Does that work? Man, you'd think as a newly appointed DBA I'd be a little less rusty with SQL o.O

[jfreak53]

Awesome thanks for the help.

[Old Pedant]

Ummm...no, Fou-Lu.

SELECT p.pr_name
FROM products p
RIGHT JOIN messages m USING(pr_name)
WHERE m.pr_name != p.pr_name

Example:

products.pr_name
apple
banana

messages.pr_name
fou_lu
apple

Result: No records. Empty set.

I *think* what you want is this:

SELECT p.pr_name
FROM products p
LEFT JOIN messages m USING(pr_name)
WHERE m.pr_name is null;

That gives you just "bananas" as an answer, which is correct, if I understand the question.

(If you care, I can give a horribly lengthy explanation why yours didn't work.)

[Fou-Lu]

I think I got it.
Left join into the record set will produce no matches from the messages table, which is why you need to check for null instead of non-equal. Using a right join would align 'banana' as NULL compared to 'Fou-Lu', whilst the LEFT join would associate 'Fou-Lu' as NULL compared to 'banana'. Since right would be the primary table, there would never be any null matches against the product table from the messages table, so you could never use the right join in this query (unless you of course reverse the table lookups).
Sound about right?

[jfreak53]

Ok guys first off thanks for the help last time, worked great. But I am again in need of assistance with and SQL statement. Except this one is batty! ha ha

I tried a bunch of different joins and I can't get it to work right, so can someone give me some light please, I'm lost.

What I have is this. There are three tables this time. One is a category list, another a product list and yet the other is an xref list that states which category the product belongs to. In the product list there is a field called vendor_id and in the xref list there are three fields, category_id - product_id - product_list. This last one isn't needed here. Now what I want to do is only display the categories that have products in them from vender number X passed in as a variable through GET. Let's say vendor number 2. So somehow the two product tables have to be joined based on which category the select query has at that moment. Then only spit out the categories from the query that has products in them from vendor number 2.

Did I make this too confusing yet? I hope not, I'm confused, I hope you guys aren't. I'm pretty sure that all I have to do is the sub query almost the same as last time but in the sub query just join the two product tables, but I can't get it to work.

Any ideas?

Thanks for any help with this.

[Old Pedant]

It helps *tons* to show your table schemas in some readable form.

As I get it, you have:

table: Categories
catid : int, primary key
catname : text of some kind

table: Products
prodid : int, primary key
prodname : text
vendorid : int, foreign key to vendors table

table: ProductCategories
catid : int, foreign key to Categories table
prodid : int, foreign key to products table


Okay, when I do it like that it's nice and normal and makes sense.

Piece of cake.


SELECT C.catid, C.catname
FROM Categories AS C, ProductCategories AS PC, Products AS P
WHERE C.catid = PC.catid
AND PC.prodid = P.prodid
AND P.vendorid = 2


No funny stuff at all needed.

PHP, so...

$sql = "SELECT C.catid, C.catname
FROM Categories AS C, ProductCategories AS PC, Products AS P
WHERE C.catid = PC.catid
AND PC.prodid = P.prodid
AND P.vendorid = $vendid;";

Just make sure that you "sanitize" $vendid before using it. That is, make sure it is a simple integer value, with no SQL Injection junk in it.

[jfreak53]

Sorry about that, you have the table scheme correct, of course names are different but the layout is correct. The problem is that there is already an sql statement because the system already exists I am adding functionality to it, so I have to add all of this to the end and make it work. Right now the SQL statement selects it's categories then finds child categories, what I want it to do is only select those that have products in it from X vendor like I said. So you have a little bit more from this here is the current SQL statement:

$q = "SELECT category_id, category_thumb_image, category_child_id,category_name FROM category,category_xref ";
$q .= "WHERE category_xref.category_parent_id='$category_id' ";
$q .= "AND category.category_id=category_xref.category_child_id ";
$q .= "AND category.vendor_id='$ps_vendor_id' ";
$q .= "AND category.category_publish='Y' ";

So right now it selects all the categories and I think it selects the child ones too that are published, hence the Y.

So what I have to do is make sure it only selects those that have the products in it from my vender. The vendor ID is passed through GET and I already grabbed that and assigned it to a variable above called "$vendor_id".

So I took your statement you sent me and added it to the end like this:

$q .= "AND $vendor_id IN (SELECT C.category_id, C.category_name
FROM category AS C, product_category_xref AS PC, product AS P
WHERE C.category_id = PC.category_id
AND PC.product_id = P.product_id
AND P.vendor_id = '$vendor_id') ";

I didn't think that would work and it didn't I think because it's selecting the category info not the product vendor info, well that's what I thought, tell me if my assumption is correct, I want to learn? So I moved it around to say this:

$q .= "AND $vendor_id IN (SELECT P.vendor_id
FROM product AS P, category AS C, product_category_xref AS PC
WHERE C.category_id = PC.category_id
AND PC.product_id = P.product_id
AND P.vendor_id = '$vendor_id') ";

That didn't work either, so of course I was wrong, so I tried changing the IN to =. No go of course as you guys probably already know what is wrong with this whole thing by now, but I thought you might like some history.

Ok teach me what I did wrong please? I know I should have given more info from the start, sorry. Oh here is the table print outs:

category` (
`category_id` int(11) NOT NULL auto_increment,
`vendor_id` int(11) NOT NULL default '0',
`category_name` varchar(128) NOT NULL default '',
`category_description` text,
`category_thumb_image` varchar(255) default NULL,
`category_full_image` varchar(255) default NULL,
`category_publish` char(1) default NULL,
`cdate` int(11) default NULL,
`mdate` int(11) default NULL,
`category_browsepage` varchar(255) NOT NULL default 'browse_1',
`products_per_row` tinyint(2) NOT NULL default '1',
`category_flypage` varchar(255) default NULL,
`list_order` int(11) default NULL,
PRIMARY KEY (`category_id`)

product` (
`product_id` int(11) NOT NULL auto_increment,
`vendor_id` int(11) NOT NULL default '0',
`product_parent_id` int(11) NOT NULL default '0',
`product_sku` varchar(64) NOT NULL default '',
`product_s_desc` varchar(255) default NULL,
`product_desc` text,
`product_thumb_image` varchar(255) default NULL,
`product_full_image` varchar(255) default NULL,
`product_publish` char(1) default NULL,
`product_weight` decimal(10,4) default NULL,
`product_weight_uom` varchar(32) default 'pounds.',
`product_length` decimal(10,4) default NULL,
`product_width` decimal(10,4) default NULL,
`product_height` decimal(10,4) default NULL,
`product_lwh_uom` varchar(32) default 'inches',
`product_url` varchar(255) default NULL,
`product_in_stock` int(11) NOT NULL default '0',
`product_available_date` int(11) default NULL,
`product_availability` varchar(56) NOT NULL default '',
`product_special` char(1) default NULL,
`product_discount_id` int(11) default NULL,
`ship_code_id` int(11) default NULL,
`cdate` int(11) default NULL,
`mdate` int(11) default NULL,
`product_name` varchar(64) default NULL,
`product_sales` int(11) NOT NULL default '0',
`attribute` text,
`custom_attribute` text NOT NULL,
`product_tax_id` int(11) default NULL,
`product_unit` varchar(32) default NULL,
`product_packaging` int(11) default NULL,
`child_options` varchar(45) default NULL,
`quantity_options` varchar(45) default NULL,
`child_option_ids` varchar(45) default NULL,
`product_order_levels` varchar(45) default NULL,
PRIMARY KEY (`product_id`)

product_category_xref` (
`category_id` int(11) NOT NULL default '0',
`product_id` int(11) NOT NULL default '0',
`product_list` int(11) default NULL

Is this now overkill? :confused:

[Old Pedant]

I think maybe your big mistake is *having* a vendor_id field in the category table!!!

When you do that, you limit each category to a *SINGLE VENDOR*.

If that's your intent, fine, but it's pretty unusual DB design.

[jfreak53]

I understand that, but that is something totally different, that is the way the system was created. I am nothing more than adding functionality. Really the vendor ID in the cat table is not used anywhere in the system, I think they put it there for future use. What I want to filter is based on the vendor ID in the product table, as you can see by the SQL above.

So what am I missing here? I know it has to be something really simple that I'm am really overlooking.

[Old Pedant]

Yes, but you then said you *DID* use it in that first query:

$q = "SELECT category_id, category_thumb_image, category_child_id,category_name FROM category,category_xref ";
$q .= "WHERE category_xref.category_parent_id='$category_id' ";
$q .= "AND category.category_id=category_xref.category_child_id ";
$q .= "AND category.vendor_id='$ps_vendor_id' ";
$q .= "AND category.category_publish='Y' ";

And that is *POISON* to the rest of the query. From that point on, you are hosed.

But then you really also confuse me, because that query includes
$q .= "WHERE category_xref.category_parent_id='$category_id' ";

But you don't show any such field in your category_xref table in the list of table fields.

And, indeed, I would think that having a field by that name with the meaning I assume it has would be a big mistake. You might have that in the category table, but it clearly shouldn't be in the category_xref table.

I *REALLY* feel you should remove the vendor_id from the category table! It can *ONLY* get you in trouble in the future!

And then in the "addon" that you suggested, you use this:

category_xref.category_child_id
and again I feel that field is in the wrong table.

Anyway, it's really hard for me to comment on the query when you don't show me the real table structure and when you have what I consider major flaws in the table structure.

[jfreak53]

Well to be honest I am not withholding anything from anyone, the xref has NOTHING to do with what I'm trying to do. That is the Xref table that decides if a subcategory is related to a category.

ALL I want to do is read a list of products from the fields I GAVE and see if any of them match the current category and vendor ID, if they do then that category is selected, if not then no.

Why am I withholding things here? What I want to do has nothing at all to do with that other table. And the vendor ID is not for me to decide in category, this might be a flaw according to your thought process and mine, but maybe the original designers had a reason for it I have not found yet because it means nothing to me and I have not been looking.

I am sorry that you feel this way but I came here for help in a subject and I feel I have provided the information needed withholding nothing. I need a simple query to compare that last thing, nothing more. I gave a list of the tables involved and that should be it I would think.

Thank you for everyones help in the past, it has been much appreciated.

[Old Pedant]

OH SHEESH! Slap me upside the head! I am *SO* sorry!

I saw category_xref and stupidly read that as product_category_xref !!

Mea maxima culpa! My face is beet red.

Okay, back in a bit...let me try this again.

[Old Pedant]

I *think* it is as simple as this:

$q = "SELECT C.category_id, C.category_thumb_image, C.category_child_id, C.category_name "
. " FROM category AS C, category_xref AS CX, product_category_xref AS PC, product AS P "
. " WHERE CX.category_parent_id = $category_id "
. " AND C.category_id = CX.category_child_id "
. " AND C.category_publish = 'Y' "
. " AND C.category_id = PC.category_id "
. " AND PC.product_id = P.product_id "
. " AND P.vendor_id = $vendor_id ";


I'm still 90% sure you should not be using the vendor_id field in the category table.

Unless you can come up with a really good reason for that field to be there, I'd get rid of it were I you.

[Old Pedant]

By the way, you don't have to use aliases for the table names (AS C, AS CX, etc.).

I just find it so much more readable to use the aliases, just because it makes the query shorter.

But remember, if you do alias a table name, then you can no longer refer to it (outside the FROM clause) by its full name.

Also, your vendor_id and category_parent_id fields are INT fields, right? So you should *not* be comparing them to strings. That is, you should *NOT* have '...' around $category_id and $vendor_id. Will the query work with them there? Yes, only because MySQL isn't as fussy as some DBs. But I think it means you lose a little bit of the semantic sense of the query, when you see a string where a number clearly should be. And it's good practice to do it right, in case you ever use another DB that is fussier.

[jfreak53]

Ha ha, not a problem, I had a few brain farts today too.

Ok your not going to like me when I say this, ha ha. No go. It didn't work at all, until, I got rid of one thing then it printed out a bunch of things that it is not supposed to print. I tried a bunch of things messing around and I found something. So when I put your code up completely, nothing. But when I get rid of this "product_category_xref AS PC," wow it prints not only the categories but the products, which consequently for testing have the same names as the categories.

This is how it normally displays:

http://codingforums.com/attachment.php?attachmentid=8217&stc=1&d=1266067968

This is with all the products included that it just did:

http://codingforums.com/attachment.php?attachmentid=8218&stc=1&d=1266067976

And of course with any of the things we've tried nothing:

http://codingforums.com/attachment.php?attachmentid=8216&stc=1&d=1266067961

Ok so I've printed out the statements using echo at run time, so I know they are there. But they must be filtering just a little bit wrong.

What it should do is only print out category name one and two for vendor number 2. Category 3 has no products at all in it. And I made sure that the category vendorID was not in the query anymore. So what part could be filtering wrong here? Is there a way just to do a subquery instead, could it be that the category xref is interfering with all the queries? I'm lost entirely.

Thanks for all your help by the way.

[Old Pedant]

I'm sure you're aware that those images didn't show anything. Just some numbers.

Instead of images, just copy/paste the text results, maybe?

Could you try some queries using a query tool, insted of using your PHP code? Just in case??

Find a vendor_id that only appears in a handful of products in your table, if possible.

Ad then do this:

SELECT product_id FROM product WHERE vendor_id = 777;

Where you replace 777 with that particular vendor_id.

How many product_id values show up? Hopefully, no more than a handful??

Okay, so then do this:

SELECT P.product_id, PC.category_id
FROM product AS P, product_category_xref AS PC
WHERE P.vendor_id = 777
AND PC.product_id = P.product_id;

Again replacing 777 with a number of your choice.

How many category_id values do you get from *that*?

And then:

SELECT P.product_id, PC.category_id, C.category_name
FROM product AS P, product_category_xref AS PC, category AS C
WHERE P.vendor_id = 777
AND PC.product_id = P.product_id
AND C.category_id = PC.category_id;

What does *THAT* show you???

Again, don't try do do screen shots; just copy/paste the text output.

*********

p.s.: You could find which product has the fewest vendors, to that you can plug that number into the above in place of 777, by doing this:

SELECT product_id, COUNT(DISTINCT vendor_id) AS vendorCount
FROM product
GROUP BY product_id
ORDER BY vendorCount
LIMIT 1;

Or use LIMIT 10 or so to get a few choices.

[Old Pedant]

It occurs to me that *probably* you need a DISTINCT in your final form query, once we get the bugs ironed out:

$q = "SELECT DISTINCT C.category_id, C.category_thumb_image, C.category_child_id, C.category_name "
. " FROM category AS C, category_xref AS CX, product_category_xref AS PC, product AS P "
. " WHERE CX.category_parent_id = $category_id "
. " AND C.category_id = CX.category_child_id "
. " AND C.category_publish = 'Y' "
. " AND C.category_id = PC.category_id "
. " AND PC.product_id = P.product_id "
. " AND P.vendor_id = $vendor_id ";

But first we have to get *some* results.

[jfreak53]

Ok, I didn't even think about doing that the first time, sorry. Ok so I am running them all in phpMyAdmin on the DB in question. I am running them all on vendor id #3, there is only one product that is registered to them in one category. First query it returned one product, product id #3 it so happens to be, this is correct.

Query 2, again only one return, product id 3 category id 1, this is also correct.

Query 3, again only one return, product id 3, category id 1, and category name 1, all correct.

Now for the fun part! I got it! This is the final query that works, it was the last one before you sent me that worked, what was happening it was spitting out something like ambiguos on the category_id part, so all I had to do is assign it to a table and that was it. What I figure was that since there are multiple tables with that same name it didn't know what to do, am I right?

Here is the working version:

SELECT category.category_id, category_thumb_image, category_child_id, category_name
FROM category, category_xref, product_category_xref AS PC, jos_vm_product AS P
WHERE category_xref.category_parent_id='$category_id'
AND category.category_id = category_xref.category_child_id
AND category.category_publish = 'Y'
AND category.category_id = PC.category_id
AND PC.product_id = P.product_id
AND P.vendor_id = $vendor_id

All I did was assign that category_id a table and that was it. I would have never seen it if I didn't run it in pure SQL.

What did we learn from this grasshopper???

We learned from now one, use SQL if I am having problems with a query since I will get a response from it!!!

Thanks for all your help man, it's much appreciated.

[Old Pedant]

Yes, all too often the system side language you are using--be it PHP or ASP or JSP--will "hide" the real problem. Glad it turned out to be so simple.

[jfreak53]

AHA!!! Gentlemen I have a new revelation! This happens not much, so let me enjoy it while it lasts ha ha.

While running my new query I found out that if there is more than one product belonging to that vendor in X category, then it prints the category name out TWICE, or however many products are there! AHA!!

So my query was wrong. But thanks to the excellent tutelage from Pedant, I ran it in SQL on PHPMyAdmin and found out that it was doing just that! So I re-did the SQL query and tested it in SQL. And after trying many things an epiphany came to me, it hurt when it did too. Why am I comparing the vendor ID, why don't I just run a subquery with my new code and see if the category ID is in that subquery, if it is then I have a match right????!!!

IT WORKED!! So here is my new code, all I did was add it to the end of the original SQL statement:

$q .= "AND category.category_id IN (SELECT C.category_id FROM category AS C, product_category_xref AS PC, product AS P WHERE category.category_id = PC.category_id
AND PC.product_id = P.product_id
AND P.vendor_id = $vendor_id) ";

Again thank you for showing me that using SQL in straight PHPmyadmin or another runner is the best way to go. If I hadn't I would have been there for days trying to figure it out.

[Old Pedant]

*PROBABLY* another answer would have been to simply use DISTINCT in the prior query:

SELECT DISTINCT category.category_id, category_thumb_image, category_child_id, category_name
FROM category, category_xref, product_category_xref AS PC, jos_vm_product AS P
WHERE category_xref.category_parent_id='$category_id'
AND category.category_id = category_xref.category_child_id
AND category.category_publish = 'Y'
AND category.category_id = PC.category_id
AND PC.product_id = P.product_id
AND P.vendor_id = $vendor_id ;

Just for your own edification, you might want to try that, so you'll know about it next time.

[jfreak53]

Hmm, I didn't know nor think of this. I will test out to see what it does, now I'm curious, thanks again.