SELECT (DISTINCT column_name)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

SELECT (DISTINCT column_name)

Post by shiznatix »

As the subject suggests, I want to return a column value only once. I have this query:

Code: Select all

SELECT `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`website`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('tandläkare') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
 INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
 INNER JOIN `nr_company_keywords` AS `companyKeywords` ON companyKeywords.companyid = companies.companyid
 INNER JOIN `nr_keywords` AS `keywords` ON keywords.keywordid = companyKeywords.keywordid
 INNER JOIN `nr_company_addresses` AS `companyAddresses` ON companyAddresses.companyid = companies.companyid
 INNER JOIN `nr_cities` AS `cities` ON cities.cityid = companyAddresses.cityid WHERE (MATCH(keywords.word) AGAINST ('tandläkare linköping')) AND (companies.active = "1")
Which is fine but it returns the same companyid multiple times for different nr_company_addresses results. I want to only return DISTINCT companies.companyid values but I get an SQL error when I use the line:

Code: Select all

SELECT (DISTINCT companies.companyid)
error: check the manual about 'DISTINCT `companies`.`companyid`), `companies`.`childCategoryid`, `companies`.`r

How can I make it only return distinct companyid's?
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: SELECT (DISTINCT column_name)

Post by x_mutatis_mutandis_x »

You cannot return distinct companyid's while returning all the addresses as well. Best solution is to remove the join with nr_company_addresses, nr_cities (and any other table/s having one-companyid to many relationship) and use a separate query for it. Many-companyid's to one/many relationships should be fine with your original query (as your companyid's will be distinct anyways).
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: SELECT (DISTINCT column_name)

Post by tr0gd0rr »

To elaborate on how to do the one-to-many query, you would loop through the results of the first query and collect company ids then put them into a new query:

Code: Select all

SELECT * FROM nr_company_addresses WHERE companyid IN ($ids)
Where `$ids` is your string of comma-delimited company ids. Then you can loop through the results of your first query again and add an array key `addresses` that contains an array of addresses from the second query. This is a common pattern handled by ORMs.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: SELECT (DISTINCT column_name)

Post by shiznatix »

Very sad. Maybe there is another way around this though. Each company can have a maximum of 2 records in the nr_company_addresses table. The most important record in company_addresses has primary = "1" set. Is there a way I could make my single query where it would check if a match is found with primary = "1" and if no record found, check primary = "0"?

The reason I want to do this all in 1 query is because I am using Zend_Paginator and it takes a single query to do its stuff on. I can't really use Zend_Paginator with the multiple queries situation.
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: SELECT (DISTINCT column_name)

Post by x_mutatis_mutandis_x »

If you want to get the first (or last, basically one) address, then yes you can do it in one query:

Code: Select all

SELECT  `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`website`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('tandläkare') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
  INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
  INNER JOIN `nr_company_keywords` AS `companyKeywords` ON companyKeywords.companyid = companies.companyid
  INNER JOIN `nr_keywords` AS `keywords` ON keywords.keywordid = companyKeywords.keywordid
  INNER JOIN `nr_company_addresses` AS `companyAddresses` ON companyAddresses.companyid = companies.companyid
  INNER JOIN (select min(id) /*or max(id)*/ from `nr_company_addresses`) as `temp_companyAddresses` on companyAddresses.id = temp_companyAddresses.id
  INNER JOIN `nr_cities` AS `cities` ON cities.cityid = companyAddresses.cityid WHERE (MATCH(keywords.word) AGAINST ('tandläkare linköping')) AND (companies.active = "1")
Last edited by x_mutatis_mutandis_x on Thu May 10, 2012 10:56 am, edited 1 time in total.
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: SELECT (DISTINCT column_name)

Post by x_mutatis_mutandis_x »

tr0gd0rr wrote:This is a common pattern handled by ORMs.
It's (lazy fetching, or fetch by select) one of the options. Some ORMS give you an option to fetch by join (e.g. Hibernate).
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SELECT (DISTINCT column_name)

Post by Weirdan »

Is there a way I could make my single query where it would check if a match is found with primary = "1" and if no record found, check primary = "0"?

Code: Select all

select company.name, coalesce(primary_address.address, secondary_address.address)  as address
from company 
left join address primary_address on primary_address.company_id=company.id and primary_address.`primary`
left join address secondary_address on secondary_address.company_id=company.id and not secondary_address.`primary`
-- ...
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: SELECT (DISTINCT column_name)

Post by shiznatix »

Thanks guys. Got it kicking. Learning much about SQL that I didn't know before, good stuff.
Post Reply