[SOLVED] WHERE IN shoud be AND not OR

[sw9]

Hi,

 

I think this is a pretty basic question. I am using a WHERE IN clause in my statement below:

 

select cs.field_program_nid, n.title from content_type_content_showing cs 
LEFT JOIN node n ON (cs.field_program_nid = n.nid)
LEFT JOIN term_node tn ON (cs.field_program_nid = tn.nid) WHERE tn.tid IN ('206', '229' , '230' ) AND cs.nid = 56893

 

I thought this syntax is the same as saying:

WHERE tn.tid = '206' AND tn.tid = '229' AND tn.tid = '230'

 

But in fact the results I am getting are as if it said

WHERE tn.tid = '206' OR tn.tid = '229' OR tn.tid = '230'

 

How can I get it to do a WHERE IN with the above AND result? I don't want to join the table multiple times, as I never know how many tn.tid values will be put in the query...help is greatly appreciated!

 

Thanks!

[rhodesa]

tn.tid can't equal all three value at the same time

[sw9]

OK, so I need to JOIN the table multiple times. Thanks for getting back to me.

 

Along this same line (which is why I won't start another post), I have another query where I've joined the same table twice so that I can make a similar statement, but I can't get this working either. Here is my query:

 

SELECT DISTINCT NOW(), cp.nid, TIMESTAMP(cp.field_pdate_value), n.title 
FROM content_type_content_program cp 
LEFT JOIN node n ON (n.nid = cp.nid) 
LEFT JOIN term_node tn ON (cp.nid = tn.nid)
LEFT JOIN term_node tn2 ON (cp.nid = tn2.nid) 
LEFT JOIN term_data td ON (tn.tid = td.tid)  
WHERE tn.tid = '230' AND tn2.tid != '229' 
AND TIMESTAMP(cp.field_pdate_value) <= NOW() 
AND cp.field_mv_path_value != '' 
ORDER BY cp.field_pdate_value DESC 
LIMIT 4

 

So I've joined term_node twice so that I can make two equations with it. When I run this statement, it will follow the "tn.tid = '230'" WHERE clause, but it seems to disregard "AND tn2.tid !='229'".

 

I need to be able to get all the results where tn.nid is not associated with tn2.tid 229 but IS associated with tn.tid 230.

 

Am I going about this totally wrong?

Thank you again for your assistance.

[rhodesa]

I'm having trouble visualizing this...any chance I can get a dump of the table structure/data and sample of how you want the result set to look?

[sw9]

What you said made me go back and try to figure out how to better explain this. I have been trying to write a query that would output me the results I want, but I just can't get it to work. But I think I can explain it a bit clearer now and explain that table better.

 

I am working with videos that are associated with certain categories. Each video has an ID and each category has an ID. The table term_node takes the video id (nid is the column) and the category id (tid is this column). So let's say I have two vidoes. One with nid '1' and the second with nid '2'. and I have 10 categories, three of which are "Music" (tid '230'), "Youth" (tid '228') and "Art" (tid '229'). I have many videos and some of them are categorized in both art and music, and sometimes other things. Here is how my table might look:

 

nid | tid

1  | 230

1  | 229

2  | 230

2  | 228

 

I want my statement to return the nid(s) that are NOT associated with Art (the tid of '229'), but ARE associated with Music (the tid of '230'). With the above example, I would want it to only return nid '2'.

 

So I have toned down my statement to make it less confusing. This is what I thought I should say, but it still returns nid 1 and 2.

SELECT nid FROM term_node
WHERE EXISTS (SELECT DISTINCT nid FROM term_node WHERE tid = '229') AND tid = '230'

 

Hopefully that is more clear; I am really banging my head against the wall on this. Thanks for helping!

[rhodesa]

It does make sense now. I couldn't think of a way to do it with JOINs, but was able to find a solution with nested SELECTs:

 

SELECT * FROM node
  WHERE nid IN (
    SELECT nid FROM term_node
      WHERE tid = '230'
  ) AND nid NOT IN (
    SELECT nid FROM term_node
      WHERE tid = '229'
  )

[sw9]

THANK YOU, thank you, thank you rhodesa. I don't know why I couldn't wrap my head around it; seeing it makes perfect sense. I really appreciate you tinkering through it with me. 

[fenway]

Just remember that NOT IN is poor for index usage... better to LEFT JOIN with IS NULL.