CODEWITHSUNDEEP
sqlmysqljoin
Matchu
Matchu

Reputation: 85832

MySQL query involving cross join, uniqueness, etc

Thankfully, I haven't had to work with particularly complex SQL queries before. Here's my goal.

I have the table hams, which I would like to cross-join with the table eggs - that is, get all ham-egg combinations... to an extent.

The eggs table also has an attribute how_cooked, which is defined as ENUM('over-easy','scrambled','poached'). I would like a resultset listing every possible combination of ham and egg-cooking method, along with a sample egg cooked that way. (I don't care which egg in particular.)

So if 3 hams with id of 1, 2, and 3, and 3 eggs of each cooking method, my resultset should look something like this:

+---------+-----------------+---------+
| hams.id | eggs.how_cooked | eggs.id |
+---------+-----------------+---------+
| 1       | over-easy       | 1       |
| 1       | scrambled       | 4       |
| 1       | poached         | 7       |
| 2       | over-easy       | 1       |
| 2       | scrambled       | 4       |
| 2       | poached         | 7       |
| 3       | over-easy       | 1       |
| 3       | scrambled       | 4       |
| 3       | poached         | 7       |
+---------+-----------------+---------+

I'm sure I could hack together some solution with loads of subqueries here and there, but is there any elegant way to do this is MySQL?

Upvotes: 0

Views: 292

Answers (2)

Damon
Damon

Reputation: 687

SELECT hams.id, eggs.how_cooked, eggs.id
FROM hams
  CROSS JOIN eggs

This does the trick. CROSS JOIN is synonymous with , but has a higher precedence in MySQL .

MySQL 5.0 Reference - JOIN syntax

Upvotes: 1

Matchu
Matchu

Reputation: 85832

Through a bit of thinking real hard and Googling, I may have found a good solution:

SELECT * FROM hams, eggs GROUP BY hams.id, eggs.how_cooked

It seems to work. Is it really that easy?

Upvotes: 2

Related Questions