Sql Two Tables and Creating a Link Table

SQL two tables and creating a link table

Employee_list should have:

• employee_listid (INT PK)
• employee_id (INT FK)
• store_id (INT FK)

I would recommend changing the table name to represent the composite table, i.e.
EmployeeStores. This would allow your schema to be scalable, employees can work in multiple stores.

In SQL Server:

CREATE TABLE EmployeeStores
(
   EMPLOYEEStoreID   INT IDENTITY,
   EMPLOYEEID INT FOREIGN KEY REFERENCES Employee(employee_id),
   STOREID INT FOREIGN KEY REFERENCES Store(store_id)
)

database design: link table for multiple different tables

This appears to be similar trying to map polymorphism to the relational model - it's just a bad fit - there's no obvious right answer.

There are a few obvious solutions. The one you've identified (storing the helptext in a linked table) is neat, but requires lots of joins if you're retrieving all the books for an author belonging to a publisher etc. As the business logic says "all objects should have helptext" (and I assume each row has different helptext), it's not super logical to store this in a child table - "helptext" is an attribute of each object, not a related concept.

You could also add a helptext column to each table. That stores the attribute in the main table, and reduces the cognitive load (as well as the number of joins). This is logical if each author, book, etc. has their own helptext.

Link two tables in SQL

If you have a many to many relationship, then yes you need a 3rd table to create the relationship. This table will often only hold the ID's of the respective rows that that in the table, with both being a composite primary key (to avoid duplication). Something like this:

--Sample tables
CREATE TABLE dbo.Machine (MachineID int IDENTITY NOT NULL,
                          Label nvarchar(50));

ALTER TABLE dbo.Machine ADD CONSTRAINT PK_MachineID PRIMARY KEY CLUSTERED (MachineID);
GO
CREATE TABLE dbo.Part (PartID int IDENTITY NOT NULL,
                       Label nvarchar(50));

ALTER TABLE dbo.Part ADD CONSTRAINT PK_PartID PRIMARY KEY CLUSTERED (PartID);
GO
--Relationship table
CREATE TABLE dbo.MachinePart (MachineID int NOT NULL,
                              PartID int NOT NULL);
--Add Primary Key
ALTER TABLE dbo.MachinePart ADD CONSTRAINT PK_MachinePartID PRIMARY KEY CLUSTERED (MachineID,PartID);

--Add Foreign Keys
ALTER TABLE dbo.MachinePart ADD CONSTRAINT FK_MachinePart_MachineID FOREIGN KEY (MachineID) REFERENCES dbo.Machine(MachineID);
ALTER TABLE dbo.MachinePart ADD CONSTRAINT FK_MachinePart_PartID FOREIGN KEY (PartID) REFERENCES dbo.Part(PartID);
GO
--Clean up
DROP TABLE dbo.MachinePart
DROP TABLE dbo.Part;
DROP TABLE dbo.Machine;

How do I link two tables of the same name across two Azure SQL Server databases?

You can specify the remote schema and object name in CREATE EXTERNAL TABLE, eg

CREATE EXTERNAL TABLE ext.Customers
(   
  [CustomerID] int NOT NULL,
  [CustomerName] varchar(50) NOT NULL,
  [Company] varchar(50) NOT NULL
)
WITH
( 
  DATA_SOURCE = MyElasticDBQueryDataSrc,
  SCHEMA_NAME = N'dbo',  
  OBJECT_NAME = N'Customers'  
)

What is an Link Table?

Link tables are usually association/bridge tables between different Hub tables in Datavault. They mostly resolve many to many relation between different Hub tables.

Example

Link - INVOICE_LINE_ITEM

Hub - INVOICE, PRODUCT etc.

Here relation b/w INVOICE and PRODUCT are many to many.

(?) SQL - interlinked joins (how to build a chain between two tables?)

You can use a couple of CTEs to first UNION the tables together and then follow through the list starting from the firstPage, using a row number to guarantee the ordering of results:

WITH allpq AS (
  SELECT name, 'page' AS type, next, next_type
  FROM page
  UNION ALL 
  SELECT name, 'question', next, next_type
  FROM question
),
list AS (
  SELECT type, name, next, next_type, 1 as rn
  FROM allpq
  WHERE name = 'firstPage'
  UNION ALL
  SELECT a.type, a.name, a.next, a.next_type, list.rn + 1
  FROM allpq a
  JOIN list ON a.name = list.next AND a.type = list.next_type
)
SELECT type, name, next, next_type
FROM list
ORDER BY rn

Output:

type        name            next            next_type
page        firstPage       secondPage      page
page        secondPage      firstQuestion   question
question    firstQuestion   secondQuestion  question
question    secondQuestion  thirdPage       page
page        thirdPage       fourthPage      page
page        fourthPage      fourthQuestion  question
question    fourthQuestion  fifthPage       page
page        fifthPage       fifthQuestion   question
question    fifthQuestion   sixthPage       page
page        sixthPage       seventhPage     page
page        seventhPage     eighthPage      page
page        eighthPage      

Demo on dbfiddle

SQL - How to query multiple tables via a link ./ associative table

You need 2 left joins.

Here I created simplified schema

CREATE TABLE IF NOT EXISTS `main` ( `title` varchar(200));
CREATE TABLE IF NOT EXISTS `tags` ( id int(10), `Name` varchar(200));
CREATE TABLE IF NOT EXISTS `links` ( `title` varchar(200),  `tag` int(10));

INSERT INTO main VALUES ('Foo'),  ('Bar'),  ('Baz');
INSERT INTO tags VALUES (1, 'tag1'), (2, 'tag2'), (3, 'tag3'), (4, 'tag4');
INSERT INTO links VALUES ('Foo', 1), ('Foo', 2), ('Bar', 3),  ('Bar', 4),  ('Hello', 5), ('Foo', 6);

And this is SQL:

select ar.title, group_concat(t.name) as tags 
from main ar 
left join links lnk on ar.title=lnk.title 
left join tags t on lnk.tag = t.id
group by title

Output:

title    tags
-----    -----
Bar      tag3,tag4
Baz      (null)
Foo      tag1,tag2

http://sqlfiddle.com/#!9/a14ef4/1

SQL query with two linked tables

For Given Example:

For the example data where users sell the same drugs through all sales types:

SELECT mus.userId 
FROM User2SalesType qus 
INNER JOIN User2Drug qud       ON qus.UserId = qud.UserId
INNER JOIN User2SalesType mus  ON qus.SalesTypeId = mus.SalesTypeId
                               AND mus.UserId != qus.UserId
INNER JOIN User2Drug mud       ON qud.DrugId = mud.DrugId 
                               AND mus.UserId = mud.UserId
WHERE qus.UserId = ? # querying user ID

---

For More Flexible Data:

If you want to support a user selling drug A by sales X and drug B by sales Y (but not drug A by sales Y or drug B by sales X) you can use this data model:

User:       id, etc.
Response:   userId, salesTypeId, drugId
SalesType:  id, etc.
Drug:       id, etc.

Then you can query by self-joining Response:

SELECT mu.userId 
FROM response qu                       # querying user
INNER JOIN response mu                 # matching users
  ON qu.salesTypeId = mu.salesTypeId
  AND qu.drugId = mu.drugId
  AND qu.userId != mu.userId
WHERE qu.userId = ?                    # ? = querying user ID

---

Related Topics