Quoting Bruce Feist <[EMAIL PROTECTED]>: > Phil Dowson wrote: > > >SELECT t1.t1id > >FROM t1 > >LEFT JOIN t2 ON t1.t1id = t2.t2id > >WHERE t2.t2gid <> 192 //an example of a gid I am using// > > > >This is close, it shows all the id's that exist in the t2 table without a > >t2.t2gid of 192, but I also need to show all t1.t1id's that exist in the t1 > >table that do NOT exist in t2. > > > > > Ah, I think I understand now. Try > > SELECT t1.t1id > FROM t1 > LEFT JOIN t2 ON t1.t1id = t2.t2id AND t2.t2gid <> 192 > > By including the condition on t2.t2gid <> 192 in the ON, you're saying that > you only want the join to succeed if t2.t2gid <> 192. > > If I'm right about understanding what you want, then this is what ought to > happen in various cases: > > 1) No t2's with t1.t1id = t2.t2id: display t1.t1id > 2) t2's with t1.t1id = t2.t2id and t2.t2gid = 192: don't display anything. > (This is the one I'm least sure you want.) > 3) t2's with t1.t1id = t2.t2id but no t2.t2gid = 192: display t1.t1id > > Can there be multiple t2 rows for a given t1 row, some with t2.t2gid = 192 > and some with t2.t2gid <> 192? If so, what do you want to happen to them? > > Bruce > > > >
You know, this is confusing :-). I think you are beginning to get a better understanding of my own system that I do. > Can there be multiple t2 rows for a given t1 row, some with t2.t2gid = 192 > and some with t2.t2gid <> 192? Yes, what I need to happen is that all rows concerned center around the t2.t2gid. The statement needs to check only whether the row's t2gid doest not exist as 192, if true then display the t1id. I then want the statement to ignore any other entries in the table that have another t2gid value than 192. Literally in MSSQL server you would have (but I cant use subselects) SELECT t1id FROM t1 WHERE t1id NOT IN (SELECT t2id FROM t2 WHERE t2gid = 192) OR say (simplified for my own purposes) Only show t1id's that DO NOT appear in the statement SELECT t2id FROM t2 WHERE t2gid = 192 Thanks for your continued help, I'm still learning Phil --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
