Part of the EllisLab Network
CodeIgniter  >   Forum Home  >  CodeIgniter Development Forums  >  General CodeIgniter Discussion (no code)  >  Thread
   
 
SQL Query Help
 
Michael Wales
Posted: 03 December 2007 06:57 PM   [ Ignore ]  
Lab Technician
Avatar
RankRankRankRank
Total Posts:  2280
Joined  07-30-2007

I would like to accomplish this within one query if at all possible. I know I could query the products table for a list of unique category_slugs and then perform queries on each of those, but… bleh.

Tables

products
————
category_slug VARCHAR

categories
—————
slug VARCHAR
title VARCHAR

Goal:
I would like to select categories.slug, categories.title from categories WHERE products.category_slug is UNIQUE.

I’m pretty sure that statement can lead me to a syntactically correct SQL statement, I have all the keywords in there.

I was giving this a go, but my subquery returns more than one row (as it should):

SELECT categories.slug, categories.title FROM categories JOIN products WHERE products.category_slug = ( SELECT DISTINCT category_slug FROM products ) 


Ideas?

 Signature 

Follow me on twitter here.
MichaelWales.com | MichaelWales.info
Profile
 
 
gtech
Posted: 03 December 2007 07:05 PM   [ Ignore ]   [ # 1 ]  
Research Assistant
Avatar
RankRankRank
Total Posts:  865
Joined  09-25-2007

have you tried the DISTINCT keyword? just scanning google it looks like it pulls unique entries from the database. (I may be wrong)

search for distinct

however I am not sure how optimal this is.
Profile
 
 
Michael Wales
Posted: 03 December 2007 07:08 PM   [ Ignore ]   [ # 2 ]  
Lab Technician
Avatar
RankRankRankRank
Total Posts:  2280
Joined  07-30-2007

I think I found a solution… I had the misconception you could only SELECT DISTINCT a single column - turns out it worked perfectly in this situation:

SELECT DISTINCT products.category_slug, categories.title 
FROM products 
JOIN categories ON products.category_slug = categories.slug 
 Signature 

Follow me on twitter here.
MichaelWales.com | MichaelWales.info
Profile
 
 
Michael Wales
Posted: 03 December 2007 07:12 PM   [ Ignore ]   [ # 3 ]  
Lab Technician
Avatar
RankRankRankRank
Total Posts:  2280
Joined  07-30-2007

Thanks for the reply gtech - looks like I was replying at the same time…

 Signature 

Follow me on twitter here.
MichaelWales.com | MichaelWales.info
Profile
 
 
gtech
Posted: 03 December 2007 07:16 PM   [ Ignore ]   [ # 4 ]  
Research Assistant
Avatar
RankRankRank
Total Posts:  865
Joined  09-25-2007

no probs, glad you got it working.
Profile
 
 
   
 
 
‹‹ relative dates?      Oracle INSERTs failing, but no errors ››
Powered By ExpressionEngine
ExpressionEngine Discussion Forum - Version 3.1.5 (20110621)
Script Executed in 0.3954 seconds
Atom Feed   RSS 2.0