Jump to content

A fun multitable problem. Can't get syntax right.


yeago

Recommended Posts

I have two tables.

 

Sites

 

id, site_info, etc.

 

Categories

 

site_id, category

 

Sites can be in multiple categories, new entry for each one.

 

1,Bad

1,Ugly

1,Obnoxious

2,Good

2,Wonderful

 

Say a user comes along and asks for all sites that are 'Ugly' and 'Obnoxious' but not 'Wonderful'' or 'Bad'

 

Now, for relevancy purposes I also get the number of times site_id appears in Categories, so that sites that match the most user included categories rise to the top.

 

First I used the following multitable query to get both the number of times site appears in category, but also the site info.

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id

GROUP BY categories.site_id, sites.id";

 

Then I thought: well why not get ONLY sites that share one or more of the categories that users want with this same query? Isn't it better just to get it all done now? So I...

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id and (category = "ugly" or category = "good")

GROUP BY categories.site_id, sites.id";

 

Then I thought why not just knock off the sites the user wants to exclude now? So I....

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id and (category = "ugly" or category = "good") and (category != "wonderful" and category !="hideous")

GROUP BY categories.site_id, sites.id";

 

--

 

My current problem: Sites that are wonderful and hideous are still showing up! Ideas?

Link to comment
Share on other sites

[!--quoteo(post=330189:date=Dec 24 2005, 02:14 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 24 2005, 02:14 PM) 330189[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Well, I don't really understand how your last query has any effect -- if you're already only including results "WHERE category IN ('ugly','good')", then the other ones would necessarily be excluded! Maybe you can shed some light on this.

 

 

User makes a list of sites heshe wants:

 

Include('good','bad','ugly')

 

And doesn't want:

 

Exclude('wonderful','hideous')

 

Category table goes like:

 

1,good

1,bad

2,good

2,wonderful

3,ugly

4,otherworldy

 

According to the user's include/exclude list, Site 2 has something they want but is excluded because it is also 'wonderful' which was excluded. Site 1 and 3 appear. Site 4 wasn't mentioned, and so it is not included either.

 

Howto?

Link to comment
Share on other sites

It can't work that way, because you have a GROUP BY clause, so you're collapsing all of the categories for each site into a single row so that you can get your counts -- unless you do a GROUP_CONCAT() and then search for each category string in the list.

 

You can't include & exclude sites in the same query, since the WHERE clause applies to a _single_ record, so it you ask for "category='good'", then obviously it's not equal to anything else! You need to run two queries -- or use a subquery -- to first include the desired ones but not the ones where they are sites that are undesired. So you do an "include" query -- without the GROUP BY -- and then add something like "AND NOT site_id IN ( SELECT site_id FROM categories WHERE category IN ('wonderful','hideous') )".

 

You'll have to do the counts separately. Hope that helps.

Link to comment
Share on other sites

 

You know, I had a feeling that was the case. Thank you.

 

SELECT sites. * , categories.site_id, count( categories.category ) FROM categories, sites WHERE categories.site_id = sites.id and ( categories.category = 'bad') and not site_id in( select site_id from categories where category in ('good','bad') )

 

I get a syntax error at select 'site_id...'

 

 

Link to comment
Share on other sites

Gonna do it using temp table I guess since MySQL version is only 4.0.25 . I don't know how efficient it will be.

 

One thing: how can I make count(categories.category) more friendly? Do I use 'AS'?

 

Instead of $r[categories.category] I'd prefer $r[relevancy].

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.