mysql - join two tables in one with one id and multiple ids in another table -


i have table content joined content_image. there can different images single content_id. want join them case, according image_size.

in other words, have 1 content, 3 different images sizes. these images in table, want show 1 content, 3 different images in 1 output.

anyway hope code more clear in explanation:

    select c.chapter_id,ch.chapter,c.content_id,            c.cont_head name,            small=(case when ci.image_size=0 contimage_small end),            big=(case when ci.image_size=2 contimage_small end),            c.update_date,            c.record_date       content c                left outer join content_chapter ch on c.chapter_id = ch.chapter_id                left outer join content_image ci on ci.content_id=c.content_id      ch.contentcat_id = 14     order c.update_date desc,c.record_date desc

and output code: 

    <cfoutput query="get_images">         <a class="highslide" target="_blank" onclick="return hs.expand(this, { slideshowgroup: 1 } )" href="/documents/content/#big#"><img src="/documents/content/#small#" title="#name#" border="0" /></a>     </cfoutput>

the problem generates 2 outputs. in first one, big value populated. in second one, small value populated. if group cfoutput content_id, generate single result, again - big value defined :) want single output both big , small values defined.

thank help!

show 1 content 3 different images in 1 output.

if understanding correctly, trying return single record both image size. achieve result must join image table multiple times: once each of image sizes.

again as dan mentioned above, if wish preserve outer join's filtering on tables must done within join's, not where clause, otherwise end doing implicit inner join instead.

 select c.chapter_id        , ch.chapter        , c.content_id        , c.update_date        , c.record_date        , c.cont_head name        , cis.contimage_small smallimage        , cib.contimage_small bigimage  content c          left outer join content_chapter ch               on c.chapter_id = ch.chapter_id , ch.contentcat_id = 14          left outer join content_image cis               on cis.content_id = c.content_id , cis.image_size = 0          left outer join content_image cib               on cib.content_id = c.content_id , cib.image_size = 2 order c.update_date desc,c.record_date desc

Comments

Post a Comment

Popular posts from this blog

c++ - CryptStringToBinary API behavior -

i seeing strange behavior cryptstringtobinary cryptography api. please see below code (config: x64 debug): #include "stdafx.h" #include <windows.h> #include <strsafe.h> #include <iostream> #include <exception> void main() { dword dwskip; dword dwflags; dword dwdatalen; //lpcwstr pszinput = l"jaaaaaecaaadzgaaakqaagdnnl1l56bwgfjdgr3rpxtqqqn6daw3usv2emkjym4t"; //this works fine lpcwstr pszinput = l"mytest"; //doesnt work, api returns false,error code 0x0000000d // determine size of byte array , allocate memory. if(! cryptstringtobinary( pszinput, _tcslen( pszinput ) + 1, crypt_string_base64, null, &dwdatalen, &dwskip, &dwflags ) ) { dword dw = getlasterror(); //0x0000000d: data invalid throw std::exception( "error computing byte length." ); } byte *pbytebyte = null; try { ...
Read more

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more

iphone - Three second countdown in cocos2d -

i trying have 3 second countdown display when level beat in game. did: -(void) gamesegmentbeat { [self pauseschedulerandactions]; // overlay game opaque background ccsprite *opaquebg = [ccsprite spritewithfile:@"background1.png"]; opaquebg.position = screencenter; [self addchild:opaquebg z:10000]; // these 3 labels 3 seconds cclabelttf *three = [cclabelttf labelwithstring:@"3" fontname:@"helvetica" fontsize:100]; three.position = ccp(screencenter.x, screencenter.y); cclabelttf *two = [cclabelttf labelwithstring:@"2" fontname:@"helvetica" fontsize:100]; two.position = ccp(screencenter.x, screencenter.y); cclabelttf *one = [cclabelttf labelwithstring:@"1" fontname:@"helvetica" fontsize:100]; one.position = ccp(screencenter.x, screencenter.y); // secondspast specified in update method secondspast = 0; if (secondspast == 1) { [self addchild:thr...
Read more