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:
[sql]SELECT * from template as t1
WHERE t1.title NOT IN (SELECT t2.title FROM template AS t2 WHERE t2.styleid = -1);[/sql]
This is not the complete statement, but should get you started.

[Andreas]

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

[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]

Originally Posted by 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?

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?

Originally Posted by Dream

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.