Returning a single row from query joining multi-row tables

I have a database design that holds data about houses. There are 2 (relevant) tables - one holds property code, name, description etc, and the second holds information about attributes of the property.

Currently I have got columns in the property table (MaxDoubles, MaxSingles etc) that hold denormalised data that I need to now hold (for various reasons) in the attributes table. In effect I am swapping a series of columns in the Properties table for a series of rows in the Attributes table. So my existing query

SELECT MaxDoubles, MaxSingles FROM Properties

that returns one row per property needs re-writing to also produce one row per property when joined to the Attributes. If I try

SELECT A.MaxDoubles, A.MaxSingles FROM Properties P, Attributes A

then I obviously get multiple rows returned per proper开发者_开发知识库ty.

Is there a clever way of joining these tables so the query result returns a single row?

Thanks

Assuming a schema like @Konerak's example, you'll need to "pivot" the attributes records if you want one row containing a property and all of its attributes.

Fortunately, there is no shortage of info on SO about how to do that. :)

What you now have is called an EAV data structure and what you want to do is called "to pivot". Besides using a subselect, there are two possibilities.

Using a GROUP BY:

SELECT P.Property_ID,
       MAX(IF(A.Type = 'maxsingles',A.Value,0)) AS MaxSingles,
       MAX(IF(A.Type = 'maxdoubles',A.Value,0)) AS MaxDoubles
FROM Properties P
JOIN Attributes A USING (Property_ID)
GROUP BY Property_ID

Using one JOIN for each attribute:

SELECT P.Property_ID, A1.Value AS MaxSingles, A2.Value AS MaxDoubles
FROM Properties P
JOIN Attributes A1 ON (A1.Property_ID = P.Property_ID AND A1.Type = 'maxsingles')
JOIN Attributes A2 ON (A2.Property_ID = P.Property_ID AND A2.Type = 'maxdoubles')

We'll need more information from you to handle your question. For example:

What do your tables look like (SHOW CREATE TABLE Properties) What queries do you want to execute? How do you join your tables?

This schema is normalized enough to allow typical all queries:

Table Properties:

• PropertyID
• PropertyName
• Propertydescription

Table Attributes:

• AttributeID
• PropertyID
• AttributeName
• AttributeValue

So if you have Property 1, a white house with 3 bedrooms, you could have

PropertyID: 1
PropertyName: Charming white landhouse with 3 spacious bedrooms
Propertydescription: This charming landhouse will ...

Attributes
AttributeID: 1
PropertyID: 1
AttributeName: NrBedrooms
AttributeValue: 3

AttributeID: 2
PropertyID: 1
AttributeName: NrBathrooms
AttributeValue: 2

AttributeID: 3
PropertyID: 1
AttributeName: Kitchen
AttributeValue: Fully Equiped

Now if you want to know how many bedrooms your house has, you can ask

SELECT A.AttributeValue from Attributes A
INNER JOIN Properties P
ON P.PropertyID = A.PropertyID
WHERE P.PropertyID = 1
and A.AttributeName = 'NrBedrooms'