December 31st, 2003, 08:48 AM
Comparing two tables for certain similarities
I have two tables. The first is Data, containing row upon row of values. The second is Param, which has a limited amount of rows, but contains only the specific values for 3 items that I am trying to select from the Data table.
I have created an Inner Join command that selects only the items from Data where Data.1=Param.1 and Data.2=Param.2 and Data.3=Param.3
But, after looking at the results, I am not sure that it actually succeeded in doing what I wanted it two. Is there another way to run this same type of query in order to give me something to compare results with?
December 31st, 2003, 09:38 AM
no, there isn't
if you could give some sample rows from each table, sample output from your query, the sql used by your query, and an explanation of whatever it is you feel may be lacking from your approach, then we would have more to go on
December 31st, 2003, 09:57 AM
Ok, here goes
These are the fields in the Data table:
SENSOR_ID, TERM_NPA, TERM_NXX, ORIG_OCN, BILLING_ID, CALL_TYPE, AMA_CALL, STRU_CODE, CIC_CODE, MOU, CAR_MOU
These are the fields of the Param table:
SENSOR_ID, AMA_CALL, STRU_CODE
So, I am trying to find those rows in the Data table where the SENSOR_ID, AMA_CALL, STRU_CODE matches any of the rows from the Param table. If the row matches two of the three, I don't want it, I only want rows from the Data table that exactly match a row from the Param table.
So, in the data I may have something that has
SENSOR_ID of 19999, AMA_CALL of 160, STRU_CODE of 60
if there is no row in the Param table that has that sequence, I do not want to select it.
The problem is, that when runing a join query on this data, it only gives me 89 records out of 45,000 that match. From other data I have, I should have gotten way more matches.
My query looked like this:
SELECT * FROM Data
INNER JOIN Param ON
(Data.Sensor_ID=Param.Sensor_ID AND Data.AMA_Call=Param.Ama_Call AND Data.Stru_Code=Param.Stru_Code);
December 31st, 2003, 10:06 AM
your query looks fine
please show this "other data" that you have
i.e. please show some sample rows, so we can actually set up a test table ourselves, to test your query to see why it's not working