sql question

mrblom

I have two tables where on table contains unique words, and the other one contains all the id's related to that word.

Say you want to search for three words and want to get out all id's that match all these words. How can you do that?

The table with the unique words only contains two fields.
ID which is a primary key and of course the WORD field.

The "relation" table contains its own "id" and a reference id to the WORD table.

I use PostgreSQL

nightowl

select ID from second_table where foreignkey_of_first_table_fieldname in (select keyfieldname from first_table where word = 'firstsearch' or word = 'secondsearch' or ...)

mrblom

That worked well!!!

But there is another issue...
I want to order by the one that looks most like my input...

how can i do that best? This was not as easy as it usually is with order by clauses.

thanks

Wien

More simple and I guess faster :

SELECT Relation.Id FROM Relation, Word 
WHERE Relation.WordID = Word.Id 
AND (Word.Value = 'xyz' OR Word.Value = 'xyzazz' OR Word.Value = 'xy123z' )

and for the second questions

SELECT Relation.Id , count(Relation.Id ) as NbRelation 
FROM Relation, Word 
WHERE Relation.WordID = Word.Id 
AND (Word.Value = 'xyz' OR Word.Value = 'xyzazz' OR Word.Value = 'xy123z' ) 
GROUP BY Relation.Id ORDER BY NbRelation desc

mrblom

Thanks alot... That really helped...

I also have a table containing all the information. This table is linked to the word table through the relation table.

When i use the query above as a subselect of my "main" query I get an error becouse there are to many fields in the subselect?
How can i avoid this and still have the effect of the subselect query...

This i basically my query:

SELECT * FROM table WHERE table.id in (... the above query ...)

but this fails because of the errors mentioned above..

ultraslacker

It is an additional join -

SELECT Relation.Id , count(Relation.Id ) as NbRelation

FROM Relation, Word, definition

WHERE Relation.WordID = Word.Id

AND definition.wordid = word.wordid AND (Word.Value = 'xyz' OR Word.Value = 'xyzazz' OR Word.Value = 'xy123z' )

GROUP BY Relation.Id ORDER BY NbRelation desc

If you have a one to many relation between word and definition, this will alter the ordering.

mrblom

The fields i want to select is in the data table, not the word or relation table.

The word and relation table is used only to accelerate a search.

The keys i get from the query matches entries in the table containing all the data. How can i get these fields by using one query.

I tried using the suggested query as a subselect? but the query returns two fields... so the use of subselect fails. Can anybody help?

thanks again?

ultraslacker

You may want to look into functions for this. You may also be able to do this with EXISTS (rather than IN).

mrblom

I tried using EXISTS but it didn't work. So if any of you guys have any other ideas I would appreciate it!