TapeGun007 Posted September 9, 2015 Share Posted September 9, 2015 Prospects --------- ProspectCode ProspectBusinessName ProspectLastContacted ProspectCounty ProspectZip ProspectState Territories ----------- TerritoryID TerritoryCode (Should always match ProspectCode above) Territory (should always be a county name) TerritoryState TerritoryZip I want to join these two tables under these circumstances: ( ProspectCounty = Territory County AND ProspectState = TerritoryState OR ProspectZip = TerritoryZip ) AND ProspectLastContacted was last contacted 2 weeks ago. Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/ Share on other sites More sharing options...
TapeGun007 Posted September 9, 2015 Author Share Posted September 9, 2015 (edited) Here at least one of my attempts at this: SELECT * FROM Prospects p LEFT JOIN Territories t ON p.ProspectCounty = t.Territory WHERE (ProspectCounty = Territory AND ProspectState = TerritoryState) AND DATE(ProspectLastContacted) < DATE(NOW() - INTERVAL 14 DAY) ORDER BY p.BusinessName I won't use * once I get the right code, I realize that's not good. I just realized I forgot to put the OR in there. Edited September 9, 2015 by TapeGun007 Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520579 Share on other sites More sharing options...
Solution Barand Posted September 9, 2015 Solution Share Posted September 9, 2015 try SELECT * FROM Prospects p LEFT JOIN Territories t ON (ProspectCounty = Territory AND ProspectState = TerritoryState) OR (ProspectZip = TerritoryZip) WHERE DATE(ProspectLastContacted) < CURDATE() - INTERVAL 14 DAY ORDER BY p.BusinessName Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520580 Share on other sites More sharing options...
TapeGun007 Posted September 9, 2015 Author Share Posted September 9, 2015 I attempted this just to see if I could narrow it down. I realize I probably need to join based on State names and not counties. "SELECT * FROM Prospects p LEFT JOIN Territories t ON p.ProspectState = t.TerritoryState OR t.ProspectZip = t.TerritoryZip "; This yields nothing. Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520581 Share on other sites More sharing options...
TapeGun007 Posted September 9, 2015 Author Share Posted September 9, 2015 (edited) Hi Barand, Just to clarify... in the table "Territories" there is either a Territory (County) AND State set, OR just a Zip code with no State or County set. I checked my "Prospects" table to ensure they all have a "ProspectLastContacted" date that is at least 2 - 4 weeks ago. I tried your code and it give me nothing in return. Edited September 9, 2015 by TapeGun007 Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520582 Share on other sites More sharing options...
TapeGun007 Posted September 9, 2015 Author Share Posted September 9, 2015 Barand, I knew you had to be right, because so far you've ALWAYS been right. I started to debug... it wasn't working because it was ORDER BY p.ProspectBusinessName. Thank you so much! Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520583 Share on other sites More sharing options...
Barand Posted September 9, 2015 Share Posted September 9, 2015 It's a confusing data model that you have there. TerritoryCode (Should always match ProspectCode above) That implies that each prospect can have many territories, but it seem more likely that a territory would have many prospects and the model would be more like this +----------------------+ +--------------+ | Prospects | | Territory | +----------------------+ +--------------+ | ProspectCode | +-------| TerritoryID | | ProspectBusinessName | | | County | | ProspectLastContacted| | | State | | TerritoryID |>----+ | Zip | +----------------------+ +--------------+ Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520585 Share on other sites More sharing options...
TapeGun007 Posted September 10, 2015 Author Share Posted September 10, 2015 Yeah, I realized when I first wrote that .... that it wasn't the ID that needed to match. I get confused sometimes because normally I would match an ID. But the Sales People are assigned a "ReferralCode" that is unique to them already. It really goes like this: Sales | Prospects | | Territories | -------------- ------------------ ----------------- SalesCode Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520593 Share on other sites More sharing options...
TapeGun007 Posted September 10, 2015 Author Share Posted September 10, 2015 Somehow I clicked submit on accident. I use a "code" because the code is unique to each sales person and is assigned by another database that I have no control over. So instead of using a SalesID, I use a SalesCode, ProspectCode, and TerritoryCode. So the SalesCode must match in the TerritoryCode. In some rare cases I may let a sales person keep a lead that is not in their territory. Here is how the table actually works: Quote Link to comment https://forums.phpfreaks.com/topic/298113-join-problem/#findComment-1520594 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.