CODEWITHSUNDEEP
sqldatabasemysql
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

MySQL - Show All Permutations?

Table1:

Id   MyFK   f_name   l_name
===========================
1    100    John     Doe
2    100    Little   Timmy

Table2:

Id   MyFK   item     price
===========================
1    100    Car      200
2    100    Bike     100

In MySQL, how do I produce a table like this:

Id   MyFK   f_name   l_name  item  price
========================================
1    100    John     Doe     Car   200
1    100    John     Doe     Bike  100
2    100    Little   Timmy   Car   200
2    100    Little   Timmy   Bike  100

Upvotes: 2

Views: 2198

Answers (2)

Álvaro González
Álvaro González

Reputation: 146350

CREATE TABLE person
SELECT 1 AS Id, 100 AS MyFK, 'John' AS f_name, 'Doe' AS l_name UNION
SELECT 2, 100, 'Little', 'Timmy';

CREATE TABLE vehicle
SELECT 1 AS Id, 100 AS MyFK, 'Car' AS item, 200 AS price UNION
SELECT 2, 100, 'Bike', 100;

SELECT p.Id, p.MyFK, p.f_name, p.l_name, v.item, v.price
FROM person p
CROSS JOIN vehicle v;

Upvotes: 5

gabe3886
gabe3886

Reputation: 4265

Use a join to join the tables on a field which will give the result you are after. In this instance use the MyFK field on each table so you would use:

SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.MyFK = Table2.MyFK

That gives the result you are after.

Upvotes: -1

Related Questions