Page 1 of 2
[56K WARN] Query Help - Grouped items
Posted: Sat Oct 27, 2007 6:44 pm
by GeXus
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Ok... Im lost. I have the following tables
Categories
- id
- name
Category_Groups
- id
- category_id
- group_id
Links
- id
- url
- category_id
The way this works is you create categories... then you can group similar categories... the group_id is just an incremented id based on the last created group_id...
Anyways, I'm trying to create a query that selects url from links where name = 'Category Name'
This is what I have...
[syntax="sql"]
select links.url, links.category_id, links.guid from links
inner join categories on links.category_id = categories.id
where categories.name = 'Test'
Simple enough... but this will only select links that are from the category_id that has the name of 'Test'... well I grouped together two categories, 'Test' and 'Testing'... some links are assigned to Testing, some are to Test, but I want to list them all... since their grouped.
Any help would be great!! Thanks
feyd | Please use[/syntax]Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Sat Oct 27, 2007 7:13 pm
by GeXus
Just want to give an example of how this would be done with individual queries...
Code: Select all
//get category_id
SELECT id from categories where name = 'Test'
//returns 6
//get group_id
SELECT category_groups.group_id from category_groups where category_id = 6
//returns 7
//get all grouped category_id's
SELECT category_id FROM category_groups WHERE group_id = '7'
//returns 1 and 6
//get all links
SELECT * FROM link WHERE category_id IN (1,6)
//returns all links for category name 'Test'and any categories that are in the same group as 'Test'
So, this would work.... but it's just not ideal...
Posted: Sat Oct 27, 2007 10:05 pm
by califdon
How about:
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id AND Category_Groups.category_id = Links.category_id
I haven't tried it, but on paper it looks like it should do what you want, or close to it.
Posted: Sun Oct 28, 2007 12:42 pm
by GeXus
califdon wrote:How about:
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id AND Category_Groups.category_id = Links.category_id
I haven't tried it, but on paper it looks like it should do what you want, or close to it.
How would I add the name though? Ultimately it's looking it up based on a category name?
Posted: Sun Oct 28, 2007 1:33 pm
by John Cartwright
Posted: Sun Oct 28, 2007 2:37 pm
by GeXus
The name would have to be in the where clause... that's what it's all being looked up by
Posted: Sun Oct 28, 2007 2:53 pm
by John Cartwright
GeXus wrote:The name would have to be in the where clause... that's what it's all being looked up by
Ah, your question wasn't very clear. Nor is it still

