SQL Query help (Count problem)

[katiafudge]

Hi,

I am working on some queries for my final year project and one has got me a little stumped. I can do it a simpler way with my program but i thought getting the values from one query woudl be better.

I have these tables:

Session Keyword
SESSID KWID KWID KW_Name
S0001 KW0001 KW0001 sun
S0001 KW0003 KW0002 sea
S0001 KW0004 KW0003 traffic
S0002 KW0002 KW0004 road
S0002 KW0001

Ok and i want to get a query that will count all the keywords that are the same as the ones in the SQL query for a specific session. This is to get a value for a similarity calculation. So far i have this:

SELECT COUNT(session.KWID) FROM session_tb, keyword WHERE SESSID = 'S0001' AND KW_Name = 'sun';

But it returns the value 3, so its only counting how many keywords session 1 has and not specific to the keyword name, it should only return the value 1.

Any help? I'm probably doing something wrong.

I am basically wanting to calculate how many keywords a query has with the current quesry submitted, sun.

Thanks

:D

[mdb002]

You just forgot to join the tables, you can use 2 methods (SQL may differ slightly depending on your DB system)



SELECT COUNT(session.KWID) FROM session_tb, keyword WHERE SESSID = 'S0001'
AND KW_Name = 'sun' AND session_tb.KWID=keyword.KWID;


Or


SELECT COUNT(session.KWID) FROM session_tb
INNER JOIN keyword ON session_tb.KWID=keyword.KWID
WHERE SESSID = 'S0001' AND KW_Name = 'sun';