Part of the EllisLab Network
CodeIgniter  >   Forum Home  >  CodeIgniter Development Forums  >  CodeIgniter Discussion  >  Thread
   
 
SQL Query Help
 
Michael Wales
Posted: 03 December 2007 05:57 PM   [ Ignore ]  
Lab Technician
Avatar
RankRankRankRank
Total Posts:  2264
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 

Become a fan of the CodeIgniter Cookbook (estimated: Fall 2010).

Follow me on twitter here.
MichaelWales.com | MichaelWales.info

Profile
 
 
gtech
Posted: 03 December 2007 06:05 PM   [ Ignore ]   [ # 1 ]  
Research Assistant
Avatar
RankRankRank
Total Posts:  869
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 06:08 PM   [ Ignore ]   [ # 2 ]  
Lab Technician
Avatar
RankRankRankRank
Total Posts:  2264
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 

Become a fan of the CodeIgniter Cookbook (estimated: Fall 2010).

Follow me on twitter here.
MichaelWales.com | MichaelWales.info

Profile
 
 
Michael Wales
Posted: 03 December 2007 06:12 PM   [ Ignore ]   [ # 3 ]  
Lab Technician
Avatar
RankRankRankRank
Total Posts:  2264
Joined  07-30-2007

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

 Signature 

Become a fan of the CodeIgniter Cookbook (estimated: Fall 2010).

Follow me on twitter here.
MichaelWales.com | MichaelWales.info

Profile
 
 
gtech
Posted: 03 December 2007 06:16 PM   [ Ignore ]   [ # 4 ]  
Research Assistant
Avatar
RankRankRank
Total Posts:  869
Joined  09-25-2007

no probs, glad you got it working.

Profile
 
 
   
 
 
‹‹ relative dates?      Oracle INSERTs failing, but no errors ››
Post Marker Legend
New Topic New posts Hot Topic Hot Topic with new posts New Poll New Poll Moved Topic Moved Topic Sticky Topic Sticky topic
Old Topic No new posts Hot Old Topic Hot Topic with no new posts Old Poll Old Poll Closed Topic Closed Topic Announcement Announcements
Theme
Change Theme
Visitor Statistics
The most visitors ever was 819, on March 11, 2010 11:15 AM
Total Registered Members: 120378 Total Logged-in Users: 19
Total Topics: 126497 Total Anonymous Users: 5
Total Replies: 665221 Total Guests: 279
Total Posts: 791718    
Members ( View Memberlist )
Newest Members:  sgpresz85,  Inkybro,  Ade Sugiandi,  garyhussey,  walterdave18,  lastminuteskid,  linces,  elitecamper,  vandylocks,  Schneeschuhe
Active Members:    boudewijn,  ccb700,  Chris Newton,  Crucial,  digitalbloke,  gstoa,  jalalski,  jikowhitewolf,  kgill,  logikit,  markwyner,  Oblique,  prolific,  pspvideoconverter,  regfix,  robertvn2k,  rossmurphy,  samota,  theprodigy
Powered By ExpressionEngine
ExpressionEngine Discussion Forum - Version 2.1.1 (20081028)
Script Executed in 1.0031 seconds
Atom Feed   RSS 2.0