How to Do an Inner Join on Multiple Columns

How to do an INNER JOIN on multiple columns

You can JOIN with the same table more than once by giving the joined tables an alias, as in the following example:

SELECT 
    airline, flt_no, fairport, tairport, depart, arrive, fare
FROM 
    flights
INNER JOIN 
    airports from_port ON (from_port.code = flights.fairport)
INNER JOIN
    airports to_port ON (to_port.code = flights.tairport)
WHERE 
    from_port.code = '?' OR to_port.code = '?' OR airports.city='?'

Note that the to_port and from_port are aliases for the first and second copies of the airports table.

How to join two tables by multiple columns in SQL?

Yes: You can use Inner Join to join on multiple columns.

SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E
INNER JOIN Value V
ON E.CaseNum = V.CaseNum AND
    E.FileNum = V.FileNum AND 
    E.ActivityNum = V.ActivityNum

Create table

CREATE TABLE MyNewTab(CaseNum int, FileNum int,
    ActivityNum int, Grade int, Score varchar(100))

Insert values

INSERT INTO MyNewTab Values(CaseNum, FileNum, ActivityNum, Grade, Score)
SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E
INNER JOIN Value V
ON E.CaseNum = V.CaseNum AND
    E.FileNum = V.FileNum AND 
    E.ActivityNum = V.ActivityNum

Grouping by multiple columns in MYSQL across multiple tables (INNER JOIN)

I found the answer myself:

SELECT  o.orderid, s.suppliername, COUNT(p.productname) AS numberOfProducts
FROM Orders o
JOIN OrderDetails od
ON o.orderid = od.orderid
JOIN Products p
ON p.productid = od.productid
JOIN Suppliers s 
ON s.supplierid = p.supplierid
GROUP BY o.orderid, s.suppliername
HAVING o.orderid = 10300;

The mainissue was that
ON o.shipperid = s.supplierid had to be
ON s.supplierid = p.supplierid

friendly users on SO helped me out on that :)

MySQL inner join with multiple columns

Consider this select statement only filtering by equality among email columns

SELECT t1.*, t2.* 
  FROM Person t1 
 INNER JOIN Person t2
 WHERE t1.Email=t2.Email 
 ORDER BY t1.Id, t2.Id;

returns (1,1), (1,3), (3,1), (3,3) for t1.id and t2.id values respectively for the mail anne@example.com, and only (2,2) for cat@example.com. Then If you consider the other filter AND t1.Id > t2.Id,

SELECT t1.*, t2.* 
  FROM Person t1 
 INNER JOIN Person t2
 WHERE t1.Email=t2.Email 
   AND t1.id > t2.id
 ORDER BY t1.Id, t2.Id;

then you'll only have one tuple (3,1) since t1.id > t2.id is satisfied only for this case of id tuples. If you convert SELECT t1.*, t2.* to DELETE t1 (of course remove ORDER BY part also), then obviously you'll delete id = 3 and left rows with id values 1 and 2, reversely if you replace SELECT t1.*, t2.* with DELETE t2, then you'll have rows with id values 2 and 3.

Demo

Efficient way to join multiple columns to the same column? - SQL

You might find it easier to use a correlated subquery for each username, especially where you have to implement many columns as it's easier to cut n paste!

Something like:

select 
 (select Username from Mapping m where m.UserId = t.UserId1) Username1,
 (select Username from Mapping m where m.UserId = t.UserId2) Username2,
 (select Username from Mapping m where m.UserId = t.UserId3) Username3 etc
from InputTable t

UPDATE with a one-to-many JOIN (multiple columns)

The problem here is your assumption: "In my case though, I'm updating different columns, so overriding a previous update is not a concern of mine." It most certainly is a problem, because ultimately, your code

value1 = CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END,
value2 = CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END

only runs (or takes effect) once per row. SQL Server is not required to update the same row twice, and will normally just arbitrarily take a single row's values, one of which is NULL.

The documentation states (my bold):

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic. For example, in the UPDATE statement in the following script, both rows in Table1 meet the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.

Only one row may be used and one update done, you cannot assume that the updates will happen sequentially. So you need to make sure you have a single match for each row you want to update.

Therefore you should pre-aggregate your values

UPDATE O SET 
    value1 = T.value1,
    value2 = T.value2
FROM
    #original O
INNER JOIN (
    SELECT
      id,
      MAX(CASE WHEN T.name = 'value1' THEN T.value END) value1,
      MAX(CASE WHEN T.name = 'value2' THEN T.value END) value2,
    FROM #temp T
    GROUP BY
      id
) T ON T.id = O.id;

You could also use CROSS APPLY or a CTE for this.

SQL Inner Join With Multiple Columns

1. The logic of the DB structuring - am I doing it correctly?

• This is denormalized data. To normalize it, you would restructure your database into three tables:
  
  
  • Pizza
  • PizzaIngredients
  • Ingredients

Pizza would have ID, name, and type where ID is the primary key.

PizzaIngredients would have PizzaId and IngredientId (this is a many-many table where the primary key is a composite key of PizzaId and IngredientID)

Ingredients has ID and name where ID is the primary key.

2. List all the names of all the ingredients of each dish alongside each dish's name. Something like this in MySQL (untested):

SELECT p.ID, p.name, GROUP_CONCAT(i.name) AS ingredients
FROM pizza p
INNER JOIN pizzaingredients pi ON p.ID = pi.PizzaID
INNER JOIN ingredients i ON pi.IngredientID = i.ID
GROUP BY p.id

3. If you've encountered such a problem before - one that requires a single-to-many relationship - how did you solved it in a way different than this, using PHP & MySQL?

• Using a many-many relationship, since that what your example truly is. You have many pizzas which can have many ingredients. And many ingredients belong to many different pizzas.

---

Related Topics