Board index » delphi » SQL statement - Advice needed

SQL statement - Advice needed

Hi.

I have a KEYWORD table a PICTURE table and a PICTURE_KEYWORD
table (many to many relationship).
My question is: How do I select records from the
PICTURE_KEYWORD table where the PICTURE has 2 or more
specific KEYWORDS.

Example:
        PICTURE_KEYWORDS table
        pictureID   Keyword
        1               'A'
        1               'B'
        1               'C'
        2               'B'
        2               'C'
        3               'A'
        3               'B'
        3               'C'

I want the pictureid of all pictures having Keyword 'A', 'B' AND 'C'.
So I want my SELECT statement to return pictureID 1 and 3

What is the fastest way to do this?
My solution (the only one I've managed to come up with) is:

Select pictureID from PICTURE_KEYWORDS where Keyword='A' AND
pictureID in (Select pictureID from PICTURE_KEYWORDS where
Keyword='B' AND pictureID in (Select pictureID from PICTURE_KEYWORDS
where Keyword='C'));

This can't be very efficient. Is there another way to do this?

TIA
-Tommy

 

Re:SQL statement - Advice needed


What you are trying to do is an OR operation.

Try the following examples:

1. This migth not work in some db engines
select pictureID from PICTURE_KEYWORDS
  where Keyword IN ("A","B","C")

2. This is standart
select pictureID from PICTURE_KEYWORDS
  where Keyword="A"
    or Keyword="B"
    or Keyword="C"

3. On some db engines this the fastest way
select pictureID from PICTURE_KEYWORDS
  where Keyword="A"
union
select pictureID from PICTURE_KEYWORDS
  where Keyword="B"
union
select pictureID from PICTURE_KEYWORDS
  where Keyword="C"

Rui Domingos
rui.domin...@psidoc.pt

Re:SQL statement - Advice needed


Tommy,

This may also not be a practical solution, depending on the number of
keywords available, but here is another idea, in theory:

Create a temporary table with the fields pictureid, keyword1, keyword2,
keyword3

Then run 3 inserts into this table, in each case putting the keyword in the
correct column (insert into temp columns(picture1, keyword1) select
pictureid, keyword from picture_keyword where keyword = 'A'; repeat for
keyword2 & 'B', etc.)

You now have a table that contains only the pictureids that have any of the
keywords. It can be summarised (grouped) and selected:

select pictureid from temp group by pictureid having (keyword1 is not null)
and (keyword2 is not null) and (keyword3) is not null

There are a number of variations on this :
Use a single character or number in the temp table, instead of the keyword.
(Y/N or 0/1)
Insert/update the temp table to reduce the number of rows, also eliminates
the final grouping.
etc.

Another idea, which will not work on all versions of SQL, but which is
independent of the number of keywords:

Create a temp table with 1 column, the pictureid

Insert into this table all the pictureids that match keyword1

For each of the remaining keywords, delete the pictureids (from the temp
table, please!!) that do not have keywordn

delete from temp where pictureid not in (select pictureid from
picture_keyword where keyword = 'B')

At the end of this loop, you will be left with only the pictureid's that
match all the keywords.

(variation of this method - create 3 temp tables with pictureids for each
keyword, and inner-join them , which will give only the ones that are in all
three tables)

Have fun!!

Barbara

Quote
Tommy Nordvik wrote in message <6d36s6$3o...@toralf.uib.no>...
>Hi.

>I have a KEYWORD table a PICTURE table and a PICTURE_KEYWORD
>table (many to many relationship).
>My question is: How do I select records from the
>PICTURE_KEYWORD table where the PICTURE has 2 or more
>specific KEYWORDS.

>Example:
> PICTURE_KEYWORDS table
> pictureID   Keyword
> 1 'A'
> 1 'B'
> 1 'C'
> 2 'B'
> 2 'C'
> 3 'A'
> 3 'B'
> 3 'C'

>I want the pictureid of all pictures having Keyword 'A', 'B' AND 'C'.
>So I want my SELECT statement to return pictureID 1 and 3

>What is the fastest way to do this?
>My solution (the only one I've managed to come up with) is:

>Select pictureID from PICTURE_KEYWORDS where Keyword='A' AND
>pictureID in (Select pictureID from PICTURE_KEYWORDS where
>Keyword='B' AND pictureID in (Select pictureID from PICTURE_KEYWORDS
>where Keyword='C'));

>This can't be very efficient. Is there another way to do this?

>TIA
>-Tommy

Re:SQL statement - Advice needed


I think that is not what he needs.
For all cases he'll get pictureID 1, 2 and 3.
As i understood he needs only 1 and 3.

Toni

Rui Domingos <rui.domin...@psidoc.pt> schrieb im Beitrag
<6d4b4n$hc...@news.EUnet.pt>...

Quote
> What you are trying to do is an OR operation.

> Try the following examples:

> 1. This migth not work in some db engines
> select pictureID from PICTURE_KEYWORDS
>   where Keyword IN ("A","B","C")

> 2. This is standart
> select pictureID from PICTURE_KEYWORDS
>   where Keyword="A"
>     or Keyword="B"
>     or Keyword="C"

> 3. On some db engines this the fastest way
> select pictureID from PICTURE_KEYWORDS
>   where Keyword="A"
> union
> select pictureID from PICTURE_KEYWORDS
>   where Keyword="B"
> union
> select pictureID from PICTURE_KEYWORDS
>   where Keyword="C"

> Rui Domingos
> rui.domin...@psidoc.pt

Re:SQL statement - Advice needed


Yeah, I didn't understand the problem.
Sorry,.

Quote

Re:SQL statement - Advice needed


Hi,

there is another way to get the PictureIds but I don't know if the function you
need exists in every database.
Try the following statement:
select pictureID, List(keyword) as KeywordList from picture_keywords group by
pictureID;

The used function List() gets all keywords in a comma seperated list. You get:
PictureID  KeywordList
1              A,B,C
2              B,C
3              A,B,C

To get only the PictureIDs of the specific keywords you have to define a having
clause like:
select pictureID, List(keyword) as KeywordList from picture_keywords group by
pictureID having KeywordList='A,B,C';

Christian

P.S.:
This way works fine with Sybase SQL Anywhere 5.5.02 !!!
Sorry for my bad english.

Other Threads