How to not retrieve duplicate row due to stored procedure

I’ve been using my stored procedure to retrieve events filter by : a date start, a date end, a shopId, a sellerId and customerId.

First of all, here the principal structure of my database which causes me the problem :

Ticket:

Id	Reference	..	..
15222	BOOKING56654	..	..

Seller :

Id	Name	..	..
41	Caty	..	..
47	Stephane	..	..

TicketSeller :

SellerId	TicketId
47	15222
41	15222

It works good, when I save my ticket with multiple seller, it saves in the many to many relation TicketSeller, two rows. That’s normal.

But when I execute my stored procedure, instead of retrieving only a row, it retrieves me two rows because of this relation many to many in TicketSeller. The problem might be when I join the tables but I didn’t success of fix it.

Here the code in my stored procedure when I do the join between those tables :

(...)
    FROM Ticket t 
    JOIN TicketSeller ts ON ts.TicketId = t.Id
    JOIN Seller e ON e.Id = ts.SellerId
    JOIN Customer c ON c.Id = t.CustomerId
    JOIN TicketProduct tp ON tp.TicketId = t.Id
    JOIN Product p ON p.Id = tp.ProductId
    LEFT JOIN Category ca ON ca.Id = p.CategoryId
    WHERE t.LicenseId = @licenseId AND t.[State] > 0 AND t.Type = 2 AND
    t.BookingAt >= @dateStart AND t.BookingEnd <= @dateEnd AND 
    ((@shopId is not null AND t.ShopId = @shopId) OR (@shopId = 0 AND t.ShopId > 0)) AND
    ((@sellerId > 0 AND t.SellerId = @sellerId) OR (@sellerId = 0 AND t.SellerId > 0)) AND
    ((@customerId > 0 AND t.CustomerId = @customerId) OR (@customerId = 0 AND t.CustomerId > 0))

​x
 
(...)    FROM Ticket t     JOIN TicketSeller ts ON ts.TicketId = t.Id    JOIN Seller e ON e.Id = ts.SellerId    JOIN Customer c ON c.Id = t.CustomerId    JOIN TicketProduct tp ON tp.TicketId = t.Id    JOIN Product p ON p.Id = tp.ProductId    LEFT JOIN Category ca ON ca.Id = p.CategoryId    WHERE t.LicenseId = @licenseId AND t.[State] > 0 AND t.Type = 2 AND    t.BookingAt >= @dateStart AND t.BookingEnd <= @dateEnd AND     ((@shopId is not null AND t.ShopId = @shopId) OR (@shopId = 0 AND t.ShopId > 0)) AND    ((@sellerId > 0 AND t.SellerId = @sellerId) OR (@sellerId = 0 AND t.SellerId > 0)) AND    ((@customerId > 0 AND t.CustomerId = @customerId) OR (@customerId = 0 AND t.CustomerId > 0))​

Answer

Based on your code example the Ticket table is aliased as ‘t’ and the TicketSeller is aliased as ‘ts’

FROM Ticket t 
    JOIN TicketSeller ts ON ts.TicketId = t.Id

 
FROM Ticket t     JOIN TicketSeller ts ON ts.TicketId = t.Id​

and in your description of your tables, the SellerId is in the TicketSeller table

 ((@sellerId > 0 AND t.SellerId = @sellerId) OR (@sellerId = 0 AND t.SellerId > 0))

 
 ((@sellerId > 0 AND t.SellerId = @sellerId) OR (@sellerId = 0 AND t.SellerId > 0)) ​

You are using t.SellerId and not ts.SellerId

If this is the correct code (and not just a spelling mistake), then I think this is why you have double records.

Advertisement

Answer