Page 1 of 1
SELECT (DISTINCT column_name)
Posted: Wed May 09, 2012 8:13 am
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?
Re: SELECT (DISTINCT column_name)
Posted: Wed May 09, 2012 10:18 am
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).
Re: SELECT (DISTINCT column_name)
Posted: Wed May 09, 2012 3:46 pm
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.
Re: SELECT (DISTINCT column_name)
Posted: Thu May 10, 2012 2:32 am
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.
Re: SELECT (DISTINCT column_name)
Posted: Thu May 10, 2012 10:24 am
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")
Re: SELECT (DISTINCT column_name)
Posted: Thu May 10, 2012 10:31 am
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).
Re: SELECT (DISTINCT column_name)
Posted: Thu May 10, 2012 3:09 pm
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`
-- ...
Re: SELECT (DISTINCT column_name)
Posted: Fri May 11, 2012 4:02 am
by shiznatix
Thanks guys. Got it kicking. Learning much about SQL that I didn't know before, good stuff.