OT: need help with mysql (join ?)

[horst]

Hi, I'm not sure if I can ask this here because it has nothing to do with PW, so - if is unwanted please tell me.

I'm actually working on a old site of mine that runs with Gallery2. There I have added 3 userfields to albums and now have to update all data (300 albums). Because it is so very very slow with updating through its own admin interface, I want to write a short interactive update wizzard script but stuck with the mysql-select query.

I need to query two tables in a (old) mysql 4 DB. From the first Table I need this:

SELECT g_id,g_title,g_summary,g_description FROM g2_Item WHERE g_canContainChildren=1

But I want only get the rows in result where are not set all data in the second table:

the second table (g2_CustomFieldMap) has fields: g_itemId,g_field,g_value 
g_field can contain: 'custom_fn1','custom_fn2','custom_fn3' for each g_itemId 
(g_itemId is g_id from first Query)

Is it possible to call this all together as one query?

Any help from mysql-specialists are welcome

[diogo]

I don't have a clue about the answer, but you can post this here for sure. That's what the dev talk forum is here for!

[teppo]

Umm.. perhaps something as simple as this would work (writing from memory and not tested at all.. and I do have to admit that I wasn't 100% sure which values you actually wanted to find):

SELECT t1.g_id, t1.g_title, t1.g_summary, t1.g_description FROM g2_Item t1, g2_CustomFieldMap t2 WHERE t1.g_id = t2.g_itemId AND t1.g_canContainChildren=1 AND (t2.g_field IS NULL OR t2.g_value IS NULL);

Slightly offtopic: I find this representation of different join types (even if it's not entirely correct) very useful when trying to get those right..

[horst]

Umm.. perhaps something as simple as this would work (writing from memory and not tested at all.. and I do have to admit that I wasn't 100% sure which values you actually wanted to find):

SELECT t1.g_id, t1.g_title, t1.g_summary, t1.g_description FROM g2_Item t1, g2_CustomFieldMap t2 WHERE t1.g_id = t2.g_itemId AND t1.g_canContainChildren=1 AND (t2.g_field IS NULL OR t2.g_value IS NULL);

Slightly offtopic: I find this representation of different join types (even if it's not entirely correct) very useful when trying to get those right..

Hi teppo,

thanks for the help and the useful link. I have tried with the explanation on that site, but I have solved it only 90% with this query:

$q = 'SELECT t1.g_id, t1.g_title, t1.g_summary, t1.g_description FROM g2_Item t1 LEFT JOIN g2_CustomFieldMap t2 ON t1.g_id = t2.g_itemId WHERE t2.g_itemId is NULL AND t1.g_canContainChildren=1';

it returns all rows from t1 that have no matching row in t2. But I want to get all that do not have 3 matching rows in t2:

sample data of t1:

g_id = 1000, g_summary = 'my summary1' , etc.

g_id = 2000, g_summary = 'my summary2' , etc.

g_id = 3000, g_summary = 'my summary3' , etc.

g_id = 4000, g_summary = 'my summary4' , etc.

sample data of t2:

g_itemId = 1000, g_field = 'custom_fn1', g_value = 'myValue1'

g_itemId = 1000, g_field = 'custom_fn2', g_value = 'myValue2'

g_itemId = 1000, g_field = 'custom_fn3', g_value = 'myValue3'

g_itemId = 2000, g_field = 'custom_fn1', g_value = 'myValue1'

g_itemId = 3000, g_field = 'custom_fn1', g_value = 'myValue1'

g_itemId = 3000, g_field = 'custom_fn3', g_value = 'myValue3'

The wanted select should return the rows for g_id: 2000, 3000, 4000, because only g_id 1000 has all 3 rows in table t2.