September 22, 2007 at 2:48 pm
I need to get the query which should display me the marina_id,boat_registration,customer_id,first_name,last_name and address as a output. The output should have only duplicate records. Customer_id, first_name, last_name and address are in customer table.
But marina_id, boat_registration, customer_id are in other tables (say 3 tables) . Here I have joined with 1.customer and inventory_assignment table
2.customer and recurring_item table
select ia.marina_id,ia.boat_id,a.customer_id,a.name2,a.name2,a.address1
from
(
select c.customer_id, c.name1,c. name2,c.address1
from customer1 c ) as a
left outer join inventory_assignment ia
on a.customer_id = ia.customer_id
inner join boat b
on ia.boat_id =b.boat_id
select ri.marina_id,ri.boat_id,a.customer_id,a.name2,a.name2,a.address1
from
(
select c.customer_id, c.name1,c. name2,c.address1
from customer1 c ) as a
left outer join recurring_item ri
on a.customer_id = ri.customer_id
inner join boat b
on ri.boat_id =b.boat_id
I can get the duplicate customers from customer table. But I tried to join with other table the output
has more number of duplicates showing much more duplicates because of other table.
Problem1: Can anyone give me solution so that i get all duplicate records having marina_id, boat_registration,customer_id, first_name,last_name and address showing only the duplicates from customer table and not from other table.
problem2: some customers in customer table can be in inventory_assignment and some customers in customer table can be in recurring_item. Do you know how I can get one output so that i do not
miss marina_id and boat_registration.
September 26, 2007 at 9:56 am
jehen joshi,
Please provide the DDL for the table structure. Please define "What is a duplicate customer."
Example of what I mean:[/u]
Customer 1 matches Customer 2, because the Name and Address are the same.
This will help us answer your question.
Regards,
MCTS
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply