vb.org Archive - Join SQL command

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

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

- - Join SQL command (https://vborg.vbsupport.ru/showthread.php?t=230007)

MyPornLife.info

12-11-2009 11:17 PM

Join SQL command

hey.
i use these SQL command

PHP Code:


		
			
    $a = $db->query_first("SELECT `IDs` FROM " . TABLE_PREFIX . "second WHERE pID = $pID");

    $b = $a['IDs']; // it returns like 7,9,15,16

    $query = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "first WHERE ID IN ($b)");

its working perfectly.

but i want to use a single query instead of two
i tried this:

PHP Code:


		
			
    $query = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "first AS first

                    LEFT JOIN " . TABLE_PREFIX . "second AS second

                    WHERE first.ID IN (second.IDs) AND second.pID = $pID");

not working.showing error

plz help me

Lynne

12-12-2009 03:13 AM

Perhaps something like:

PHP Code:


		
			
LEFT JOIN " . TABLE_PREFIX . "second AS second ON (first.ID = second.IDs)

It's not clear to me whether there is an ID field in both tables (or whether it really is IDs in second), but you need to say what the two tables are joined ON - what the relationship is between the two tables.

MyPornLife.info

12-12-2009 06:55 AM

field "ID" is in table: first (its an INT field)
field "IDs" is in table: second (its a VARCHAR field)

so "ID" & "IDs" two different field in deferent table.

"ID" contains a single unique number
but "IDs" contain some id. i.e- 14,15,16,17,118

so i think ON is not a good idea...coz they never be equal.
thats why IN must be used.

plz read my both codes of first post very carefully again...so it will be clear to u
& plz help me

& sorry for the confusing post :)

--------------- Added [DATE]1260613675[/DATE] at [TIME]1260613675[/TIME] ---------------

hey i tried this:

PHP Code:


		
			
$query = $db->query_read("SELECT * FROM " . TABLE_PREFIX . "first AS first

    LEFT JOIN " . TABLE_PREFIX . "second AS second

    ON (first.ID IN (second.IDs)) WHERE second.pID = $pID");

its not showing error but incomplete result.
i mean it returns just only one table although it shud show more as there r more row according to condition.

Lynne

12-12-2009 05:34 PM

It could be that you want a RIGHT JOIN instead of a LEFT JOIN.

MyPornLife.info

12-12-2009 10:36 PM

not working. same result

All times are GMT. The time now is 01:10 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00939 seconds Memory Usage 1,725KB 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 (4)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (5)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: