How do I join this data in MySQL?
If I have a table like this:
TABLEA
id| object | type
1 | greg | person
2 | mary | person
3 | jared | person
4 | kelly | person
5 | melissa | person
6 | william | person
7 | skiing | hobby
8 | biking | hobby
TABLEB
id | husband | wife
1 | greg | mary
2 | jared | kelly
3 | william | kelly
TABLEC
id | female | hobby
1 | mary | skiing
2 | kelly | biking
Is there a way to get the results table:
TABLED
id | a | b | link
1 | 1 | 2 | related
2 | 3 | 4 | related
3 | 6 | 4 | related
4 | 1 | 4 | related
5 | 2 | 7 | likes
6 | 4 | 8 | likes
Use only MySQL query (s)?
- The logic will basically start iterating from table B and all rows of table B.
- The third column
related
when the table is selectedTABLEB
andlikes
when the processed table is TABLE B.
Example logic:
Looking at the first line of TABLEB
(husband) greg and wife (mary), it looks with TABLEA to see that greg is on line 1 (id 1) and mary is on line 2 (id 2) and creates a new TableD with the first line equal to 1 | 2.
Is there a query that can do some kind of join, or something that could do it, without having to programmatically iterate over all the TABLEB lines and then all the TABLEC lines to create the TABLED you want?
source to share
You will need the JOIN
table tablea
two times with tableb
:
- one for
husband
>object
and the other, - one for
wife
>object
.
So, you can get two husband and wife IDs on the same row.
The same with Tablec
p tablea
. Then use UNION
(implicit distinctive) or UNION ALL
to combine the two result sets.
Something like:
SELECT
(@rownum := @rownum + 1) AS id,
sub.*
FROM
(
SELECT
ah.id AS a,
aw.id AS b,
'related' AS link
FROM tableb AS b
INNER JOIN Tablea AS ah ON ah.object = b.husband AND ah.type = 'person'
INNER JOIN Tablea AS aw ON aw.object = b.wife AND aw.type = 'person'
UNION ALL
SELECT
a.id AS a,
a2.id AS b,
'hobby'
FROM tablec AS c
INNER JOIN tablea AS a ON a.object = c.female
INNER JOIN tablea AS a2 ON a2.object = c.hobby AND a2.type = 'hobby'
) AS Sub, (SELECT @rownum := 0) AS t;
SQL Fiddle Demo
This will give you:
| ID | A | B | LINK |
------------------------
| 1 | 1 | 2 | related |
| 2 | 3 | 4 | related |
| 3 | 6 | 4 | related |
| 4 | 2 | 7 | hobby |
| 5 | 4 | 8 | hobby |
Note:
-
This query will give you 5 rows in total, where the expected result set you are looking for is 6. It has no row
4 | 1 | 4 | related
becausegreg
there is no entry for those (id 1) andkeyll
(id 4) intableb
. As you explained in your question. -
The new column
id
is an automatic incremental identifier generated from the result set, it is not selected from the tables.
If you need to create a new table from this selection, use the following syntax:
CREATE TABLE Tabled
AS
SELECT ...
... -- the same previous select query
and you will have a new table tabled
having the same structure of this SELECT
.
source to share
create table tabled (
id int auto_increment,
a varchar(10),
b varchar(10),
link varchar(10));
insert into tabled (a, b, link)
select a1.id a, a2.id b, 'related' link
from tablea a1
join tableb b on a1.object = b.husband and a1.type = 'person'
join tablea a2 on a2.object = b.wife and a2.type = 'person'
union all
select a1.id a, a2.id b, 'likes' link
from tablea a1
join tablec c on a1.object = c.female and a1.type = 'person'
join tablea a2 on a2.object = c.hobby and a2.type = 'hobby';
source to share