{"action":"created","issue":{"url":"https://api.github.com/repos/salk31/RedQueryBuilder/issues/35","labels_url":"https://api.github.com/repos/salk31/RedQueryBuilder/issues/35/labels{/name}","comments_url":"https://api.github.com/repos/salk31/RedQueryBuilder/issues/35/comments","events_url":"https://api.github.com/repos/salk31/RedQueryBuilder/issues/35/events","html_url":"https://github.com/salk31/RedQueryBuilder/issues/35","id":63323421,"number":35,"title":"Using \"OR\" with a foreign key returns an inner join when should be an outter join","user":{"login":"scottdear","id":4229319,"avatar_url":"https://avatars.githubusercontent.com/u/4229319?v=3","gravatar_id":"","url":"https://api.github.com/users/scottdear","html_url":"https://github.com/scottdear","followers_url":"https://api.github.com/users/scottdear/followers","following_url":"https://api.github.com/users/scottdear/following{/other_user}","gists_url":"https://api.github.com/users/scottdear/gists{/gist_id}","starred_url":"https://api.github.com/users/scottdear/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/scottdear/subscriptions","organizations_url":"https://api.github.com/users/scottdear/orgs","repos_url":"https://api.github.com/users/scottdear/repos","events_url":"https://api.github.com/users/scottdear/events{/privacy}","received_events_url":"https://api.github.com/users/scottdear/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2015-03-20T22:50:28Z","updated_at":"2015-03-27T16:22:36Z","closed_at":null,"body":"If you use RQB and have two tables, a and b, and you create a foreign key which joins b to a, then you do a query where you specify a where clause that involves table a, then an \"OR\" and a where clause that involves table b, it should be creating an outer join.\r\n\r\nFor example, lets say we have company and weather and you link the two on zip code then do something like:\r\n\r\ncompanies.employees > 100\r\nOR\r\nweather.tmp < 50\r\n\r\nYou get SQL that looks like\r\n```sql\r\nSELECT * FROM companies\r\nINNER JOIN weather ON\r\ncompanies.zip = weather.zip\r\nWHERE\r\ncompanies.employees > 100\r\nOR\r\nweather.tmp < 50\r\n```\r\nWhat it should look like is:\r\n\r\n```sql\r\nSELECT * FROM companies\r\nOUTTER JOIN weather ON\r\ncompanies.zip = weather.zip\r\nWHERE\r\ncompanies.employees > 100\r\nOR\r\nweather.tmp < 50\r\n```\r\nThis is complicated, when using MySQL as it does not support OUTTER JOINS\r\n\r\nIt can be emulated by doing:\r\n\r\n```sql\r\nSELECT * FROM companies\r\nLEFT JOIN weather ON\r\ncompanies.zip = weather.zip\r\nWHERE\r\ncompanies.employees > 100\r\nUNION\r\nSELECT * FROM companies\r\nRIGHT JOIN weather ON\r\ncompanies.zip = weather.zip\r\nWHERE\r\nweather.tmp < 50\r\n```\r\n\r\nFrom a UI/End User perspective, it may be preferrable to do a LEFT JOIN so that you don't get any records from the right, that are not also on the left."},"comment":{"url":"https://api.github.com/repos/salk31/RedQueryBuilder/issues/comments/86994662","html_url":"https://github.com/salk31/RedQueryBuilder/issues/35#issuecomment-86994662","issue_url":"https://api.github.com/repos/salk31/RedQueryBuilder/issues/35","id":86994662,"user":{"login":"salk31","id":5011186,"avatar_url":"https://avatars.githubusercontent.com/u/5011186?v=3","gravatar_id":"","url":"https://api.github.com/users/salk31","html_url":"https://github.com/salk31","followers_url":"https://api.github.com/users/salk31/followers","following_url":"https://api.github.com/users/salk31/following{/other_user}","gists_url":"https://api.github.com/users/salk31/gists{/gist_id}","starred_url":"https://api.github.com/users/salk31/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/salk31/subscriptions","organizations_url":"https://api.github.com/users/salk31/orgs","repos_url":"https://api.github.com/users/salk31/repos","events_url":"https://api.github.com/users/salk31/events{/privacy}","received_events_url":"https://api.github.com/users/salk31/received_events","type":"User","site_admin":false},"created_at":"2015-03-27T16:22:36Z","updated_at":"2015-03-27T16:22:36Z","body":"Simplest thing would just to always do a left join?\r\n\r\nIt wouldn't make any difference to AND since all comparisons would be against null so fail anyway?"}}