CODEWITHSUNDEEP
mysqlsql
boby lapointe
boby lapointe

Reputation: 535

Single request search in multiples tables using join table

I've got to search in 3 differents tables a term. Here are my 3 requests, I'd like to make only one. Tables are teledis, afr, software_cache. If you want, here is my schema : https://i.sstatic.net/HlIkp.jpg

SELECT teledis.nameid
FROM teledis 
LIKE vulcain = '%XXX%';



SELECT teledis.nameid
FROM joinafr
    JOIN teledis
        ON joinafr.teledis_id = teledis.idteledis
    JOIN afr 
        ON joinafr.afr_id = afr.idafr
WHERE afr.name LIKE '%XXX%';



SELECT teledis.nameid
FROM softs
    JOIN software_cache
        ON softs.id_soft_cache = software_cache.id
    JOIN joinsoft
        ON softs.idsofts = joinsoft.soft_id
    JOIN teledis
        ON joinsoft.soft_id = teledis.idteledis WHERE software_cache.name LIKE '%XXX';

Upvotes: 0

Views: 32

Answers (2)

Harsh Gupta
Harsh Gupta

Reputation: 4538

You can try something like this:

SELECT teledis.nameid, afr.name, software_cache.name
FROM softs
JOIN software_cache
    ON softs.id_soft_cache = software_cache.id
JOIN joinsoft
    ON softs.idsofts = joinsoft.soft_id
JOIN joinafr
    ON joinafr.teledis_id = teledis.idteledis
JOIN teledis
    ON joinsoft.soft_id = teledis.idteledis
JOIN afr 
    ON joinafr.afr_id = afr.idafr
WHERE afr.name LIKE '%XXX%' OR software_cache.name LIKE '%XXX';

Upvotes: 1

user327961
user327961

Reputation: 2490

SELECT teledis.nameid
FROM teledis 
WHERE vulcain LIKE '%XXX%'
UNION
SELECT teledis.nameid
FROM joinafr
    JOIN teledis
        ON joinafr.teledis_id = teledis.idteledis
    JOIN afr 
        ON joinafr.afr_id = afr.idafr
WHERE afr.name LIKE '%XXX%'
UNION
SELECT teledis.nameid
FROM softs
    JOIN software_cache
        ON softs.id_soft_cache = software_cache.id
    JOIN joinsoft
        ON softs.idsofts = joinsoft.soft_id
    JOIN teledis
        ON joinsoft.soft_id = teledis.idteledis WHERE software_cache.name LIKE '%XXX';

Upvotes: 1

Related Questions