MYSQL Selecting multiple values from the same column as a row

Question

I have two tables

table COMMUNITY
  id, 
  name, 
  etc.

table PROPERTY
  id,
  community_id,
  type,
  price,
  rate,
  etc.

There are four possible property types and a community can have info for one or more of the different types.

I am trying to figure out how to combine the information for each community into one row. For instance I might want to get a row which includes

community.id, community.name, condo.price, condo.rate, town_home.price, town_home.rate

Of course there aren't tables for condo or town_home, the property type is represented in the property table by a column and a community can have multiple property rows.

My first thought was to use multiple joins on the table with aliases, but I can't seem to get anything that works properly.

Any suggestions?

ysrb · Accepted Answer · 2011-10-16 01:56:35Z

3

You can use left join for this.

SELECT c.id, c.name, condo.price, condo.rate, town_home.price, town_home.rate
FROM Community c
LEFT JOIN Property condo ON condo.community_id = c.id AND condo.type = 'condo'
LEFT JOIN Property town_home ON town_home.community_id = c.id AND town_home.type = 'town_home'

edited Oct 16, 2011 at 1:56

answered Oct 15, 2011 at 23:49

ysrb

6,7202 gold badges32 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

christian Over a year ago

Thanks! I'm assuming the last join should have town_home.type or maybe it doesn't matter as it is the same table.

Botz3000 · Accepted Answer · 2012-05-29 06:44:14Z

1

I am the kind of person who doesn't like much to use joins, might be because I don't see much reason as for use them or as I wouldn't need it. By any means, this is the way I would put it:

As you have a link between the two tables, you are able to use them to bring you the data you need, as long as you have this link you can bring whatever field you want.

So you said you wanted the fields: community.id, community.name, condo.price, condo.rate, town_home.price, town_home.rate

    SELECT
        community.id,
        community.name,
        condo.price,
        condo.rate
    FROM
        community, property condo
    WHERE
        community.id = condo.community_id;

This should solve it.

edited May 29, 2012 at 6:44

Botz3000

39.8k8 gold badges107 silver badges128 bronze badges

answered May 28, 2012 at 17:11

Rafael

1,5361 gold badge15 silver badges26 bronze badges

Comments

csalvato · Accepted Answer · 2011-10-15 23:47:40Z

0

Try this:

SELECT * FROM community LEFT OUTER JOIN property ON community.id = property.community_id;

This should join the two tables on the ID field, so you should have one complete row with all of the information. if anything is null, it should be present in the row, as well. If multiple properties are listed with the same community ID, they should all come up in the result.

PS - I use PostgreSQL and not MYSQL so the syntax might be a bit different. Let me know how it works!

answered Oct 15, 2011 at 23:47

csalvato

5785 silver badges13 bronze badges

1 Comment

christian Over a year ago

This includes all info in the result, but not in a row. Thanks.

Collectives™ on Stack Overflow

MYSQL Selecting multiple values from the same column as a row

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related