CODEWITHSUNDEEP
mysqlselectjoin
christian
christian

Reputation: 2489

MYSQL Selecting multiple values from the same column as a row

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?

Upvotes: 0

Views: 2172

Answers (3)

Rafael
Rafael

Reputation: 1535

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.

Upvotes: 1

ysrb
ysrb

Reputation: 6740

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'

Upvotes: 3

csalvato
csalvato

Reputation: 568

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!

Upvotes: 0

Related Questions