vb.org Archive - let's see whos good at sql

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - let's see whos good at sql (https://vborg.vbsupport.ru/showthread.php?t=83525)

Dream

06-22-2005 04:37 AM

let's see whos good at sql

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 :/

Code:

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

06-22-2005 04:44 AM

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

06-22-2005 10:56 AM

[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

06-23-2005 06:00 AM

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	06-23-2005 08:29 AM

Quote:

Originally Posted by Dream

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

06-23-2005 05:17 PM

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?

Quote:

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.

All times are GMT. The time now is 08:47 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01057 seconds Memory Usage 1,720KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (6)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: