CODEWITHSUNDEEP
mysqlsqlinner-join
Rafael Herscovici
Rafael Herscovici

Reputation: 17134

getting more then one column on an inner join

i have the following query:

SELECT c.company_id, c.company_title, c.featured, a.address, a.postal_code, pc.city, pc.region,cat.category_title, p.phone
FROM dloc.companies c 
    INNER JOIN dloc.address a ON ( c.address_id = a.address_id  )  
  INNER JOIN dloc.postal_code pc ON ( a.postal_code = pc.postal_code )
  INNER JOIN dloc.company_categories cc ON ( c.company_id = cc.company_id )
  INNER JOIN dloc.categories cat ON ( cc.category_id = cat.category_id )
  INNER JOIN dloc.phones p ON ( c.company_id = p.company_id )

WHERE c.company_title like '%gge%'

everything works just fine. the only thing is.. well. phones contains more then one phone number for some companies... and i guess i get only the first one... or random, im not sure. how can i rewrite this query so it will return all the phone numbers for each company?

Upvotes: 2

Views: 403

Answers (4)

Clockwork-Muse
Clockwork-Muse

Reputation: 13106

Given that query, you should get one row for each phone number row (per company). SQL works as relationship/set math/theory - having a '-many' relationship means multiple rows returned (So, your query should already be perfroming the desired behaviour).

Often, the problem people writing queries experience isn't getting multiple rows - it's restricting it to the desired 'single' row.

EDIT:

Result Ordering -
SQL, by convention, returns things unordered, unless some sort of explicit ordering is given (through the use of an ORDER BY clause). You are seeing 'random' phone numbers being returned 'first' because the RDBMS still has to read/return results sequentially; this order is determined at runtime (...usually), when the system picks what indicies to use while accessing data. The full interaction is rather complex (and is probably vendor specific), so just keep this in mind:
UNLESS YOU SPECIFY ORDERING OF YOUR RESULTS, THE RESULTS ARE RETURNED IN A RANDOM ORDER
period.

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58645

I would suggest you using GROUP_CONCAT on the p.phone field.

A few considerations, though:

  • It looks like a company can have more than one phone. But also, none! So you should change your INNER JOIN to LEFT JOIN to take that into consideration.
  • It also looks like a company can have more than one address. If that's true, not only previous consideration should be taken into, but also you should rework your query to accommodate this.

Upvotes: 1

Johan
Johan

Reputation: 76753

group_concat will collect all phonenumbers in one column.
Add a group by on company_id, if you are sure no other values are listed more than once per company.
If not, put those 'duplicate' (for want of a better word) values in a group_concat as well.

SELECT 
  c.company_id
  , c.company_title
  , c.featured
  , a.address
  , a.postal_code
  , pc.city
  , pc.region
  , cat.category_title
  , GROUP_CONCAT(p.phone) as phonenumbers
FROM dloc.companies c 
INNER JOIN dloc.address a ON ( c.address_id = a.address_id  )  
INNER JOIN dloc.postal_code pc ON ( a.postal_code = pc.postal_code )
INNER JOIN dloc.company_categories cc ON ( c.company_id = cc.company_id )
INNER JOIN dloc.categories cat ON ( cc.category_id = cat.category_id )
INNER JOIN dloc.phones p ON ( c.company_id = p.company_id )
WHERE c.company_title like '%gge%'
GROUP BY c.companyid

See: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 0

mylopeda
mylopeda

Reputation: 155

You should get all phone numbers for the given company id because the join begins by doing a cartesian product between the two tables and then removing all "rows" that doesn't match the criteria in the join.

Upvotes: 0

Related Questions