help with MySql command and multiple fields

[shawjames]

Okay, I have 2 tables. One has a field that is varchar(3), the other has a field that is varchar(9).

I want to select all the varchar(9) fields where there is no varchar(3) that are the same as the first 3 varchar in the varchar(9) field.

table1.field1 is the varchar(9)
table2.field2 is the varchar(3)

I want to do something like this:

SELECT table1.field1 FROM table1,table2 WHERE table1.field1 not like "table2.field2%"

[RossC0]

Try this:

SELECT field1, field2
FROM table
WHERE field1 Like table.field2+'%';

EDIT

Tell a lie mysql doesnt seem to like it...

[RossC0]

All sorted mysql has funny concatenation:

SELECT *
FROM table
where field1 NOT like CONCAT(field2,'%')

[shawjames]

Thanks!!

Okay, that worked. Now I want to group by to only see the first 3 char of that field. Here is what I have so far:

SELECT ds_products.pnum, count(ds_products.pnum) FROM ds_products,ds_mfg WHERE ds_products.pnum not like concat(ds_mfg.mfgnum,"%") group by ds_products.pnum

So what I want to select is only the first 3 char of the field ds_products.pnum. Then I want to count how many of those there are.

Can I do this with the GROUP BY fields? like substr(0,3)?

[RossC0]

Not sure if thats do able in sql - best to return the recordset and then use php the string function!

Good luck!