0

I created a set of three tables similar to the ones created in this answer How to store a list in a db column, with the difference being that my keys are each composed of a pair of columns instead of single columns.

Foo:

Chr1 Chr2
a a
a b
b a
b b

Fruits:

Type Name
Berry Cranberry
Berry Blueberry
Aggregate Blackberry
Berry Blackberry
Berry Lingonberry
Aggregate Raspberry
Berry Raspberry
Berry Boysenberry

FooFruits:

Chr1 Chr2 Type Name

Foo will be ~75 rows

Fruits will be ~600 rows, and has other columns that are not used here.

Eventually FooFruits will be very long because I will be assigning each Foo a large list of fruits.

All of these columns are nvarchar(50).

I made the composite keys because there are guaranteed to be duplicates of each [Chr1] and [Chr2], or each [Type] and [Name], but there can't be duplicates of the pair.

I am able to use the format mmxx gave (in a different order for my purpose)

INSERT FooFruits([Chr1], [Chr2], [Type], [Name])

SELECT [Chr1],[Chr2],'berry','cranberry' FROM Foo WHERE [Chr1] ='a' and [Chr2]='b'

to create a new row in my "FooFruits" table.

These databases will be accessed and modified with a Python program, most likely using QSqlTableModels in PyQt5.

The actual question

Is there some way to insert a row for everything where [Name] = '%nberry', so it would insert rows for 'cranberry', 'lingonberry', and 'boysenberry'? I could SELECT [Name] FROM Fruits WHERE [Name] = '%nberry' and then loop through each result and run individual inserts for them, but that seems very inefficient.

I also want to know what would happen to corresponding rows in "FooFruits" if I deleted one of the rows in "Foo" or "Fruits".

Del
  • 1,529
  • 1
  • 9
  • 18
UnsweetIceTea
  • 23
  • 4
  • What DB are you using? How are you joining the tables together? You can use `LIKE` for what you are referencing. Also, since FooFruits is a table and not a view, unless you put a Foreign Key on it, nothing will happen if you delete rows from Foo or Fruits. – Del Aug 16 '21 at 15:15
  • I am using a Microsoft SQL Server, and I have foreign keys connecting the tables so that nothing can be added to FooFruits that is not either a proper Fruit or Foo. – UnsweetIceTea Aug 16 '21 at 15:36
  • Update: I reformatted my query and it was able to work with `Like`, which I had tried earlier and it hadn't worked. Probably made some other error. Thanks for making me try that again. – UnsweetIceTea Aug 16 '21 at 15:42

1 Answers1

0

This was solved. I had tried using Like earlier, but clearly had made some other error at the same time.

This

INSERT FooFruits([Chr1], [Chr2], [Type], [Name])

SELECT 'a','b','berry',[Name] FROM Fruits WHERE [Name] LIKE '%nberry'

worked. Thanks, Del.

UnsweetIceTea
  • 23
  • 4