| Author |
Topic |
|
bernie
Starting Member
6 Posts |
 Posted - 2010-01-23 : 08:55:19
|
| Hi all,Im new to SQL and I'm a bit lost with a query. From a table like this:*****************url | feature***************** www.a.de | x www.a.de | y www.b.de | xwww.n.de | p*****************I would like to query all urls that have the features x and y (both). In this case, the result would be www.a.de How can I do this? Thanks! bernie |
|
|
proxteam
Starting Member
2 Posts |
Posted - 2010-01-23 : 11:03:14
|
| SELECT urlFROM table AS objTableWHERE (feature = 'x') AND EXISTS (SELECT feature FROM table WHERE (objTable.text = text) AND (feature = 'y'))Greez proxteam |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-23 : 11:55:24
|
quote:
Originally posted by proxteam
SELECT urlFROM table AS objTableWHERE (feature = 'x') AND EXISTS (SELECT feature FROM table WHERE (objTable.url = url) AND (feature = 'y'))Greez proxteam
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bernie
Starting Member
6 Posts |
Posted - 2010-01-23 : 12:04:54
|
| Thanks proxteam,works! But a follow up: Could it be better to create a different table or view to improve performance of this kind of query? Not sure if this question makes sense. It's just that the query seems a bit complicated to me. And I wonder if my table setup is maybe not the best for this purpose.Thanks, b |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-23 : 12:14:38
|
| Nothing wrong with a self referencing query like this.Whether the design of your database is ideal is another question! Not enough information here to give you advice on that. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-23 : 12:14:58
|
There is a one to many relationship between url and feature.It would be better to have a table for url and another table for the features.for example:url_tableid inturl varchar(500)feature_tableid inturl_idfeature
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bernie
Starting Member
6 Posts |
Posted - 2010-01-23 : 12:53:25
|
| Ok, I get the impression I need to think about my database design.It's actually a many-to-many relation. A url has many features and a feature can occur in many urls. And I will regularly have to query for all urls with features x,y,z,... How would a good table setup look like for this? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-23 : 13:38:31
|
many to many needs a third table to link url_id with feature_id.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 04:43:50
|
| Third table is definitely the "text book answer", but I think it depends a bit ....The URLs make a good natural key (although they are quite long)I don't know about the features. If they are simple words then they would be good natural keys too.If you are going to store some other data about the URLs, or the Features (Active/Inactive, Long description, etc.) then definitely separate tables and a "third table" to link them. But if it is literally URL + FeatureName then I'm not so sure.Even then, a separate table reduces the likelihood of the FeatureName being wrongly spelt, or inconsistently used. |
|
|
|
bernie
Starting Member
6 Posts |
Posted - 2010-01-24 : 05:47:32
|
| Ok, I will try to use URLs and features as keys. But I have to think and read a bit more on how to setup my tables. Maybe I will come back to that later, with more detailed info...Thanks for your help! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 06:16:51
|
| url_table========URL_ID intURL varchar(500),... other columns ...feature_table=============Feature_ID int,Name varchar(50),... other columns ...URL_FeatureLink table=====================URL_ID int,Feature_ID int |
|
|
|
|
Query newbie question