CODEWITHSUNDEEP
sqlsqlite
user965972
user965972

Reputation: 2587

SQL get rows with similar data for multiple rows

I have a table with ID (PK), fixed (integer) and a lot of other columns (name,content,comment,...)

When I create a version of my data I want to check if new or updated rows have a duplicate row with a fixed value of 1 or higher. (To edit rows with fixed values, you need more privileges)

ID | Fixed | name
1  | 1     | "john"
2  | 0     | "john" //new row
3  | 0     | "jane" // updated row

After creating a version history this should become: 

ID | Fixed | name
1  | 2     | "john"
3  | 1     | "jane" 
4  | 0     | "john" //current row
5  | 0     | "jane" //current row

This query allows me to find the ID value of a "fixed" row for any of the new or updated rows (if I know their ID's beforehand).

select T.ID 
from MyTable T 
inner join (
      select name, 
      comments 
      from MyTable
      where ID = 2) E
on T.name = E.name 
and T.comments = E.comments 
where T.fixed = 1

This query works but I doubt it's the best solution.

I have the following questions/concerns:

1) I have to run this query for every new or updated row. Every time it will be comparing one row against all the other rows in the table. That isn't very efficient. Surely there is a better way?

2) can I search against all the ID's (2 and 3) in one query and return the matching ID's (or null if there isn't any)?

3) I should only compare the relevant data columns (e.g. name). Is there a way to select or inner join on all columns except some columns (ID, Fixed)?

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

First, you can rewrite your query just as a join:

select T.ID 
from MyTable T inner join
     MyTable e
     on T.name = E.name and T.comments = E.comments and e.id = 2
where T.fixed = 1;

For performance, you want an index on MyTable(fixed, name, comments, id).

If you want it to return a NULL value then use left outer join. For a list, just use in:

select T.ID 
from MyTable T left outer join
     MyTable e
     on T.name = E.name and T.comments = E.comments and e.id in (2, 3)
where T.fixed = 1;

Upvotes: 1

Related Questions