difficult query... at least for me

[farberama]

I need to design a query that will use the following tables:

CommunityElevation:
communityModelID
communityID
elevationID
baseprice

Community:
communityID
name

Elevation:
elevationID
elevationName
modelID

Model:
modelID
modelName

This database, in part, describes a home builder's communities, models, and model elevations. These relate to each other in the following way:

-each community has multiple models
-each model has multiple elevations
-different communities can contain the same models/elevations
-each elevation has it's own baseprice depending on in which community it is located. That is the purpose of the communityElevation table.

My query needs to produce 1 row for each model/community (NOT elevation/community) pairing with the lowest possible baseprice for that pairing (i.e. lowest baseprice in communityElevation for a specific model and community).

Does this make sense? Is it possible?

Thanks for your help in advance.

 

[hos2]

first of all I would recommend to change your fieldnames to unique names otherwise it becomes a bit fuzzy

CommunityElevation:
   comeModelID
   comeUnityID
   comeElevationID
   comeBaseprice

something like this

a query for what you are asking would be something like

$query="SELECT string(comeModelID,comeElevationId) as modelelev,min(comeBasePrice),max(modelName),max(elevationName) from CommunityElevation,Model,Community where comeModelID=ModelId and comeElavationId=ElevationId group by modelelev


ps your table structure looks strange since you have a modelId in your main table but it's also in the elevation table ?? which one to use ??

 

[farberama]

Thanks for your help, hos2, but I'm not sure I understand your query. I can't change the field names because most of the application is already built and I would have to go back and change everything. Could you please rewrite the query using the field names I have? To clarify which fieldname you are using, you could use the syntax tableName.fieldName.

Maybe it would help if I clarified some things.

First, the field communityModelID in the communityElevation table should be communityElevationID which is the table key. Sorry for the mistake.

Second, any time you see 'tableNameID' in the table 'tableName' it is the key for that table. Any time you see 'anotherTableID' in table 'tableName' it refers the the row in table 'anotherTable' with that ID. For example, The modelID in the elevation table refers back to a specific modelID (key) in the model table.

Third, even though a model can have more than 1 elevation, each elevation is associated with only 1 model.

Does this clarify things?