Making arbitrary sets

[artacus]

This one is pretty advanced, but I'll give it a shot...

I need to join two "sets." So lets say that I have one set (1,2,3,4,5,6) and a second set ('A,B,C,D,E,F') and I want to join them so I should get 36 results '1:A', '1:B', '1:C' thru '6:F'. You can do something like so:
[code]
SELECT alpha.letter, numeric.num
FROM (
SELECT letter
FROM alphabet WHERE letter IN ('A','B','C','D','E')
) AS alpha
JOIN (
  SELECT num
  FROM numbers WHERE num BETWEEN 1 AND 6
) AS numeric ON 1 = 1
[/code]

Thats fine if you've got an alphabet and numbers table lying around that just happens to have the info that you need. But what about an arbitrary set?

I'm thinking there should be some way to use EXPORT_SET, FIND_IN_SET, MAKE_SET, IN(), EXPLODE... something!

[fenway]

I'm confused... by default, join will give you the cartesian product (MxN)... how is what you want different?

[artacus]

I want to be able to do that when I don't have the data in tables. I gave a simplistic example, what I need is more complicated than 'A,B,C'.
So I'm looking for something maybe like this:
[code]
SELECT *
FROM (
  SELECT EXPLODE_A_SET('MA70,MA80,MAI1,MAI2,RU1,RU2,MG,AG')
) AS M
JOIN (
    SELECT EXPLODE_A_SET('7,8,9,10,11,12')
) AS N ON 1=1
[/code]

Obviously EXPLODE_A_SET() isn't a real function. But mysql is very good at working with sets when it comes from your tables, so I'm thinking maybe it can do the same w/o the tables.

[fenway]

Oh, I see what you mean... let me ponder for a while.

[fenway]

I'm thinking of a solution where mutex tables are used... but I'm still thinking.

[artacus]

mutex? Thats a new one to me. And all I'm finding in the manual is mutex status and mutex locks. Is that similar to a temp table?

[shoz]

Although not an elegant solution, If this has to be done in MYSQL you should be able to use UNIONS. I'd imagine that the performance would be fine as well.

[code]
SELECT
*
FROM
    ((SELECT 'a'  AS l)
    UNION
  (SELECT 'b'))
    AS letters
INNER JOIN
  ((SELECT 1 AS n)
    UNION
  (SELECT 2))
    AS numbers
[/code]

If you explain the details of the problem perhaps another approach can be suggested.

[fenway]

[quote author=artacus link=topic=123053.msg512923#msg512923 date=1169697504]
mutex? Thats a new one to me. And all I'm finding in the manual is mutex status and mutex locks. Is that similar to a temp table?
[/quote]
It's basically just a fake table that you can use to "join" and create "records" for mysql to play with.