CODEWITHSUNDEEP
sqlstored-procedures
harili
harili

Reputation: 477

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))

Upvotes: 0

Views: 46

Answers (1)

Ef.B.
Ef.B.

Reputation: 26

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

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))

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.

Upvotes: 1

Related Questions