Join problem

[TapeGun007]

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.

 

[TapeGun007]

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

[Barand]

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

[TapeGun007]

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.

[TapeGun007]

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

[TapeGun007]

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!

[Barand]

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         |
+----------------------+             +--------------+

[TapeGun007]

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
 
 

[TapeGun007]

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: