Diplay duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Diplay duplicate records

Author

Topic

jehenjoshi
Starting Member

1 Post

Posted - 2007-09-22 : 16:59:13

I am having big trouble with this query and if anyone could solve it, i would be very thankful

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.addres

s1
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.addres

s1
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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 03:36:04

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89841

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources