[SOLVED] query troubleshoot

[oliveralden]

Hoping someone here can help me figure out what's wrong with my statement. 

 

FYI: Mysql version 5.0.51a-community

 

The statement:

SELECT * FROM `cpLinkedProducts` as A left join `cpProductsValues` as B ON (A.id_product = B.id_product) WHERE `id_category` = '21' AND `id_productfield` = '2' AND `value` LIKE 'c%';

 

The 2 tables in question:

 

cpProductsValues:

id_productvalue id_productfield id_product value

52 2 5 Nelson, Willie

53 3 5 The Ghost, Part 1

54 4 5 Masked Weasel

56 8 5 http://www.amazon.com/gp/music/clipserve/B000ARG27...

48 3 3 The Ghost, Part 3

25087 8 5053 http

[oliveralden]

Sorry, didn't finish and it posted accidentally. Try again:

 

My statement:

SELECT count(*) FROM `cpLinkedProducts` as A left join `cpProductsValues` as B ON (A.id_product = B.id_product) WHERE `id_category` = '21' AND `id_productfield` = '2' AND (`value` LIKE 'a%' OR `value` LIKE 'b%' OR `value` LIKE 'c%');

 

table #1 cpLinkedProducts:

id_linked id_category id_product modified created

30 26 5 2006-09-04 11:26:26 2006-09-04 11:26:26

8535 50 2799 2008-01-03 17:32:08 2008-01-03 17:32:08

8534 21 2799 2008-01-03 17:32:08 2008-01-03 17:32:08

13986 21 4496 2008-06-07 14:28:51 2008-06-07 14:28:51

63 22 15 2006-09-07 06:59:37 2006-09-07 06:59:37

1257 128 3 2007-01-08 05:06:43 2007-01-08 05:06:43

 

table #2 cpProductsValues

id_productvalue id_productfield id_product value

52 2 5 Nelson, Willie

53 3 5 The Ghost, Part 1

54 4 5 Masked Weasel

56 8 5 http://www.amazon.com/gp/music/clipserve/B000ARG27...

48 3 3 The Ghost, Part 3

25087 8 5053 http

 

What's supposed to happen:

The result should be a #, and the # represents a list of each artist (artists turn up every 7th value, always during id_productfield 2), but only the ones in category 21 ('rock'), and only the ones beginning with letters a, b, & c. There should be 152 items in the list.

 

What's actually happening:

I get a list of 143. Some items I didn't expect to turn up are there (like the artist '13th Floor Elevators', and also 'Brian Eno', even though I checked, and the artist field is stored in the database as 'Eno, Brian'). Also, some things which should be there are missing ('Current 93' is one- I checked the database entry and it has all 3 values: id_category=21, id_productfield=2, value starts with 'C').

 

Any ideas where I've gone wrong?

 

Thanks for your time.

[fenway]

It sounds like you're checking the left joined table's fields in the where clause, which would have been NULLed out, so that might explain it... hard to tell without table prefixes.

[oliveralden]

Hmm. Okay.

 

Well, if I read you correctly & use table prefixes like so:

 

SELECT count(*) FROM `cpLinkedProducts` as A left join `cpProductsValues` as B ON (A.id_product = B.id_product) WHERE `A.id_category` = '21' AND `B.id_productfield` = '2' AND (`B.value` LIKE 'a%' OR `B.value` LIKE 'b%' OR `B.value` LIKE 'c%');

 

then I get a mysql error, so no number at all rather than the wrong one.

 

I do definitely want to call columns from both tables (hence the join). Is there a more syntactically appropriate way to do so?

[fenway]

Drop all those backticks and you won't get any syntax errors.  Then you need to move some conditions on the B table to the ON clause -- possibly all of them (untseted)

 

SELECT count(*) FROM cpLinkedProducts as A left join cpProductsValues as B ON (A.id_product = B.id_product AND B.id_productfield = '2' ) WHERE A.id_category = '21' AND (`B.value` LIKE 'a%' OR B.value LIKE 'b%' OR B.value LIKE 'c%');

[oliveralden]

That's helpful, so much appreciated, and it got rid of the syntax error. Also I think I've figured out why my query is only showing 143 instead of 152 items returned. Looks like I've been counting the number of rows in the database using one field, but echoing another field, and although they're very similar, they don't match up exactly.

 

Back to the drawing board for me then, but thanks again.