1

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

0

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.)

  • This solution works correctly, but it is very slow. Most likely i will modify the database structure. Thank you for your answer – Darii Petru May 18 '15 at 6:33
  • You'll need to optimize the 3 subqueries independetly. Most likely you need indexes. – ypercubeᵀᴹ May 18 '15 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.