let's see whos good at sql

[Dream]

im breaking my head for about two hours, need a query to get the custom templates from the "template" table. what it needs to do, is a inner join that gets all templates which names doesn't exist in the master style (styleid -1). that's what I was able to come up with :/

SELECT t1.templateid, t1.styleid, t1.title, t2.styleid, t2.title
FROM template AS t1, template AS t2
WHERE t1.templatetype = 'template' AND t2.templatetype = 'template'
AND (t1.styleid <> -1 AND t1.title <> t2.title AND t2.styleid = -1)
AND NOT (t1.styleid = -1 AND t1.title = t2.title AND t2.styleid = -1)
GROUP BY t1.templateid, t2.title
ORDER BY t1.title

[Marco van Herwaarden]

You should look into something like:
SELECT * from template as t1
WHERE t1.title NOT IN (SELECT t2.title FROM template AS t2 WHERE t2.styleid = -1);
This is not the complete statement, but should get you started.

[Andreas]

SELECT customstyle.* FROM template AS customstyle
LEFT JOIN template AS masterstyle ON (masterstyle.title = customstyle.title AND masterstyle.styleid = -1)
WHERE ISNULL(masterstyle.templateid)

[Dream]

thanks kirby, gonna use that one.

im having a problem calling $db->query_read() with it now, doesnt log errors in error_log and it seems to not even try the query. do i need to initialize something to use $db?

[Brad]

thanks kirby, gonna use that one.

im having a problem calling $db->query_read() with it now, doesnt log errors in error_log and it seems to not even try the query. do i need to initialize something to use $db?
If you are working within vBulletin no, if you are including vBulletin's global.php file then no. Otherwise yes you would have to include the class manualy and set it up correctly.

[Dream]

im including global.php... its weird, I tried query_read( somefunction() ), and it doesnt even enter the other function.


edit:

BTW, anyone of you would know a query to remove all thread ratings made by the author of the thread?

im including global.php... its weird, I tried query_read( somefunction() ), and it doesnt even enter the other function.

can't call $db from inside a function, had to pass it as a variable to the function.