Question on how to show fields x,y,z only if data exists in another table

[Jfwatson]

I have two tables. I want to make a query that will show all the fields from table 1, but only if the data from column A in table one actually exists in column C from table two. I'm sure there might be a simple way to do this I just cannot figure it out. When I tried to build it, I ended up getting way more records than I even have on either of the tables. Basically, it was duplicating table 1 each time it noticed the data from table 2.



An Idea of what I have:

Table 1, Column A has a provider name. The other columns have all sorts of other data about them.
Table 2, column C has a provider name. (these are the only providers I care to look into)

I need to see all the data from table 1, but only if the provider in column A on table one is also listed on Table 2.


NOTE: Each table may have a single provider listed multiple times.

[pbaldy]

In a query, join the two tables on that field. By default you'll only get the records where the provider exists in both tables.

[Jfwatson]

Thats what I tried to do. The issue is that I only have 3214 rows on Table 1. When I Join the two in a select query by the "Provider Name" I end up with a result of over 127 thousand rows. I should only get 3214 rows max, but would expect less since I'm sure that every provider on table 1 does not actually exist on table 2.

[pbaldy]

What is the SQL of your query? Can you attach the db here?

[Jfwatson]

SELECT [DATA 4-10 Dmds Since 10-20-15].LTCLMT, [DATA 4-10 Dmds Since 10-20-15].[LTLAW#], [DATA 4-10 Dmds Since 10-20-15].STATUS, [DATA 4-10 Dmds Since 10-20-15].STADDDTE, [DATA 4-10 Dmds Since 10-20-15].LTRCVDT, [DATA 4-10 Dmds Since 10-20-15].LTRSNCD2, [DATA 4-10 Dmds Since 10-20-15].LTLGLACT, [DATA 4-10 Dmds Since 10-20-15].LTPRFX, [DATA 4-10 Dmds Since 10-20-15].LTPLNR, [DATA 4-10 Dmds Since 10-20-15].LTDMDPRV, [DATA 4-10 Dmds Since 10-20-15].EndrInd, [DATA 4-10 Dmds Since 10-20-15].EnrDate, [DATA 4-10 Dmds Since 10-20-15].LossDate, [DATA 4-10 Dmds Since 10-20-15].EdrType, [DATA 4-10 Dmds Since 10-20-15].PlaintiffLawFirm, [DATA 4-10 Dmds Since 10-20-15].PlnfFirmTaxIDFROM [DATA 4-10 Dmds Since 10-20-15] INNER JOIN [All Bills less ben ex, dups and] ON [DATA 4-10 Dmds Since 10-20-15].LTDMDPRV = [All Bills less ben ex, dups and].[Provider Name];

[Jfwatson]

<image deleted> Here it is

[pbaldy]

How many records in table 2? Could there be multiple records for providers that added up to the 127k?

[Jfwatson]

Table 2 has 30,761 rows. There might be a few providers on table two that are listed over a hundred times (or on 100 different rows). They might show up on table one on a few different rows as well. Ultimately, I'm wanting to narrow down table 1 by saying, "Well I dont need to look at all of these records because these providers do not even show up on table 2 at all".

[Jfwatson]

It believe it is multiplying my response. It's looking at table one and instead of showing me each row only if the provider in that row shows up in table 2, it's actually duplicating row 1 everytime that provider shows up in table 2, then duplicating row 2 each time that provider shows up in Table 2, etc....

[pbaldy]

Oh, I was thinking one of the tables contained a single row per provider. If that's not the case, create a query and join on that instead.

SELECT DISTINCT Provider
FROM Table2