-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|