Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: same table inner join multiple conditions

    Hello!
    I am new to complex SQL queries and I really need whatever help I can get on this.
    I have one table in a database that i have read only access.
    It contains - record id, date, customer id, address, name, date of birth.
    I am trying to recreate the following logic:
    return all records that have either A OR B OR C
    A: all records from certrain date that have
    a. (Same address + same name) : more than 3 times AND
    b. Different DOB
    B: all records from same date that have
    a. (Same address + same name) : more than 3 times AND
    b. Different Customer ID (exclude all 0s)
    C: all records from same date that have
    a. Same address : more than 3 times AND
    b. Same customer ID (exclude all 0s) AND
    c. Different DOB


    I tried to write A and then use "UNION" to join A, B and C, but i cannot come up with a way to incorporate (same name + same address) more than 3 times.
    Here is my query:

    select '_'+ A.recordid, A.date, A.name, A.customerid, A.dob, A.address
    from table as A, table as B
    where A.name = B.name
    and A.address=B.address
    and A.DOB <> B.DOB
    and A.date like '07/18/%' and A.date like '%2010%'


    Also - this query seems to be really slow. Is there a faster way to achieve the same functionality?

  2. #2
    Join Date
    Jul 2010
    Posts
    5
    Also - I realized that my query does not contain inner join, but the title does.
    a colleague suggested that I use inner join for the query, but unfortunately I have no idea how to.
    Thanks so much for any help!

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Is this more along the lines of what you are after for your first condition? I changed your like statements around on the date as it really made no sense to me to be performing those two like statements. Also, I am not sure if the date should be applied in the subselect, but I threw in in there, suspecting that you will need to remove it. Please remember to use code tags(i.e; [ CODE ] and [ / CODE ] without the spaces) around any code/sql, so that it will come out formatted to make it easy to read for those of us reading your thread.
    Your query did contain an inner join. You happened to use the implicit syntax, rather than explicit, which is prefered.

    Code:
    select '_'+ A.recordid
          , A.date
          , A.name
          , A.customerid
          , A.dob
          , A.address
    from table as A
    WHERE A.date = '07/18/2010'
    and 3 <= (select count(*) from table as b
              where a.name    = b.name
                and a.address = b.address
                and a.dob    <> b.DOB
                and b.date    = '07/18/2010'
    Dave Nance

  4. #4
    Join Date
    Jul 2010
    Posts
    5
    Hi Dave,
    thank you for the reply - this is exactly what I was looking for! I compared it to the query that i came up yesterday (after hours of research) and it ran in only 6 mins as opposed to 20 mins.
    here is my query(the slower one) - i hope this helps others in terms of avoiding it:
    Code:
    select '_'+ A.recordid
          , A.date
          , A.name
          , A.customerid
          , A.dob
          , A.address
    from table as A inner join (select  A.name, A.address
                                        from table as A
                                        group by A.name, A.address
                                        having count(*)>3) as B
    on 
    A.name = B.A.name
    A.address = B.A.address
    A.dob <> B.A.dob
    where A.date = '07/18/2010'
    So, as I understand it this query would return those combinations of address, name and DOB where the address and the name are the same, DOB is different more than 3 times.

    Do you have any ideas how i can make it return all records for which there is such an address that fulfills this condition? i.e. if we have:
    record id address name dob
    1, 123 main st, john smith, march 30th
    2, 123 main st, john smith, march 29th
    3, 123 main st, john smith, march 28th
    4, 123 main st, john smith, march 27th
    5, 123 main st, john smith, march 30th

    it would return all 5 records insted of only the first 4?
    Last edited by ivelinayana; 07-21-10 at 12:39. Reason: wrong sql code by mistake

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    take date comparison out

  6. #6
    Join Date
    Jul 2010
    Posts
    5
    Yes, but then it would return situations like the following, which I would like to avoid:

    1, 123 main st, john smith, march 30th
    2, 123 main st, john smith, march 30th
    3, 123 main st, john smith, march 30th
    4, 123 main st, john smith, march 30th
    5, 123 main st, john smith, march 30th

    I would like to return all matches only if within those matches there is a DOB variation.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    thorw on a distinct and remove the date qualification in your where clause.

  8. #8
    Join Date
    Jul 2010
    Posts
    5
    This probably sounds ignorant and I apologise, but where should the distinct be?
    I tried in the select statement but it gives me errors...

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select distinct '_'+ A.recordid
          , A.date
          , A.name
          , A.customerid
          , A.dob
          , A.address
    from table as A
    WHERE 3 <= (select count(*) from table as b
                      where a.name    = b.name
                         and a.address = b.address
                         and a.dob    <> b.DOB)
    Or something like:
    Code:
    select '_'+ A.recordid
          , A.date
          , A.name
          , A.customerid
          , A.dob
          , A.address
    from table as A
    WHERE exists(select 1 from table c
                      where a.name    = c.name
                         and a.address = c.address
                         and a.dob    <> c.DOB
                         and 3 <= (select count(*) from table as b
                                       where c.name    = b.name
                                          and c.address = b.address
                                          and c.dob    <> b.DOB))
    The point is there are a multitude of ways to write the SQL. You just have to decide which fits the criteria you are after and gives you satisfactory performance.
    Dave Nance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •