Hello, I am trying to retrieve a cross join of two tables. Table one contains an id column, table two contains a column that can list up to three id's from table one.
SELECT dix_ondemand_genre.id, dix_ondemand_shows.genre, id IN(dix_ondemand_shows.genre) as test FROM dix_ondemand_genre CROSS JOIN dix_ondemand_shows ON id IN(dix_ondemand_shows.genre); This select finds only the first value of the IN() column, however i want to return every possible result for all genres (Cartesian product): to illustrate - here is a sample output of what i do get: id | genre 1 | 1,2,6 3 | 3,6 4 | 4,6 But this s what i want is to have: 1 | 1,2,6 2 | 1,2,6 3 | 3,6 4 | 4,6 5 | NULL 6 | 1,2,6 6 | 3,6 6 | 4,6 actually - what i really want is to be able to group the id column to count how many genres are returned for each id, however the group by isn't the problem... I've tried quite a number of Join types, all to no avail. any help would be great. ken easson justken.net [EMAIL PROTECTED] justken web programming and technical support. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