.
Posted: Sun Oct 28, 2007 3:11 pm
by GeXus
Ha.. hmmm.... Not sure how else to explain it.. the sql I gave above, is exactly the result i'm looking for... it all starts from a name that is used to get the category_id and then get all corresponding category_ids if they fall within the same group as the original category_id (if it's even in a group)..
Just to add... the solution would be combining the sql I posted above into one query.
Posted: Sun Oct 28, 2007 4:02 pm
by califdon
GeXus wrote:califdon wrote:How about:
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id
AND Category_Groups.category_id = Links.category_id
I haven't tried it, but on paper it looks like it should do what you want, or close to it.
How would I add the name though? Ultimately it's looking it up based on a category name?
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id
AND Category_Groups.category_id = Links.category_id
AND Category_Groups.category_id = 'whatever'
?
Posted: Sun Oct 28, 2007 6:39 pm
by GeXus
califdon wrote:GeXus wrote:califdon wrote:How about:
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id
AND Category_Groups.category_id = Links.category_id
I haven't tried it, but on paper it looks like it should do what you want, or close to it.
How would I add the name though? Ultimately it's looking it up based on a category name?
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id
AND Category_Groups.category_id = Links.category_id
AND Category_Groups.category_id = 'whatever'
?
Looks like it could be close....
I changed the last condition, the 'whatever' is against categories.name
Code: Select all
SELECT url FROM Categories, Category_Groups, Links
WHERE Categories.id = Category_Groups.category_id
AND Category_Groups.category_id = Links.category_id
AND Categories.name = 'whatever'
This returned all the links assigned to the category_id associated to the 'whatever' name... However, it did not get the links from the other category_id's that are assigned to the same group...
Posted: Mon Oct 29, 2007 3:41 pm
by califdon
Your problem is more complicated than it looks at first glance. I thought I could see how to get the results that you want, but I'm really having problems. When I have found myself in such a bind in the past, it has nearly always turned out that the real problem is in the data schema. If there's a flaw in the schema, you'll never be able to extract the data you want. I don't immediately see the flaw in your schema, but my hunch is that it won't support the queries you need.
Trying to think of your underlying model, it's not a simple one-to-many or even many-to-many relationship. I think that's the real problem. I picture it like this:
Code: Select all
LINKS CATEGORIES GROUPS
id cat_id group_id
url cat_name cat_id
cat_id
Example records:
101 wxy.com 1 1 Green 11 1
102 abc.com 1 2 Blue 11 2
103 lmn.com 2 3 Red 12 2
104 rst.com 3 12 3
13 1
13 3
I don't see the relationship, once you have chosen a Category, to get back to the Links, based on all Groups that contain that Category.
Posted: Tue Oct 30, 2007 9:49 am
by GeXus
Califdon, thanks for your help... that's a good answer... I think I'll just stick to using multiple queries.. I suppose I could get rid of the groups table, and add a group_id to the categories table... but only maybe 5-10% of the categories are in groups... Do you think that would provide faster querying? I don't care much about insert.. this will be primarily all selecting..
Posted: Wed Oct 31, 2007 7:45 pm
by califdon
GeXus wrote:Califdon, thanks for your help... that's a good answer... I think I'll just stick to using multiple queries.. I suppose I could get rid of the groups table, and add a group_id to the categories table... but only maybe 5-10% of the categories are in groups... Do you think that would provide faster querying? I don't care much about insert.. this will be primarily all selecting..
Sure, that would simplify the queries, but it would be somewhat clumsier to maintain, such as adding a new grouping--you'd need to modify several category records. If there's much changing going on in the groupings, this could be an issue.
Today I sent out the monthly announcement for our local Access User Group meeting, to be held a week from Friday. I included this in the announcement (just for you!!

):
In an Access forum I follow, someone had a question about a query against three related tables. I tried to provide an answer, but soon found that I couldn't figure it out, either. It seems not too difficult at first, but I still can't quite see my way through it. I suspect the problem is with the schema, but I can't see how to design it any better. Here's the issue, if you have time to think about it, and we may discuss it at the meeting:
We have a table of URLs:
tblLinks:
id (PK)
url
cat_id (FK)
and a table of categories:
tblCats:
cat_id (PK)
cat_name
Easy, so far. Every URL is linked to a named category. Simple to list all URLs in a category.
But he wants to be able to arbitrarily group categories into 'clusters' and query all URLs that are in ANY category that is in the same group. He created a third table:
tblGroups:
grp_id
cat_id
What kind of a SQL statement (if any) could return all URLs that are in any category that is grouped with a selected cat_name?? It's harder than it sounds--at least for me!
So if you can wait that long, maybe someone will come up with something.
Oh, and yesterday at lunch with a couple of colleagues, I discussed your problem, too, and I
just now received this reply from one of them, a DBA who manages a large Access application:
You explained the problem well.
If the URL is given, the statement should be:
SELECT tblLinks_1.url
FROM ((((tblLinks INNER JOIN tblCats ON tblLinks.cat_id = tblCats.cat_id) INNER JOIN tblGroups ON tblCats.cat_id = tblGroups.cat_id) INNER JOIN tblGroups AS tblGroups_1 ON tblGroups.grp_id = tblGroups_1.grp_id) INNER JOIN tblCats AS tblCats_1 ON tblGroups_1.cat_id = tblCats_1.cat_id) INNER JOIN tblLinks AS tblLinks_1 ON tblCats_1.cat_id = tblLinks_1.cat_id
WHERE (((tblLinks.url)=[Given URL:]));
He sent me these two screen shots of his QBE grid. I haven't had a chance to try them, myself, but as we had discussed at lunch, they involve using the same table more than once in the same query, a technique I have rarely had occasion to use. You will see that one of them is for finding by url and the other by category name.

Another input
Posted: Thu Nov 01, 2007 12:37 pm
by califdon
Another of our User Group members responded with the following:
I created 3 tables; Link, Cat, Grp. These are my values
CatID CatName
1 Cat1
2 Cat2
3 Cat3
4 Cat4 'not used in sample
GrpID CatID
1 1
2 2
3 3
ID Url CatID
1 Url1 1
2 Url2 2
3 Url3 3
4 Url4 4
5 Url5 4
6 Url6 4
7 Url7 3
8 Url8 2
So I came up with the following SQL.
SELECT Grp.GrpID, Cat.CatID, Cat.CatName, Links.Url
FROM (Grp INNER JOIN Cat ON Grp.CatID = Cat.CatID) INNER JOIN Links ON
Cat.CatID = Links.CatID
WHERE (((Cat.CatName)=[Enter CatName]));
I'm not sure if that's what the user wants. If not, it's mostly due to not fully understanding the problem. Can a category reside in more that one groups?
In my concept above, a category can only reside in 1 group. This is due to GrpID being a primary key. If his concept is a category that can exist in 1 or many groups then maybe he needs to have a Grp's table like
GrpID GrpName
And a new table; GrpCats.
GrpCatsID GrpID CatID
GrpCatsID would be autnumber. The GrpID would store the from Grps, CatID from Cats
Since GrpID and CatID aren't primary keys in GrpCats then more than combination of grp/cat can be created. IOW, we have a junction table.
If that's not correct then I definitely don't understand the problem.
Posted: Thu Nov 01, 2007 12:56 pm
by GeXus
Califdon, thanks so much! That's awesome... I'm going to try out that query this evening, I'll let you know how it runs... really appreciate all of your help, this is great!
