SQL and Nested SELECT

geogal

Hi All,

Can anyone tell me what the proper syntax is for a nested SELECT statement? I'm trying to pull info from two tables, and the following would not work:

"SELECT builders.bu_communities, communities.community_name, communities.cID FROM builders, communities WHERE communities.cID IN (SELECT bu_communities FROM builders WHERE buID = $buID)";

Basically I'm trying to do this: Match a builder to the communities he builds in. The community names are listed in one table with an autonumber field. The builders are listed in another table with an autonumber field. Okay. Now some builders have homes in various communities, so I can't figure out how to set up this field in the builders table. Right now I have a column named "bu_communities" and it has 1,12,4 etc. which is suppose to contain the autonumber ID field of the communities table, from which I can get the community name. When I use an INNER JOIN, I only getting the first match from the field (say 1, but not 12 and 14). So I tried using IN and another SELECT, but it's not working.

Any help will be appreciated!

Geogal🙁

geogal

Oops. I guess MySQL doesn't support nested SELECT statements. I'll have to figure this one out...

Lars_Berg

Right now I have a column named "bu_communities" and it has 1,12,4 etc

Don't do it like that. Store each item as a separate record and use a join.

geogal

Thanks Lars! Hmmm, I had thought about that, but new communities could be added at any time to the communities table. If I make separate fields for each community in the "builders" table, then I'd have to add these new communities to that table as well.

Am I overlooking something so simple? If so, please let me know 🙂 I'm pretty new to PHP.

I have made some progress by doing two SELECT statements, one based on the results of another. Those results were then placed into an array like below:

FIRST SELECT
$sqlquery = "SELECT * FROM builders WHERE buID = $buID";
$result = mysql_query($sqlquery);

NEW VARIABLE FROM THIS RECORDSET
$communities= mysql_result($result,$i,"bu_communities");

SECOND SELECT
$subSql = "SELECT community_name FROM communities WHERE cID IN ($communities)";
$result2 = mysql_query($subSql);

PRINT RESULTS

<? while ($row = mysql_fetch_array($result2)) {
printf ("| %s", $row[0], $row[1]);

}
?>

Which looks like this:

| Lake View| Nillyville| Running Creek

This is probably a strange way to do it, but it works. I'm not saying I completely understand the array part (I found it online), but at least I'm getting results. Any other ideas on how to set up my "builders" table to better suit a JOIN query will be welcomed.

Thanks again Lars.

Geogal

Lars_Berg

This is a typical many to many relation.

A builder can have be in many communities and there can be many builders in one community.

You need three tables to modell this

builders

buid int
other columns

communities

cuid int
other columns

buildingCommunity

buid int
cuid int

So if builder no 47 is present in community 11 that will show as one row in the buildingCommunity table.

buid ! cuid

47 ! 11

Thus if a builder works in three communities there will be three records, et.c.

So if you need to get all builders in a community named 'Uppsala'

select * from builders
 where buid in (select buid from buildingCommunity bc 
inner join communites c on 
bc.cuid = c.cuid 
where c.name = 'Uppsala')

(I haven't checked your column names in detail)

There will be no problems adding new builders or communities.

ultraslacker

If I understand you correctly, you are trying to make a n to n relationship between builders and communities with an array of community ids in builders.

You may save yourself a few headaches later on by taking another look at the database design, if possible. I would use a separate table - this is standard practice - with two columns, builder_id, community_id. This will allow a better relation of data and can be accomplished without subselects.

geogal

Oh, adding another table! Thanks Lars. I'll get to work on it. It's sure to clean up my code a ton.

I appreciate your input.

Geogal

ultraslacker

Doh, Lars beat me to it!

geogal

Hi Ultraslacker! I just input my reply when you added yours. Thanks a bunch for the help. I knew it had to be in my db design but didn't even think of adding a new table. Thanks guys!

Geogal

svnfightsvn

your SQL should not use an IN operator - just say =

here you go:
"SELECT builders.bu_communities, communities.community_name, communities.cID FROM builders, communities WHERE communities.cID= (SELECT bu_communities FROM builders WHERE buID = $buID)";