Join Two Tables Based on Relationship Defined in Third Table

Join two tables based on relationship defined in third table

This should do the trick

SELECT Activity.ActivityText as Activity, Action.ActionText as ApplicableAction
FROM ActivityAction
    INNER JOIN Activity
        ON ActivityAction.ActivityId = Activity.ActivityId
    INNER JOIN Action 
        ON ActivityAction.ActionId = Action.ActionId

You should read up on JOINS in databases. Here is a good starting point:

http://en.wikipedia.org/wiki/Join_%28SQL%29

Basically what we have here is a many to many relationship between Activity and Action which is resolved by two one-to-many relationships using the a join table called ActivityAction.

To get the required data back, we are joining ActivityAction to each one of the tables using the appropriate PK and FK columns and then choosing the string columns in the SELECT

EF Core 6 & Blazor: Join two tables based on relationship defined in third table

from a in db.accounts
from ro in db.roles
from r in db.relations
where a.account_id == r.account_id && r.role_id == ro.role_id
select ...

MYSQL JOIN two tables based on a relation in third table and concat the data from the second table

SELECT AppName, GROUP_CONCAT(TagName) Tags
FROM AppTags  
NATURAL JOIN App
NATURAL JOIN Tags 
GROUP BY AppName;

Joining two different tables with a common third table on a common column

You could join the Status table twice to achieve this:

SELECT
           o.OrderNumber
         , li.LineItemNumber
         , orderStatus.StatusDesc    AS StatusDesc_Order
         , lineItemStatus.StatusDesc AS StatusDesc_LineItem
FROM       [LineItem] AS li
INNER JOIN [Status]   AS lineItemStatus ON li.StatusID = lineItemStatus.ID
INNER JOIN [Order]  AS o ON li.OrderNumber = o.OrderNumber
INNER JOIN [Status]   AS orderStatus ON o.StatusID = orderStatus.ID

I do suggest however you try and stay away from table names using reserved keywords like Order and Status, it also is good practice to explcitly add schema prefixes before the table names in the query (i.e. dbo.Status or another user defined schema).

joining two tables with a third table

Junction tables are the classic solution for modeling a many-to-many relationship in an RDBMS.

You do not need to join three tables to get cars for event1 - two tables is enough:

select *
from Cars c
join CarEvent e on e.CarId = c.CarId
where e.EventId = 1

If you need the event name, you need to join the event table as well:

select *
from Cars c
join CarEvent ce on ce.CarId = c.CarId
join Event e on e.EventId = ce.EventId
where e.EventName = 'Event1'

Connecting two Tables using a third Table (SQL)

You can try this:

SELECT       first.Name1,
             second.Name2
FROM         firstTable first
LEFT JOIN    secondTable second ON first.Code1 = second.code1
INNER JOIN   thirdTable third ON second.code2 = third.code2

You can see this here->http://sqlfiddle.com/#!3/17883/2

Hope this helps!!!

Joining two tables with common fields by using a third table with same primary key

I learned something on this one. In your example, I don't see how table t1 relates to desired results. The desired results seem to be only a combination of t2 and t3. I got the kernel of the solution from this Answer which uses a NATURAL JOIN.

First create the tables:

BEGIN;
CREATE TABLE t1 (
    PK1     text,
    field_1 text,
    field_2 text,
    field_3 text,
    field_4 text,
    field_5 text
);

INSERT INTO t1 VALUES
('1', '1-1', '1-2', '1-3', '1-4', '1-5'), 
('2', '2-1', '2-2', '2-3', '2-4', '2-5'),
('3', '3-1', '3-2', '3-3', '3-4', '3-5'), 
('4', '4-1', '4-2', '4-3', '4-4', '4-5'),
('5', '5-1', '5-2', '5-3', '5-4', '5-5');

CREATE TABLE t2 (
       PK1       text,
    field_1ab text,
    field_2ab text,
    field_3ab text,
    field_4ab text,
    field_5a  text
);

INSERT INTO t2 VALUES
('1',  '1-1a',  '1-2a',  '1-3a',  '1-4a',  '1-5a'), 
('2',  '2-1a',  '2-2a',  '2-3a',  '2-4a',  '2-5a'),
('3',  '3-1a',  '3-2a',  '3-3a',  '3-4a',  '3-5a'), 
('4',  '4-1a',  '4-2a',  '4-3a',  '4-4a',  '4-5a'),
('5',  '5-1a',  '5-2a',  '5-3a',  '5-4a',  '5-5a');

CREATE TABLE t3 (
    PK1       text,
    field_1ab text,
    field_2ab text,
    field_3ab text,
    field_4ab text,
    field_5b  text,
    field_6b  text
);

INSERT INTO t3 VALUES
('6', '1-1b', '1-2b', '1-3b', '1-4b', '1-5b', '1-6b'), 
('7', '2-1b', '2-2b', '2-3b', '2-4b', '2-5b', '2-6b'),
('8', '3-1b', '3-2b', '3-3b', '3-4b', '3-5b', '3-6b'), 
('9', '4-1b', '4-2b', '4-3b', '4-4b', '4-5b', '4-6b'),
('10', '5-1b', '5-2b', '5-3b', '5-4b', '5-5b', '5-6b');

COMMIT;

And now for the query. It seems that if you do a NATURAL FULL OUTER JOIN, PostgreSQL will try to combine as many columns as possible.

  SELECT *
    FROM (t2 NATURAL FULL OUTER JOIN t3)
ORDER BY pk1::int

which gives

select id, s.name, m.Marks1, e.Marks2 
  from maths m 
    full join english e using (id)
    join student s using (id);

select s.id, s.name, m.Marks1, e.Marks2 
  from student s
    left join english e on e.id=s.id
    left join maths m on m.id = s.id
  where e.id is not null or m.id is not null

SELECT t.id AS ID
, a.id AS ActivityID
, t.ProjectType AS ProjectType
, t.Tier1Mission AS Mission
, m.id ASMissionID
, m.name AS MissionName
, t.Tier2Activity AS Activity
, a.name AS ActivityName
, t.Tier3Project AS Project
FROM 
  tActivity a
  INNER JOIN 
   (SELECT id, name FROM tMission) m
     ON a.missionId = m.id
  LEFT OUTER JOIN
   (SELECT *
     FROM tTaxonomy
   ) t ON t.Tier1Mission = m.name AND t.Tier2Activity = a.name

SELECT t.id AS ID
, a.id AS ActivityID
, t.ProjectType AS ProjectType
, t.Tier1Mission AS Mission
, m.id ASMissionID
, m.name AS MissionName
, t.Tier2Activity AS Activity
, a.name AS ActivityName
, t.Tier3Project AS Project
FROM
  tActivity a
  INNER JOIN tMission m ON a.missionId = m.id
  LEFT JOIN tTaxonomy t ON t.Tier1Mission = m.name AND t.Tier2Activity = a.name

UPDATE
  table3 t
  LEFT JOIN (
    select     x.`1ID`, y.`2ID`
    from       table1 x
    inner join table2 y on x.orderid = y.orderid
  ) foo ON t.table2id = foo.`2ID`
SET
  t.table1id = foo.`1ID`
WHERE
  t.table1id IS NULL