CODEWITHSUNDEEP
mysql
frgtv10
frgtv10

Reputation: 5450

How do I combine two tables?

I have the following database structure:

`Products`
-------------------------------------
ID | ProductNo
-------------------------------------
1 | 3340
2 | 3450


`ProductVariants`
-------------------------------------
ID | MasterID (ref to `Products`.`ID`) | ProductNo
-------------------------------------
1 | 1 | 3341
2 | 1 | 3342
3 | 2 | 3451

How do I get the Products and their Product Variants with one query?

EDIT:

Desired result:

MasterID | IsMaster | ProductNo | VariantProductNo
1 | 1 | 3340 | null
1 | 0 | 3340 | 3341
1 | 0 | 3340 | 3342
2 | 1 | 3450 | null
2 | 0 | 3450 | 3451

Upvotes: 0

Views: 58

Answers (2)

Kickstart
Kickstart

Reputation: 21513

This would do it

SELECT *
FROM Products
INNER JOIN ProductVariants
ON Products.ID = ProductVariants.MasterID

Normally done with a single query for efficiency (you could have a query on the Products table, loop round the results of that and for each one do a query on the ProductVariants table, but it would be dog slow).

You could use a LEFT OUTER JOIN instead if you wanted products for which there were no variants

To go with your updated requirements, something like this (not tested so excuse any typos)

SELECT Products.MasterID, 1 AS IsMaster, Products.ProductNo, NULL AS VariantProductNo
FROM Products
UNION
SELECT Products.MasterID, 0 AS IsMaster, Products.ProductNo, ProductVariants.VariantProductNo
FROM Products
INNER JOIN ProductVariants
ON Products.ID = ProductVariants.MasterID
ORDER BY MasterID, IsMaster DESC, ProductNo

Upvotes: 1

Taryn
Taryn

Reputation: 247690

You will want to join the two tables on their Id = MasterId:

select *
from products p
left join ProductVariants v
  on p.id = v.Masterid

See SQL Fiddle with Demo

A LEFT JOIN will return all products even if they do not have a corresponding record in the ProductVariants table.

An INNER JOIN will return the records that appear in both table.

Here is a visual explanation of joins that is helpful in learning join syntax.

If you have more tables to join, then you would use:

select *
from products p
left join ProductVariants v
  on p.id = v.Masterid
left join MasterArtikel m
  on v.MasterId = m.MasterId  -- you place the columns that join the tables here

Upvotes: 3

Related Questions