New topic: Ordering a Many to Many
<http://forums.realsoftware.com/viewtopic.php?t=45890> Page 1 of 1 [ 1 post ] Previous topic | Next topic Author Message neonash7777 Post subject: Ordering a Many to ManyPosted: Tue Nov 13, 2012 1:08 pm Joined: Mon Nov 29, 2010 7:01 pm Posts: 307 I have a table called Relationships that works like this: COLUMN Example Table1 Contacts Table1Id 5 Table2 Merchants Table2Id 3 Reason Contact is the owner of the Merchant The problem is that every time I want to get all of the relationships (and especially specific relationships) an object has, I have to write a complex query. This is due to the fact that you don't know whether the primary object is in table1 or table2. Something like this does the trick: `Contacts` as c LEFT JOIN (SELECT (CASE`Relationships`.`Table1Id` WHEN '/*refid*/' THEN `Relationships`.`Table2Id` ELSE `Relationships`.`Table1Id`END) as 'rid' FROM `Relationships` WHERE (`Relationships`.`Table1`='/*table*/' AND `Relationships`.`Table2`='/*reftable*/' AND `Relationships`.`Table2Id`='/*refid*/') OR (`Relationships`.`Table1`='/*reftable*/' AND `Relationships`.`Table1Id`='/*refid*/' AND `Relationships`.`Table2`='/*table*/')) as RelTable ON c.`Id` = RelTable.`rid` While it works - it's annoying to use and I have to imagine there's a better way! Is there a better way? Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 1 post ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
