Not sure what to call this, -- I'm sure this has to be asked elsewhere.
Consider the following, eg.
CREATE TABLE abbrv (
abbrv_id int unsigned primary key not null auto_increment,
usps_primary varchar(64) not null,
usps_preferred varchar(16) not null
);
CREATE TABLE abbrv_variation (
variation_id int unsigned primary key not null auto_increment,
abbrv_id int unsigned, -- FK
variation varchar(64) not null
);
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('North', 'N');
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('South', 'S');
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('East', 'E');
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('West', 'W');
INSERT INTO `abbrv` (usps_primary, usps_preferred) values('ALLEY', 'ALY');
SET @abbrvId = LAST_INSERT_ID();
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ALLEE');
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ALLY');
INSERT INTO `abbrv` (usps_primary, usps_preferred) values('ANEX', 'ANX');
SET @abbrvId = LAST_INSERT_ID();
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ANNEX');
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ANNX');
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ANX');
What I am simply trying to do is return something such as:
+--------------+----------------+-----------+
| usps_primary | usps_preferred | variation |
+--------------+----------------+-----------+
| North | N | North | <-- 1 row for "usps_primary"
| North | N | N | <-- 1 row for "usps_preferred"
| South | S | South | <-- 1 row for "usps_primary"
| South | S | S | <-- 1 row for "usps_preferred"
| East | E | East | <-- 1 row for "usps_primary"
| East | E | E | <-- 1 row for "usps_preferred"
| West | W | West | <-- 1 row for "usps_primary"
| West | W | W | <-- 1 row for "usps_preferred"
| ALLEY | ALY | ALLEY | <-- 1 row for "usps_primary"
| ALLEY | ALY | ALY | <-- 1 row for "usps_preferred"
| ALLEY | ALY | ALLEE | X-- one row for each
| ALLEY | ALY | ALLY | X-- variation
| ANEX | ANX | ANEX | <-- 1 row for "usps_primary"
| ANEX | ANX | ANX | <-- 1 row for "usps_preferred"
| ANEX | ANX | ANNEX | X-- one row for each
| ANEX | ANX | ANNX | X-- variation
The ordering doesn't matter - just that they're all there. Originally this table simply had the "variation" rows for each of these but it's became unmaintainable as it's a very large table to make sure all these rows are entered each time there is a new variation added.
I've tried of course a simple LEFT JOIN, CROSS JOINs, OUTER JOINs, Joining some left and some right - I really don't know what the appropriate course of action here is, at all.
A cross join seems approximately correct but I do not want the entire series, just 2x each.
