I feel like there should be a better way to do this... So I'm hoping someone will be able to advise.
We have contacts that belong to organizations. We also have a document tracker. Holding over from VFP you can have up to six organization ids on the document and up to six contact ids. Right now the select to see if a contact has documents looks like this... SELECT * FROM Contacts WHERE (id IN (SELECT contid1 FROM documents) OR id IN (SELECT contid2 FROM documents) OR id IN (SELECT contid3 FROM documents) OR id IN (SELECT contid4 FROM documents) OR id IN (SELECT contid5 FROM documents) OR id IN (SELECT contid6 FROM documents) OR orgid IN (SELECT orgid1 FROM documents) OR orgid IN (SELECT orgid2 FROM documents) OR orgid IN (SELECT orgid3 FROM documents) OR orgid IN (SELECT orgid4 FROM documents) OR orgid IN (SELECT orgid5 FROM documents) OR orgid IN (SELECT orgid6 FROM documents)) Which is UGLY... and I feel like there should be a better way (I know I could break that out into a many-many relationship via a third linking table but I'm not 'able' to do that now.) The only change I can think of is to union the two halves of the select but I'm not sure if that would be better... (IE id in (select contid1 from documents union select contid2 from documents) etc) Any advice or is this the best I'm going to get until I can reorganize the underlying structure? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
