MySQL select with join table

join;MySQLselect

I have 3 tables, one of them is join table:

user table:

CREATE TABLE `user` (
`id`,
`name`)

media table:

CREATE TABLE `media` (
`id`,
`value`,
`type` enum('M', 'T'))

user_media table:

CREATE TABLE `user_media` (
`user_id`,
`media_id`)

I want to create a select which lets me search through user.name and media.value and return formated values.
Ex:

INSERT INTO `user` (`id`, `name`) VALUES 
(1, 'Luke'),
(2, 'Vader'),
(3, 'Gregory');

INSERT INTO `media` (`id`, `value`, 'type') VALUES 
(1, 'luke@mail.com', 'M'),
(2, 'skywalker@mail.com', 'M'),
(3, '123456789', 'T'),
(4, '123', 'T');

INSERT INTO `user_media` (`user_id`, `media_id`) VALUES 
(1, 1),
(1, 2),
(1, 3),
(2, 4);

search: luke@mail.com
output:
userId    |email          |tel
1         |luke@mail.com  |123456789

search: 123
output:
userId    |email          |tel
1         |luke@mail.com  |123456789
2         |null           |123

search: Gregory
output:
userId    |email          |tel
3         |null           |null

UPDATE

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel 
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mm.value LIKE "%luke@mail.com%"
  OR mt.value LIKE "%luke@mail.com%"
  OR u.name LIKE "%luke@mail.com%"

Result:

user_id mail               tel
1       luke@mail.com      luke@mail.com
1       skywalker@mail.com  luke@mail.com
1       123456789          luke@mail.com
1       luke@mail.com      skywalker@mail.com
1       luke@mail.com      123456789

II

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel 
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id AND mt.type="T"
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id AND mm.type="M"
WHERE mm.value LIKE "%luke@mail.com%"
  OR mt.value LIKE "%luke@mail.com%"
  OR u.name LIKE "%luke@mail.com%"

Result:

user_id mail               tel
1       luke@mail.com      null
1       luke@mail.com      null
1       luke@mail.com      123456789

III

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel 
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mt.type="T"
  AND mm.type="M"
  AND (mm.value LIKE "%Gregory%"
   OR mt.value LIKE "%Gregory%"
   OR u.name LIKE "%Gregory%")

Result: 0

Best Answer

You want 3 different searches and the results combined. I'd use UNION:

SELECT 
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel 
FROM `user` AS u
  LEFT JOIN user_media AS umt 
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON u.id = umt.user_id
  LEFT JOIN user_media AS umm 
         JOIN media AS mm ON  mm.id = umm.media_id
                          AND mm.type = 'M'
    ON u.id = umm.user_id
WHERE
  u.name LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT 
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel 
FROM media AS mt 
  LEFT JOIN user_media AS umt ON mt.id = umt.media_id
  LEFT JOIN `user` AS u ON u.id = umt.user_id 
  LEFT JOIN user_media AS umm 
        JOIN media as mm ON  mm.id = umm.media_id
                         AND mm.type = 'M'
    ON umt.user_id  = umm.user_id
WHERE
  mt.type = 'T'
  AND mt.value LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT 
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel 
FROM media AS mm 
  LEFT JOIN user_media AS umm ON mm.id = umm.media_id
  LEFT JOIN `user` AS u ON u.id = umm.user_id 
  LEFT JOIN user_media AS umt 
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON umm.user_id = umt.user_id
WHERE
  mm.type = 'M'
  AND mm.value LIKE CONCAT('%', ?, '%') ;

Tested at SQLfiddle. (replace the ? with the value you want to search for.)

Related Question