[RESOLVED] reversing sql statement

bike5

I have this sql statement which does what I need it to do by selecting certain records basied on topic_id.

"SELECT e_files.name FROM e_topic
LEFT JOIN e_file_rel USING (topic_id)
LEFT JOIN e_files USING (file_id)
WHERE e_topic.topic_id = $_POST[topic_id]"

However, I need to also reverse it and get all the results that this above query does not get.

I have tried

"SELECT e_files.name,e_files.file_id FROM e_topic
LEFT JOIN e_file_rel ON (e_topic.topic_id <> e_file_rel.topic_id)
LEFT JOIN e_files USING (file_id)
WHERE e_topic.topic_id = $_POST[topic_id]";

"SELECT e_files.name,e_files.file_id FROM e_topic
LEFT JOIN e_file_rel ON (topic_id)
LEFT JOIN e_files USING (e_files.file_id <> e_file_rel.file_id)
WHERE e_topic.topic_id = $_POST[topic_id]";

"SELECT e_files.name,e_files.file_id FROM e_topic
LEFT JOIN e_file_rel ON (topic_id)
LEFT JOIN e_files USING (file_id)
WHERE e_topic.topic_id <> $_POST[topic_id]";

and do not get the results I need. If you have any ideas on what to try please let me know, thank you.

bike5

Maybe it will help if I ask from another angle. I have 3 tables.

Only columns I am using are shown)

e_files (file_id, file_name)
e_topic (topic_id)
e_file_rel (file_id, topic_id)

I need to retreive a file_id and file_name from the table e_files. I need to get all the results in the e_files table that are not already attached to a topic. e_file_rel holds the relationship between what file is attached to what topic. My first query already gets all related files to a topic, but now I need to show all the files that arn't associated with the topic (so like the exact opposite of my first query which gets all related files to topic).The variable I have to work with is $_POST['topic_id']. I cannot think of a way to do this with one query. Is this possible? I would think it should be. Thanks for any help.

bike5

I figured out a way:

"SELECT e_file_rel.topic_id,e_files.name,e_files.file_id FROM e_files
LEFT JOIN e_file_rel USING (file_id)
";

then while looping through the results check against $_post topic id and don't show if it matches.

YEAH!

I didn't want to have to use a if statement to make this work but as for now thats the only way I can get this to work.