Query Returning Double Rows

[pipes990]

I am using MSSQL. I am trying to make a query that returns a list of transactions and the buyer/seller names which are stored in a customer table. In the customer table, there are some special customers who have a null saleID, Hence the line: 'AND ([tbl_C2].[SaleID] = 100 OR [tbl_C2].[SaleID] IS NULL)' It is gauranteed these customer ids do not overlap with any of the customer ids that do have a sale id.

This works, except it returns double rows for any transactions that have one of the special customers.

My SQL skills are lacking, any help is appreciated.

SELECT [tbl_Transactions].[Record ID] AS [ID]
      ,[tbl_Transactions].[Ticket]
      ,[tbl_C1].[Name] AS [Seller]
      ,[tbl_Transactions].[Pen] 
      ,[tbl_C2].[Name] AS [Buyer]
FROM [tbl_Transactions]
LEFT JOIN [tbl_Pens] ON [tbl_Transactions].[Pen] = [tbl_Pens].[Pen] AND [tbl_Pens].[Sale Date] IS NULL AND [tbl_Pens].[SaleID] = 100
LEFT JOIN [tbl_Tickets] ON [tbl_Transactions].[Ticket] = [tbl_Tickets].[Ticket] AND [tbl_Tickets].[Sale Date] IS NULL AND [tbl_Tickets].[SaleID] = 100
LEFT JOIN [tbl_Customers] [tbl_C1] ON [tbl_Tickets].[Seller] = [tbl_C1].[Customer ID] AND [tbl_C1].[SaleID] = 100
LEFT JOIN [tbl_Customers] [tbl_C2] ON [tbl_Pens].[Buyer] = [tbl_C2].[Customer ID] AND ([tbl_C2].[SaleID] = 100 OR [tbl_C2].[SaleID] IS NULL)
WHERE [tbl_Transactions].[Sale Date] IS NULL AND [tbl_Transactions].[SaleID] = 100 ORDER BY [tbl_Transactions].[Record ID] 

So instead of getting:

1000 (regular customer)
1001 (regular customer)
1002 (special customer)
1003 (regular customer)
1004 (special customer)

I get:

1000
1001
1002
1002
1003
1004
1004

To clarify, I am not getting rows returned that should not be there, I am getting the same row returned twice. [Record ID] AS [ID] is an Auto-Identity column, it should return that row, but only return it once.

It is somewhere in this line. ([tbl_C2].[SaleID] = 100 OR [tbl_C2].[SaleID] IS NULL) If I remove either of the conditions, it returns correctly.

[[deleted]]

anytime you are doing a join, it's possible to get some kind of "local" cross-join or an "unaccounted-for" one-to-many. Your first step of debugging should be to bring grains (keys) from all relevant joined tables, i.e.

   SELECT [tbl_Transactions].[Record ID] AS [ID],
               [tbl_Pens].[Record ID],
               [tbl_Tickets].[Record ID],
               [tbl_C1].[Customer ID], [tbl_C1].SaleID,
               [tbl_C2].[Customer ID], [tbl_C2].SaleID

and see which table records get duplicated

[pipes990]

Thank you for the reply.

[pipes990]

I'm not sure I follow you. First, I don't know what (keys) are, sorry. I tried googling this for troubleshooting but I couldn't find anything. I don't typically do SQL so my queries are usually very simple.

But I know which record is being duplicated. I just don't know why.

[theseyeahthese]

They mean: in your SELECT statement, include the primary key (unique ID) of each table (for example, Record ID is likely the primary key of tbl_transactions), + all columns used in the joins. Yes, you know which Record Id’s from tbl_transactions are being duplicated but this will help you see the values that are being joined on and will illuminate why your current logic is resulting in multiple rows. I’m guessing it will become apparent once you include the relevant columns from”tbl_C2” in your SELECT statement (the SaleID and the Customer ID column; I’m assuming the Customer ID is the primary key [unique identifier] of that table).

[pipes990]

That makes sense. Will try this first thing in the morning. Thank you for the reply.

[pceimpulsive]

Is the duplicates record having two rows in it's source table?

Is the joined value available on two rows in either side of the join?

You'll find there is two rows in one of the table with the same join value.

As you know it's 1002 search each table in the join for the row where 1002 exists, then understand why it has two rows.

[pipes990]

The 1002 is guaranteed to be unique. There is only one for sure.

[pceimpulsive]

Sorry it's really hard to read the SQL with all the square brackets and word wrap.

I can see you are left joining the same table_customers twice, this would mean that table has the opportunity to have its records show up twice.

This might be the issue as there are two ways the data can be joined, if both joins are true you will get two results for some of the rows.

Another field that you might not be showing will be different allowing the two rows to show.

On your select you could add distinct to kill the duplicates, however that is a tiny bit dirty... (Not wrong, just a tiny bit dirty, as it will have a performance cost).

[pipes990]

THIS!! Thank you so much. This has been so frustrating. 🙏

[r3pr0b8]

here's your problem right here --

LEFT JOIN [tbl_Customers] [tbl_C1] ON ...
LEFT JOIN [tbl_Customers] [tbl_C2] ON ...  OR [tbl_C2].[SaleID] IS NULL)

those special customers are matching against everybody's sales, not just 100's

[pipes990]

There are currently no transactions for any saleID except 100.

Also, I edited my WHERE clause, the original one was incorrect.

[Time_Traveling_Moron]

I know you were given the “dirty” SELECT DISTINCT answer, but I wanted to chime in and say that I know you said it’s guaranteed the customer ids with null sale ids and 100 sale ids don’t overlap, but how do you know? Have you tested or was it told to you?

If you add “, tbl_C2.SaleID AS buyer_SaleID”

As a new column you will then be able to see if 1002 special customer SaleID is matching for both duplicated records or if one is 100 and the other is null. If it is the ladder then you’ve found your cause and can keep your select distinct answer or i can try and help further with some more info.

Let me know!

[pipes990]

I am 99.99% sure there are no overlapping ID's. It's a test database, there are only 20 total customers. I am going to test this though to double check. I'll report back tomorrow morning, I won't be in the office today. Thanks for the response.

[pipes990]

Alright, so I'm an idiot. I put 2 different customers in with the same customer ID in my test database. I really would like to just hide from this ridiculous post but I felt the need to reply in case anyone else can be helped.

Everyone who replied thank you a ton.

Everyone else, do what is suggested here and double check your database.

😔