Hi,
(this is using a MySQL 5x database)
Here is my first table - think of it as the records I want:
CREATE TABLE `t1` (
`ID` int(10) NOT NULL,
`Category` varchar(30) NOT NULL
);
INSERT INTO `t1` (`ID`, `Category`) VALUES
(1, 'a'),
(2, 'a'),
(3, 'a'),
(4, 'b'),
(5, 'b'),
(6, ''),
(7, '');
so records 1-3 are in category "a", 4-5 are in category "b" and 6 and 7 are not categorized. (they represent parts that could be needed in any category)
Now think of this table as a "helper table":
CREATE TABLE `t2` (
`_ID` int(10) NOT NULL,
`_Category` varchar(30) NOT NULL
);
INSERT INTO `t2` (`_ID`, `_Model`) VALUES
(6, 'a'),
(6, 'b');
What this says is, "record 6 should appear along with category a" and "record 6 should appear along with category b"
Now, here's the query I'm using:
SELECT *
FROM t1
CROSS JOIN t2
WHERE category = 'a'
OR (
_category = 'a'
AND id = _id
)
this gives me:
ID category _ID _category
1 a 6 a
1 a 6 b *
2 a 6 a
2 a 6 b *
3 a 6 a
3 a 6 b *
6 6 a
which, by the way, certainly gives me all the records I need (I'm not interested in any actual data in t2, just using it to join additional records). I starred the duplicates
However, I'd need to GROUP BY to avoid duplication, and I feel my query is just not that elegant. So the simple question is, do you have a better query than this? I appreciate the input of those more experienced in this, it makes me realize I don't do that much "thinking" anymore with my SQL queries.
