Association Between Two Entries in SQL Table

Association between two entries in SQL table

Yes, this is right if you want to model a many-to-many relation. That is all persons can have many friends.

If you have a one-to-many relation, like all persons has one boss (or no boss) you don't need the extra table, then you only need a BossId column in the person table.

SQL query of relationship between two items from the same table

Assuming that columns of table foo: bar_one (fk->bar), bar_two (fk->bar), baz are non-null columns, you can use INNER JOIN to combine both tables, but if they are nullable, use LEFT JOIN instead.

SELECT  a.ID,
        b.name as bar_one,
        c.name AS bar_two,
        d.description
FROM    foo a
        INNER JOIN bar b
            ON a.bar_one = b.id
        INNER JOIN bar c
            ON a.bar_two = c.id
        INNER JOIN baz d
            ON a.baz = c.id

basically, INNER JOIN only displays the rows that a record has atleast one match on every table define, while LEFT JOIN displays all rows define on the left hand side whether there is match or none.

Is there a way to relationship between two table which table one has 3 unique keys and table two has 2 unique keys in SQL Server?

You can't do it this way. SQL Server requires a unique constraint (or primary key constraint) on the target of a foreign key - and you have duplicates in the source table.

For this to work, you would need to have a separate table that references all possible (CountryCode, Division) combinations. Actually, your whole schema should be normalized into something like:

-- "top" table that stores the countries
create table countries (
    country_id int primary key
    name varchar(100)
);

-- the table that stores the divisions
create table divisions (
    division_id int primary key,
    country_id int references countries(country_id),
    name varchar(100)
);

-- the table that stores the subdivisions
-- this corresponds to "table1" in your question
create table subdivisions (
    subdivision_id int primary key,
    division_id int references divisions(division_id),
    name varchar(100)
);

-- the table that stores the reports
-- this corresponds to "table2" in your question
create table reports (
    report_id int primary key,
    division_id references divisions(division_id),
    name varchar(100)
);

You can make the primary keys automatic by using identity columns (which is the SQL Server equivalent for MySQL's AUTO_INCREMENT).

As an example, here is how you would generate the current output that you are showing for the subdivisions:

select 
    sd.id,
    c.name country,
    d.name division,
    sd.name subdivision
from subdivisions sd
inner join divisions d on d.division_id = sd.division_id
inner join countries c on c.country_id = d.country_id

How to create a relationship between records in a SQL database?

Something like that :

SELECT
    A.Person1_ID, A.Compatibility, A.Person2_ID,
    B.Name AS Person1_name, B.Surname AS Person1_surname, B.Age AS Person1_age,
    C.Name AS Person2_name, C.Surname AS Person2_surname, C.Age AS Person2_age
FROM table_compatibility as A
LEFT JOIN table_people AS B
    ON A.person1_ID = B.ID
LEFT JOIN table_people AS C
    ON A.person2_ID = C.ID
WHERE A.Person1_ID = 1

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-one: Use a foreign key to the referenced table:

student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
                                                        # "link back" if you need

You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

Example queries:

 -- Getting all students for a class:

    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 

    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y

Can multiple relationship occur between two entities?

The answer to your direct question is "Yes". However, you cannot declare the model only using create table because:

• Declaring the foreign key address.person_id requires that the person table already exist.
• Declaring the foreign key person.mailing_address requires that the address table already exist.

Hence, to implement the model, you need to use alter table to add one or both of the constraints after both tables are created.

Is this the model you want? One feature of an address is that multiple people can have the same address. Your model does not allow that. To handle this, you would typically have three tables:

• Person
• Address
• PersonAddress

The third table has one row for each person/address pair. It can also have other information such as:

• Type ("mailing" versus other types)
• Effective and end dates.
• Perhaps other information.

If you want to guarantee uniqueness of the "mailing" address in such a model, many databases support filtered unique indexes, to ensure there are no duplicate mailing addresses.

---

Related Topics