Skip one row inside request using inner join (sql compact server 4.0)

Question

I am using SQL server compact 4.0 and VB.net and Visual studio 2019.

I am using this request to extract information from 4 tables : Table0, Table1, Table2 and Table3. This request is working fine before, but now I have one problem because one column in Table 3 (ID_C) is now duplicated, and this request needs table 3 without this duplication.

One solution for my problem is to create a new table (table 3 without duplication) and the problem will be solved. But I am thinking because I am beginner in SQL, maybe there is a solution to skip one row in Table3 inside the request. I have searched, but I didn't find a solution for this problem.

"SELECT Table0.M " +
 "FROM Table1 INNER JOIN " +
 "Table2 ON Table1.ID = Table2.ID_V INNER JOIN " +
 "LM ON Table2.ID_M = Table0.ID_M INNER JOIN " +
 "Table3 ON Table2.ID_C = Table3.ID_C " +
 "WHERE (Table1.IDX_V = #IDX_V#) AND (Table3.ID_C = '#ID_C#')"

In the code #IDX_V# and #ID_C# are replaced by some values.

• anon: While this doesn't impact the answer to your question, I wanted to note that SQL Server Compact was deprecated almost 9 years ago, in February 2013, and is now fully unsupported, as even extended support ended earlier this year.
• mustaccio: How would you decide what row to "skip" in Table3? Add that as the query criteria.
• all.west: @mustaccio A row is added twice so I can choose even rows or odd rows

Answer 1

You can use an EXISTS subquery

"SELECT Table0.M
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID_V
INNER JOIN LM ON Table2.ID_M = Table0.ID_M
WHERE Table1.IDX_V = @IDX_V
 AND EXISTS (SELECT 1
 FROM Table3
 WHERE Table2.ID_C = Table3.ID_C
 AND Table3.ID_C = @ID_C);"

Note the use of proper parameters, rather than injection. And the use of a multiline string

Answer 2

If the row is 100% duplicate, you could try to use distinct . This will give you unique results. This would not be "skipping" a row but would only show 1 row for any duplicates.

"SELECT DISTINCT Table0.M " +
 "FROM Table1 INNER JOIN " +
 "Table2 ON Table1.ID = Table2.ID_V INNER JOIN " +
 "LM ON Table2.ID_M = Table0.ID_M INNER JOIN " +
 "Table3 ON Table2.ID_C = Table3.ID_C " +
 "WHERE (Table1.IDX_V = #IDX_V#) AND (Table3.ID_C = '#ID_C#')"

• all.west: I will test, but the problem is only one column (ID_C) is duplicated in Table3, the other columns in Table3 are not duplicated
• Bob Klimes: you are only returning one column in your example. if you provide table creation scripts and sample data, you can get a better answer