SQL Server Performance Forum – Threads Archive

Duplicate Names

I have two tables. One with booking number,destination,departure date and the other with booking and passenger name I need to create a list of bookings where the passenger name appears on more than 1 booking for the same departure date and destination The output needs to show destination,departure date,passenger and booking numbers

---

Try the following… select destination,departuredate,min(passenger) ,min(bookingnumbers), count(*)
from Table1 a
join Table2 b on a.bookingnumbers = b.bookingnumbers
group by destination,departuredate
having count(*)>1
Mohammed U.

---

Your suggestion will not work since the information you requested from table a exists in 2 tables. I have table 1 with name and booking
I have table 2 with destination departure and booking How do I list name and booking where
name is duplicated in more than 1 booking for same destination and departure

---

–How abt this…
create table ##temp1
(
bookingid char(10),
destination char(20),
timeday char(10)
) create table ##temp2
(
bookingid char(10),
name char(10)
) insert into ##temp1 select ‘book1′,’dest1′,’date1’
insert into ##temp1 select ‘book1′,’dest1′,’date2’
insert into ##temp1 select ‘book2′,’dest2′,’date3’
insert into ##temp1 select ‘book5′,’dest2′,’date3’
insert into ##temp1 select ‘book4′,’dest2′,’date3’
insert into ##temp2 select ‘book5′,’name1’
insert into ##temp2 select ‘book1′,’name2’
insert into ##temp2 select ‘book2′,’name3’
insert into ##temp2 select ‘book4′,’name3’
insert into ##temp2 select ‘book5′,’name3’ select distinct tbl1.destination,tbl1.timeday,t.bookingid,t1.name
from ##temp1 t
join(select destination,timeday
from ##temp1 group by destination,timeday
having count(bookingid) > 1)tbl1 on t.destination = tbl1.destination
and t.timeday = tbl1.timeday
join ##temp2 t1 on t.bookingid = t1.bookingid
join (select name
from ##temp2
group by name
having count(bookingid) > 1)tbl on t1.name = tbl.name drop table ##temp1
drop table ##temp2

---

Use a correlated subquery in an EXISTS clause. This will mirror your main query, but you add a <> operator in the WHERE clause of the subquery to spot the differences. SELECT a1.name, a1.booking
FROM table1 a1
INNER JOIN table2 a2 ON a1.booking = a2.booking
WHERE EXISTS
(SELECT b1.*
FROM table1 b1
INNER JOIN table2 b2 ON a2.booking = b2.booking
WHERE a1.name = b1.name AND a1.booking <> b1.booking
AND b1.destination = b2.destination AND b1.departure = b2.departure)

---

thanks for the help

---

]]>