#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    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?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    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);
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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


    rudy

IMN logo majestic logo threadwatch logo seochat tools logo