SELECT within SELECT

vaaaska

I'm really trying to wrap my brain around how this works...no such luck...I'll just break it out the way I started and see if somebody can help me see this one through...

What I'm doing here is querying two tables to retrieve three results - this works just fine. Then, as I loop through the results I make another query at each pass to retrieve img and content which are stored in the same table as the previous query.

Is there a way to combine all of this into one query? I've been messing with a SELECT within a SELECT and also UNION without any luck...

Thanks if somebody can help me see this one...I'm obviously not there yet on my own.

$rs = $db->fetchArray("SELECT obj_ref_id,title,obj_capt,url 
	FROM mww_objects,mww_objects_prefs 
	WHERE object = 'gallery' 
	AND obj_ref_id = obj_id 
	AND type = '1' 
	AND status = '1' 
	ORDER BY obj_udate DESC LIMIT 3");


// this is called from a separate function as
// getPhoto($rs['obj_ref_id'])

$rs = $db->fetchRecord("SELECT img,content 
	FROM mww_objects 
	WHERE object = 'gallery' 
	AND type = '2'  

	AND obj_ref_id = '$id' 
	ORDER BY udate DESC");

jheitz

Can you post the table descriptions along with their relationships, some sample data and the result you would expect to get with your query? This will help immensely

Roger_Ramjet

Just ad the additional fields to the original query.

 $rs = $db->fetchArray("SELECT obj_ref_id,title,obj_capt,url, img,content
    FROM mww_objects,mww_objects_prefs
    WHERE object = 'gallery'
    AND obj_ref_id = obj_id
    AND type = '1'
    AND status = '1'
    ORDER BY obj_udate DESC LIMIT 3");

Now, my guess is that you will then get multiple rows for each obj_ref_id so you are going to have to deal with that in your loop - not a problem just compare each with the previous row.

vaaaska

Nope...wouldn't work. Type.

Type 1 is a full gallery...type 2 is just an image. Additionally, type 2 recoreds would never have a status of 1.

Yes, I know it could possibly use a different database layout...but...if I explained why it is the way that it is you would understand (but no, I'm going to do that here).

Lars_Berg

SELECT mo1.obj_ref_id,
       ???.title,
       ???.obj_capt,
       ???.url,
       mo2.img,
       mo2.contents
  FROM mww_objects mo1 join mww_objects_prefs mop
    on mo1.obj_ref_id = mop.obj_id
  join mww_objects mo2 
    on mo1.obj_ref_id = mo2.obj_ref_id
  WHERE mo1.object = 'gallery'
    AND mo1.type = 1
    AND ???.status = 1
    and mo2.object = 'gallery'
    and mo2.type = 2
  ORDER BY ???.obj_udate DESC 
  limit 3

Since you don't tell which columns that belongs to which tables you need to replace the ??? with the proper correlation name, i.e. mo1 or mop.