[56K WARN] Query Help - Grouped items

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

Moderator: General Moderators

GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Looks like the mystery continues :)

I tried the query your DBA gave you, it works, in that it returns all of the links for the category id associated to the given name in the where clause, but it doesn't return the links from categories that are grouped with that... Essentially it returns the same thing as this..

Code: Select all

SELECT category_groups.group_id, categories.id, links.url, categories.name
FROM categories
INNER JOIN links ON categories.id = links.category_id
INNER JOIN category_groups ON categories.id = category_groups.category_id
WHERE categories.name = 'Testing'
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

I think I got it!

Code: Select all

SELECT category_groups.group_id, categories.id, links.url, categories.name
FROM categories
INNER JOIN links ON categories.id = links.category_id
INNER JOIN category_groups ON categories.id = category_groups.category_id
WHERE categories.name = 'Testing' OR category_groups.group_id = category_groups.group_id
This works! adding the OR did it! I'm going to add in more data and test it with more categories grouped, see if that works... but it should.. I think, ha..
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

GeXus wrote:I think I got it!

Code: Select all

SELECT category_groups.group_id, categories.id, links.url, categories.name
FROM categories
INNER JOIN links ON categories.id = links.category_id
INNER JOIN category_groups ON categories.id = category_groups.category_id
WHERE categories.name = 'Testing' OR category_groups.group_id = category_groups.group_id
This works! adding the OR did it! I'm going to add in more data and test it with more categories grouped, see if that works... but it should.. I think, ha..
I'm glad you got a solution. :-)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Well, this doesn't work :(, it's just using the OR to return matching groups ignoring the name in the lookup
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

lol, I think I got it!

Code: Select all

 SELECT category_groups.group_id, categories.id, links.url, categories.name
FROM categories
INNER JOIN links ON categories.id = links.category_id
INNER JOIN category_groups ON categories.id = category_groups.category_id
WHERE categories.id
IN (

SELECT category_groups.category_id
FROM category_groups
WHERE category_groups.group_id = (
SELECT category_groups.group_id
FROM category_groups
INNER JOIN categories ON categories.id = category_groups.category_id
WHERE categories.name = 'Testing' )
)
Post Reply