Field involving multiple rows from another table

Salvador

I have created/am creating 3 tables: Slaves, Citizens and Incidents.

How should I go about Incident involving multiple Citizens and Slaves? Now I'm thinking about making two fields in Incidents containing list of CitizenID's and SlaveID's (SlaveID1, SlaveID2...,SlaveIDn), but it seems plain dumb.

Tim Biegeleisen

Actually your idea doesn't sound dumb at all. You can design your Incidents table like this:

+------------+-----------+---------+
| IncidentID | CitizenID | SlaveID |
+------------+-----------+---------+
|     1      |     A     |    A    |      <-- incident #1 involved 2 citizens and 1 slave
|     1      |     B     |    A    |
|     2      |     A     |    A    |      <-- incident #2 involved 2 citizens and 2 slaves
|     2      |     B     |    A    |
|     2      |     A     |    B    |
|     2      |     A     |    B    |
+------------+-----------+---------+

Now when you query for a certain incident ID you can obtain a list of all citizens and slaves involved in the incident. This is a many-to-many relationship in your database schema.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Deleting multiple rows from a table

From Dev

JQuery: How to drag multiple rows from one table to another?

From Dev

How to insert multiple rows from a table to another table based on date condition (PHP-MySQL-Query)?

From Dev

Selecting rows using multiple LIKE conditions from a table field

From Dev

MySQL - Select multiple rows from one table whose IDs are stored in another table

From Dev

select multiple column from one table and insert into another as rows

From Dev

Insert multiple rows from select into another table

From Dev

Update multiple rows in one table with differing values from another

From Dev

Insert multiple rows with single a query from one table into another in Oracle

From Dev

MYSQL - UPDATE multiple rows from another table

From Dev

How to extract multiple rows from a table based on values from multiple columns from another table and then concatenate in SQL?

From Dev

Deleting multiple rows from a table

From Dev

JQuery: How to drag multiple rows from one table to another?

From Dev

SQL insert multiple rows from single field in another table

From Dev

Fetching Multiple Rows from Table and Adding Them into Another Table and then Remove from Previous Table, Using Checkboxes

From Dev

looping mysql query to get multiple rows of data from a table and insert into another table

From Dev

select field from table in another field from another table

From Dev

Insert (multiple) new rows into a table from another table using a subquery?

From Dev

MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

From Dev

Remove a substring in a varchar field from multiple rows of a table

From Dev

insert multiple rows mysql from another table

From Dev

INSERT rows multiple times based on a column value from another table

From Dev

FileMaker - Getting Data From Another Table with Multiple Field Restrictions

From Dev

Adding multiple rows of a table to another table

From Dev

select multiple columns from another table where field contains array

From Dev

Copy rows from table to another

From Dev

Concatenate field from multiple rows into single field

From Dev

Laravel select random rows from table based on another field

From Dev

Update MYSQL Table from multiple rows in another table

Related Related

  1. 1

    Deleting multiple rows from a table

  2. 2

    JQuery: How to drag multiple rows from one table to another?

  3. 3

    How to insert multiple rows from a table to another table based on date condition (PHP-MySQL-Query)?

  4. 4

    Selecting rows using multiple LIKE conditions from a table field

  5. 5

    MySQL - Select multiple rows from one table whose IDs are stored in another table

  6. 6

    select multiple column from one table and insert into another as rows

  7. 7

    Insert multiple rows from select into another table

  8. 8

    Update multiple rows in one table with differing values from another

  9. 9

    Insert multiple rows with single a query from one table into another in Oracle

  10. 10

    MYSQL - UPDATE multiple rows from another table

  11. 11

    How to extract multiple rows from a table based on values from multiple columns from another table and then concatenate in SQL?

  12. 12

    Deleting multiple rows from a table

  13. 13

    JQuery: How to drag multiple rows from one table to another?

  14. 14

    SQL insert multiple rows from single field in another table

  15. 15

    Fetching Multiple Rows from Table and Adding Them into Another Table and then Remove from Previous Table, Using Checkboxes

  16. 16

    looping mysql query to get multiple rows of data from a table and insert into another table

  17. 17

    select field from table in another field from another table

  18. 18

    Insert (multiple) new rows into a table from another table using a subquery?

  19. 19

    MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

  20. 20

    Remove a substring in a varchar field from multiple rows of a table

  21. 21

    insert multiple rows mysql from another table

  22. 22

    INSERT rows multiple times based on a column value from another table

  23. 23

    FileMaker - Getting Data From Another Table with Multiple Field Restrictions

  24. 24

    Adding multiple rows of a table to another table

  25. 25

    select multiple columns from another table where field contains array

  26. 26

    Copy rows from table to another

  27. 27

    Concatenate field from multiple rows into single field

  28. 28

    Laravel select random rows from table based on another field

  29. 29

    Update MYSQL Table from multiple rows in another table

HotTag

Archive