combined sql queries in PHP

[Julie]

Hi, I was wondering if anyone could help me, im having abit of a prob
combining these two queries to get the result i want. The queries both
work perfectly, but i need to somehow put the second query into the
first one so that after the first one runs it will run the second one
and then return out these records. I know this is a sql thing, but i
thought since i was doing it in PHP there may be a nice way of doing it
instead of using sql, which isnt as flexible :) I know these are looong
queries, but id be grateful if someone took the time to have a peek!
the fact i have quite a few joins doesnt seem to be helping me when i
try and work it out - Doh!!

Thanks in advance :D
Julie

First query (main query)

mysql_select_db($blah, blah....);

$query_rs_careers_simple = "SELECT resource.res_id, resource.res_title,
resource.res_details, resource.res_date_updated,
resource_isbn.res_isbn_num,
resource_date_of_publish.res_date_of_publish, medium.medium_name,
availability.avail_type, resource_agcas.res_agcas_id,
agcas.agcas_class, agcas.agcas_name, publisher.pub_name,
publisher_details.pub_address, publisher_details.pub_tlf FROM resource
LEFT JOIN resource_agcas ON resource.res_id = resource_agcas.res_id
LEFT JOIN resource_isbn ON resource.res_id = resource_isbn.res_id LEFT
JOIN resource_date_of_publish ON resource.res_id =
resource_date_of_publish.res_id LEFT JOIN medium ON
resource.res_medium_id = medium.medium_id LEFT JOIN availability ON
resource.res_avail_id = availability.avail_id LEFT JOIN agcas ON
resource_agcas.res_agcas_id = agcas.agcas_id LEFT JOIN publisher ON
resource.res_pub_id = publisher.pub_id LEFT JOIN publisher_details ON
publisher_details.pub_id = publisher.pub_id ";

$rs_careers_simple = mysql_query($blah blah or die(mysql_error());
$row_rs_careers_simple = mysql_fetch_assoc($rs_careers_simple);
$totalRows_rs_careers_simple = mysql_num_rows($rs_careers_simple);

second query (sub query... i think!)

mysql_select_db($blah, blah);
$query_rs_aoi = "SELECT res_id, aoi_name FROM resource_a_o_i_lkt INNER
JOIN area_of_interest ON
resource_a_o_i_lkt.aoi_id=area_of_interest.aoi_id" ;

$rs_aoi = mysql_query($blah blah) or die(mysql_error());
$row_rs_aoi = mysql_fetch_assoc($rs_aoi);
$totalRows_rs_aoi = mysql_num_rows($rs_aoi);

[Jerry Stuckle]

Julie wrote:

Hi, I was wondering if anyone could help me, im having abit of a prob
combining these two queries to get the result i want. The queries both
work perfectly, but i need to somehow put the second query into the
first one so that after the first one runs it will run the second one
and then return out these records. I know this is a sql thing, but i
thought since i was doing it in PHP there may be a nice way of doing it
instead of using sql, which isnt as flexible :) I know these are looong
queries, but id be grateful if someone took the time to have a peek!
the fact i have quite a few joins doesnt seem to be helping me when i
try and work it out - Doh!!

Thanks in advance :D
Julie

First query (main query)

mysql_select_db($blah, blah....);

$query_rs_careers_simple = "SELECT resource.res_id, resource.res_title,
resource.res_details, resource.res_date_updated,
resource_isbn.res_isbn_num,
resource_date_of_publish.res_date_of_publish, medium.medium_name,
availability.avail_type, resource_agcas.res_agcas_id,
agcas.agcas_class, agcas.agcas_name, publisher.pub_name,
publisher_details.pub_address, publisher_details.pub_tlf FROM resource
LEFT JOIN resource_agcas ON resource.res_id = resource_agcas.res_id
LEFT JOIN resource_isbn ON resource.res_id = resource_isbn.res_id LEFT
JOIN resource_date_of_publish ON resource.res_id =
resource_date_of_publish.res_id LEFT JOIN medium ON
resource.res_medium_id = medium.medium_id LEFT JOIN availability ON
resource.res_avail_id = availability.avail_id LEFT JOIN agcas ON
resource_agcas.res_agcas_id = agcas.agcas_id LEFT JOIN publisher ON
resource.res_pub_id = publisher.pub_id LEFT JOIN publisher_details ON
publisher_details.pub_id = publisher.pub_id ";

$rs_careers_simple = mysql_query($blah blah or die(mysql_error());
$row_rs_careers_simple = mysql_fetch_assoc($rs_careers_simple);
$totalRows_rs_careers_simple = mysql_num_rows($rs_careers_simple);

second query (sub query... i think!)

mysql_select_db($blah, blah);
$query_rs_aoi = "SELECT res_id, aoi_name FROM resource_a_o_i_lkt INNER
JOIN area_of_interest ON
resource_a_o_i_lkt.aoi_id=area_of_interest.aoi_id" ;

$rs_aoi = mysql_query($blah blah) or die(mysql_error());
$row_rs_aoi = mysql_fetch_assoc($rs_aoi);
$totalRows_rs_aoi = mysql_num_rows($rs_aoi);

I'd suggest you ask in comp.databases.mysql.

I think there's a SQL only answer to your problem - but I'm not sure.
I'd need your MysQL version, table layout and what you're actually
trying to achieve.

Just posting SQL statements doesn't help much.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================