URGENT Query needed!!!

DRTechie

HELP! I've been trying to write a query but being that newbie that I am I can't get it to work. Here's what I am trying to do.

I need to select the "sitename" and "address" for any customer who has two or more circuits where the type equals "primary". Almost forgot, I also need to return the ckt_id for the circuits associated with the "sitename".

TABLE site (site_id,sitename, address)
TABLE circuits (record_id, ckt_id, type)
TABLE site_circuit (relation_id, ckt_id, site_id)

Thanks in advance for any help you can provide.

rontgoff

Can you explain this more clearly

DRTechie

ok here goes...

I have a database which holds customer information. Each customer can have one or more circuit.

I need to return the sitename, address, and ckt_id for any customer who has two or more circuits and whose circuit type is equal to primary.

Hope this makes sense now.

ultraslacker

Okay, this is going to matter - are you using mysql?

DRTechie

I am using MySQL and returning the results to a PHP page.

ultraslacker

SELECT site_id, sitename, address, COUNT(relation_id) AS total_circuits
FROM site s, circuits c, site_circuit sc
WHERE type = 'primary' AND
sc.ckt_id = c.ckt_id AND sc.site_id = s.site_id
GROUP BY site_id, sitename, address
HAVING total_circuits >= 2

site_id included for distinctness of rows.

DRTechie

Thanks that makes sense. One thing though, what if I also want to display the ckt_ids for those sites that do have two or more circuits?

rontgoff

TABLE site (site_id,sitename, address)
TABLE circuits (record_id, ckt_id, type)
TABLE site_circuit (relation_id, ckt_id, site_id)

Try, its a start. I assuming that you keep circut info in circuts

$sql = mysql_query("SELECT * FROM circuts WHERE type = 'primary' ");
$sql_result= mysql_query($sql);
while($row= mysql_fetch_array($sql_result)){
$circut_id = $row["ckt_id"];

$sql2 = mysql_query("SELECT * FROM site_circut WHERE ckt_id ='$circut_id' ");
$sql_result2= mysql_query($sql2);
while($row2= mysql_fetch_array($sql_result2)){
$site_id = $row2["site_id"];

$sql3 = mysql_query("SELECT * FROM site WHERE site_id ='$site_id' ");
$sql_result3= mysql_query($sql3);
while($row3= mysql_fetch_array($sql_result3)){

print $row3["sitename"];
print $row3["address"];
}
}
}

This should do it.

ultraslacker

Okay, my query will not work then, as ckt_id will throw off count. A subquery could handle this, but you are using mysql.

Also, my query uses the wrong column - should be site_id rather than relation_id.

// sites that have at least 2 primary type circuits
$sql = "SELECT sc.site_id, COUNT(sc.site_id) AS total_circuits
 FROM site s, circuits c, site_circuit sc
 WHERE type = 'primary' AND
 sc.ckt_id = c.ckt_id AND sc.site_id = s.site_id 
 GROUP BY sc.site_id
 HAVING total_circuits >= 2";

$site_result = mysql_query($sql);

while ($site_array = mysql_fetch_array($site_result))
{
//select details for site_id's returned in first query 
	$site_id = $site_array['site_id'];
	$sql = "SELECT sc.ckt_id, sc.site_id, sitename, address 
	FROM site_circuit sc, circuits c, site s 
	WHERE type = 'primary' AND sc.site_id = '$site_id' AND sc.ckt_id = c.ckt_id AND sc.site_id = s.site_id";
	$detail_result = mysql_query($sql);
	while ($detail_array = mysql_fetch_array($detail_result))
	{	
		// get details of each site
		// repeated rows for each row in relation
		echo $site_detail['sitename'];
	}
}

DRTechie

ultra that didn't work, it can't execute the query.

ultraslacker

error in first query - missing comma.

DRTechie

failed : You have an error in your SQL syntax near '$sql = "SELECT sc.site_id, COUNT(sc.site_id) AS total_circuits FROM site s, cir' at line 1

ultraslacker

The sql is fine.

DRTechie

ok the query works fine BUT when I try to open the script through my browser I just get a blank page. Any idea why this is happening?