-2

I have 3 tables: KeyWords, GrantsKeyConn, Grants. The way it is set up, each "grant" has associated "keywords", which are stored in the KeyWords table as such:

KeyWords Table

Each "keyword" is associated/connected to a specific "grant" in the GrnatsKeyConn table as such:

GrantsKeyConn Table

... so that multiple "keywords" can be associated/connected to one "grant". Finally, each "grant" is stored in the Grants table as such:

Grants Table

I'm trying to filter out grants by specifying keywords in a textbox. So, say I specify the keywords "test, new, final"... then the result would filter out only grants that have those keywords associated with them; it doesn't have to result in grants that only have the 3 keywords associated with them; it can result in grants that have 1, 2, 3, ..., all of the keywords specified. In addition, if a grant has more than 1 keyword associated with it, I don't want it to show more than once in the results.

So how would I make this query? I don't want to do a select from KeyWords table to get ID of a keyword, then use that ID to go into the GrantsKeyConn table to get the associated grants, then go to the Grants table to extract the right grants. If so, how would I do this?

Let me know if I need to further clarify my question.

Hristo
  • 45,559
  • 65
  • 163
  • 230
  • Please review the answer you choose, since it's a huge deal slower then my answer. And I honestly think this is advising people to use a method which purpose was never to achieve this. – Aidiakapi Mar 16 '11 at 15:51
  • @Aidiakapi... would your solution be able to return distinct results? For example, if a grant has more than 1 keyword associated with it, I only want that grant to show up once instead of once for each keyword its associated to. Where does the `DISTINCT` idea come into play? – Hristo Mar 16 '11 at 16:09
  • The `DISTINCT` always comes after the `SELECT`, just like with the other example. – Aidiakapi Mar 17 '11 at 10:59

2 Answers2

2

You might want to do something like this

select * from grants g, grantsKeyConn gKC where g.id = gKC.grantsId and gKC.KeyWordsID in (select id from KeyWords where KeyWord in ('new','final','test');

The problem with this is that if you are searching for grants that have multiple keys you will get multiple results. You could enhance it to be something like...

select DISTINCT(g.id) from ....

And then pass that into another call.

The other option is to use something like hibernate and have it map everything for you.

user321605
  • 846
  • 2
  • 7
  • 20
CtrlDot
  • 2,463
  • 14
  • 11
  • 1
    This is not what he asked for, he asked for a join, this is a subquery, and therefore also slower. – Aidiakapi Mar 15 '11 at 15:48
  • @CtrlDot... what does the syntax `grants g` and `grantsKeyConn gKC` mean? – Hristo Mar 15 '11 at 17:57
  • @Aidiakapi You would actually have to analyze the path that the database server took. You can't just make a blanked statement that a subquery is slower. An inner join is doing a lot of operations on entire tables before actually doing the query. – CtrlDot Mar 15 '11 at 19:28
  • @Hristo Basically it is a way in sql to give a "variable" name to the table you want reference. grants g and then I can go g.* to get all columns in grants – CtrlDot Mar 15 '11 at 19:29
  • @CtrlDot an inner join is the optimized way of doing the same with a subquery. Note the **optimized**, inner joins is a mechanism that's supposed to be used in this case, just like he requested. And it's just really bad advice to him letting him use a subquery, but you know be happy, your bad answer got accepted, while it was later, an obviously not what he asked. – Aidiakapi Mar 16 '11 at 12:43
  • @Aidiakapi I stand corrected, you are right. Sorry. Reference: http://stackoverflow.com/questions/141278/subqueries-vs-joins – CtrlDot Mar 16 '11 at 14:14
  • @CtrlDot Yes, that's why I strongly dislike your solution. If you have a database of let's say 1,000 records, and joined with the other table it'll be 1,000 * [count in link table], mostly a link table has about 10 links per normal record so that 10,000,000 rows, for each row it'll first check if both id's are right, then you are filtered back to 10,000 records, but remember that it already had 10,000,000 in memory, and maybe even had to store them temp. then it'll go and execute that other query 10,000 times. So that's why an INNER JOIN is way much more effective. Imagine with 10,000 records. – Aidiakapi Mar 16 '11 at 15:50
1

You could use this:

SELECT
--Choose the columns you want here
FROM
Grants
INNER JOIN GrantsKeyConn ON Grants.ID = GrantsKeyConn.GrandsID
INNER JOIN KeyWords ON GrantsKeyConn.KeyWordsID = KeyWords.ID
WHERE
--Filter here
ORDER BY
--Order here

If you need any more help, let me know. This'll return all links, but since it seems like a pretty big table, just query the columns that you actually use.

Also if you want to know how to query a database from C# then you have a lot of options. You might find this useful.

Aidiakapi
  • 6,034
  • 4
  • 33
  • 62