SQL query problem with AND

Question

I have a table that holds topic types another that holds materials. I then have another table which holds the keys of each table thus creating the many to many relation.

However, when I try to search the topics to pick out two topics which have share the same material it doesn't work.

Example tables:

Material Table:

MatID | Name
---------------
1     | book1
2     | note23

Topic table:

TID | topic
---------------
1   | computer
2   | database

MatTop table:

TID | MatID
------------
1   | 2
2   | 2

This is my query:

SELECT * FROM material
INNER JOIN mattop ON material.MatID = mattop.MatID
INNER JOIN topic ON mattop.TID = topic.TID
WHERE (topic.topic = 'computer') AND (topic.topic = 'database')

Thanks for any help.

EDIT - I know that the AND is the error sorry. I meant how do I get it to output the materials that have the topics associated with it.

Answer 1

Your problem is in the where clause:

WHERE (topic.topic = 'Design') AND (topic.topic = 'Notes')

topic.topic can never be both 'Design' and 'Notes' at the same time.

Did you mean to have your AND be an OR?

Answer 2

To get the two different topics in you need to join to it twice. (so Sonny Boy is correct in the problem is the where clause but OR is not the correct answer)

Something like (note I don't have the tables so not tested SQL)

Edit note : Sorry I think the original with 2 mattops is correct there was a version of this with only one so I have reverted to the original

SELECT * FROM material
  INNER JOIN mattop mattop1 ON material.MatID = mattop1.MatID 
  INNER JOIN topic topic11 ON mattop.TID = topic1.TID

  INNER JOIN mattop mattop2 ON material.MatID = mattop2.MatID 
  INNER JOIN topic topic2 ON mattop.TID = topic2.TID


  WHERE (topic1.topic = 'computer') AND (topic2.topic = 'database')

This should give the materials used in both topics of computer and database. A simple OR in the where clause will give materials in at least one of the topics but not necessarily both

Answer 3

If I understood you correctly,you want topics that share the same material.

here's how you do that:

SELECT t1.*,t2.* FROM topic t1 
JOIN mattop mt1 ON t1.TID = mt.TID 
JOIN mattop mt2 ON mt2.MatID = mt1.MatID 
JOIN topic t2 ON t2.TID = mt2.TID 
WHERE t2.TID <> t1.TID

this would yield a list of pairs of topics that share the same material.

Because SQl joins the table together as if they were 1 row/line, you can't have two distinct topic values at the same time unless you join in the topic table twice