Need help for query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Need help for query

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    Need help for query

    I need to get the supplier name and their supplier contact if they have the duplicate email address.
    The following are the three tables: supplier, username and contact

    SUPPLIER USERNAME CONTACT
    -------- --------- -------------
    supplier_id username_id contact_id
    name supplier_id first_name
    contact_contact_id last_name
    email

    I have the following query to get the duplicate supplier_id and email address but how can I modify the query to include the contact.first_name and contact.last_name.

    select s.supplier_id , c.email from supplier s, username u, contact c
    where s.supplier_id = u.supplier_id and
    u.contact_contact_id = c.contact_id
    group by s.supplier_id, c.email
    having count(*) >1

  2. #2
    Join Date
    Dec 2002
    Posts
    36
    select supplier_id
    ,t1.contact_id
    ,t1.first_name
    ,t1.last_name
    from username
    ,(select distinct contact_id, first_name, last_name
    from contact
    where contact_id in (select distinct contact_id
    from contact
    group by email
    having count(*) > 1)) t1
    where t1.contact_id=username.contact_id;
    fossil

Posting Permissions

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



Click Here to Expand Forum to Full Width