Join with IF Statement

[alexmb1]

I have two tables I am trying to join. Table one contains financial data and table 2 contains vendor infomation that relates to the data via a vendor_no. Because of the data quality issues I have to deal with I cannot match up a vendor_no to a location (DETLOC) 100% of the time. In in a case where there is not match, would like to group those charges based on the region in which they occurred. This could be done via and org_code that links to a particular region, for instance, Atlanta.

Right now this is my code:

SELECT tbl_other_4.*, FY11_VENDOR.detloc
FROM tbl_other_4 LEFT JOIN FY11_vendor ON tbl_other_4.vendor_no=FY11_vendor.vendor_no
WHERE left(tbl_other_4.[org code],8)=left(FY11_vendor.organization,8)

the DETLOC is a unique identifier of a location to which a vendor provided a service. Because a vendor can provide services to more than one location, I also have to match it to the first 8 numbers in the organization code so that I get the correct DETLOC match.

Here is the ideal situation. Say I have ten charges that I know were made by vendors in Atlanta. However, I can only match 8 of those charges to specific DETLOC. In my current join, the other two charges would not show up b/c there is no match. I would like those two to be labeled "Atlanta" if there is no DETLOC match but the org_code matches Atlanta. Since I have 24 regions across the country, this would repeat itself for places like New York where I encountered the same problem with my data.

One of the things that puzzles me is that with the left join, I should get all of the rows from TBL_OTHER_4, regardless of weather there is a match in the FY11_vendor table. Then I could just look for the nulls and add in the values that way. However, I am only getting back rows where there is a match. Am I just doing something wrong with my join?

Thanks for the help,
Alex

 

[Brianwarnock]

The Where clause must also be complied with, thus you will only get those with a match.

Brian

 

[alexmb1]

Is there a workaround solution for that? The reason is because I have some vendors at multiple locations. If I just match vendor_no with a DETLOC I will get all of those DETLOCs for each financial transaction record with that vendor_no.

 

[Brianwarnock]

I have tried to get my head around this but in the end I just can't get a grasp of your data.

What causes this to be not true but the recoprd to be required

WHERE left(tbl_other_4.[org code],8)=left(FY11_vendor.organization,8)

is just one of the puzzles I have.

What do Vendors at multiple locations have in the organisation field?

I would need to see some data, however in fairness I must point out that I am currently spending alot of time visiting my wife in hospital, hopefully somebody else can pick this up.

Brian

 

[alexmb1]

I will give you an example that hopefully can explain things a little better. Lets start with Atlanta. As I mentioned, there are 24 'regions' that the country is broken up into. Each has their own 20 digit Organization Code. After reading the first 8, you kow the region. The rest are there to signify sub-regions.

I have a table with thousands of vendors from all over the country. Each of these vendors provides a service to one or more DETLOCs (specific locations, i.e. Stewart County jail). In the finacial data, I see a row that tells me the organization code, the vendor_no and name, and the expense. I then use a JOIN to bring in the DETLOC information from another table that I have worked to build. This matches a vendor_no with a particular DETLOC so I can group expenses by DETLOC. So for instance, I know Smith Security Services works exclusively at the Mecklenberg Regional Jail so in my vendor table I put the DETLOC for Mecklenberg next to that vendor and I can then match the expenses. The DETLOC is in this case a 3-7 letter code that corresponds to one and only one location.

However, I also have larger vendors that do services in multiple places, either in the same region, i.e., Atlanta, or across multiple regions. For instance, the ACME Jail Company provides guard services to 3 facilities in 3 regions, Atlanta, New Orleans, and Miami. Each of these regions has a specific Org code as well, again the can be identified by the first 8 digits of the Org Code. The ACME company however, only has one vendor number. So in the financial data when I see the three expenses where the ACME company is the vendor, I need to match the first 8 digits of the Org code that match the DETLOCs that I know ACME provides services for (in the DETLOC table I have a column that matches the Org code for each DETLOC b/c I obviously know the location).

If I do not tell the join to match the Org code, the resulting table will have 9 rows instead of 3 because the 3 DETLOCS that correspond to ACME will be matched with each of the expenses.

I have attached a simple spreadsheet that illustrates what the tables look like.

 

[Brianwarnock]

It appears to me that you should drop the join on Vendor and just match using the Where clause as it is the Detloc that is important not the vendor number.

Brian

 

[alexmb1]

That would work but I have 1300 DETLOCs spread over 24 regions so the relationship is a many to one (org code). The vendor_no uniquely identifies things. Right now, I am capturing 90% of the expenses. That is fine in terms of projection. But still want to capture all the charges that are Org code 18-00 (Atlanta) and don't have a matching DETLOC in the table. But as we have seen, I encounter a problem with and without the Where clause unless I can figure out a better way to structure the query.

 

[Brianwarnock]

That would work but I have 1300 DETLOCs spread over 24 regions so the relationship is a many to one (org code).

So are you worried about performance? As I see it you are probably going to have to do this anyway to find the non-match, although I still can't get my head round your data.

The vendor_no uniquely identifies things.

Surely if that were true we would not have the problem.

Brian

 

[Brianwarnock]

I have tried to get my head around this but in the end I just can't get a grasp of your data.

What causes this to be not true but the recoprd to be required

WHERE left(tbl_other_4.[org code],8)=left(FY11_vendor.organization,8)

is just one of the puzzles I have.

Brian

Thinking about it over breakfast I keep coming back to the above, Is the
field FY11_vendor.organization Null if so you can allow for that with Or FY11_vendor.organization is null and then use an if to substitute the org for the detloc in the query, I think.

Brian