How to Join Two Tables MySQL

How to join two tables mysql?

SELECT * FROM table1 LEFT JOIN table2 on table1.id = table2.id

How I join two tables with mysql?

maybe use a query like below

select s.*,g.grade from students s 
left join grades g on s.marks between min_mark and max_mark

How do I join two tables by string?

You can use group_concat with a group by on sc.size_id

select s.name,sc.size_id,group_concat(sz.name)
from subcategories s 
join categories c on c.id = s.categories_id 
join size_categories sc on sc.categories_id = c.id 
join sizes sz on (find_in_set(sz.id,sc.size_id)>0)
group by s.name,sc.size_id

If you still get duplicate size names use distinct inside group_concat

You should normalize your schema DO NOT store relations as comma separated values.

Join Two Tables based on a third table's data

If you don't need any of the contents of the Discount table, use the exists() funtion to execute a sub query in the where clause. This will give you the fastest results.

SELECT * 
FROM Product as a
   left join Price as b on a.Ref# = b.Ref#
WHERE EXISTS (
      SELECT *
      FROM Discount as c
      WHERE c.ProductID = a.ProductID
   )

If however you do need one or more of the columns of Discount, do an inner join between Product and Discount, joining them on the ProductID. This will result in only the products that have discount, and then do another left join to Price to get the columns from Price into the resultset too. Do be aware though that in case multiple rows exist in Discount for the one Product row, this will result in the same product shown on multiple rows.

SELECT * 
FROM Product as a
   inner join Discount as c on c.ProductID = a.ProductID
   left join Price as b on a.Ref# = b.Ref#

how to join two table with where to one column

If you need a result as a single column you could use concat

SELECT concat(p.id, p.status, p.date, m.pid) 
FROM posts p
INNER JOIN post_meta m ON m.pid=p.id 
WHERE (status=1 OR status=4) 
ORDER BY date DESC 

But you need explicit column (all you need) but not select *

Or could be you are looking for group_concat

SELECT group_concat(p.title)
FROM posts p
INNER JOIN post_meta m ON m.pid=p.id 
WHERE (status=1 OR status=4) 

How would I join two tables in MySQL (many to one) and get a specific JSON output (as shown below)

SELECT 
  JSON_PRETTY(
    JSON_OBJECT(
      'team_persons', JSON_ARRAYAGG(
        JSON_OBJECT(
          'team', team,
          'id_team', id_team,
          'persons', persons
        )
      )
    )
  ) AS _result
FROM (
  SELECT 
    t.id AS id_team,
    t.team_name AS team,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id_person', p.id, 
        'personName', p.person_name
      )
    ) AS persons
  FROM team t JOIN person p ON t.id = p.id_team
  GROUP BY id_team
) AS p;

Tested on MySQL 8.0.28, but it should also work on MySQL 5.7.22 (or later).

---

To include empty teams is tricky. At first one can just use LEFT OUTER JOIN, but then it complains unless I change the GROUP BY to reference the base column, not the alias:

  ...
  FROM team t LEFT OUTER JOIN person p ON t.id = p.id_team
  GROUP BY t.id

However, this probably doesn't result in what you want, which I assume is an empty JSON array. Because the result of an outer join does have a row, but with NULLs, you get one fake team member with NULLs:

{
  "team": "team3",
  "id_team": 3,
  "persons": [
    {
      "id_person": null,
      "personName": null
    }
  ]
}

One workaround is to UNION the current query with the JOIN with another exclusion-join query:

SELECT
  JSON_PRETTY(
    JSON_OBJECT(
      'team_persons', JSON_ARRAYAGG(
        JSON_OBJECT(
          'team', team,
          'id_team', id_team,
          'persons', persons
        )
      )
    )
  ) AS _result
FROM (
  SELECT 
    t.id AS id_team,
    t.team_name AS team,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id_person', p.id,
        'personName', p.person_name
      )
    ) AS persons
  FROM team t JOIN person p ON t.id = p.id_team
  GROUP BY t.id
  UNION
  SELECT 
    t.id,
    t.team_name,
    JSON_ARRAY()
  FROM team t LEFT OUTER JOIN person p ON t.id = p.id_team
  WHERE p.id_team IS NULL
) AS p;

The latter part of the UNION returns a literal empty array, because there are no people on the team anyway.

MYSQL: How to JOIN two tables on the same query referencing the same table twice

SELECT
  Table_1.*,
  g.Name,
  m.Name
FROM
  Table_1
  INNER JOIN Table_2 AS g ON Table_1.Group=g.ID
  INNER JOIN Table_2 AS m ON Table_1.Group=m.ID
WHERE
  Table_1.Group=2
  AND Table_1.Member=7

MySQL join two tables based on a condition

The conditions should be moved to the ON clause.

In MySql a CASE expression can be used to return the result of Boolean expressions (as 1 or 0 for true or false) like this:

SELECT u.name, s.swp_to, s.first_swp 
FROM swipes s LEFT JOIN users u
ON CASE (s.swp_from = :me AND ((s.first_swp NOT IN ('like', 'superlike')) OR (s.first_swp IN ('like', 'superlike') AND s.second_swp NOT IN ('like', 'superlike'))))
  WHEN 1 THEN u.id = s.swp_to
  WHEN 0 THEN u.id = s.swp_from AND (s.swp_to = :me AND s.second_swp <> 'pending')
END; 

---

Related Topics