CODEWITHSUNDEEP
mysql
maaudet
maaudet

Reputation: 2358

MySQL search with relations

I have 3 tables.

user
    id
    firstname
    lastname
    ...

user_rel_domain
    user_id
    domain_id
    value

domain
    id
    name

I would like to make a search that would match the firstname, the lastname and the domain(s) name(s) if the value in user_rel_domain is higher then 0.

How could I do this in a single MySQL query?

EDIT:

Ok, I though that this idea would be easy to integrate from the 3 tables example, but I actually have 6 tables and I don't think that the answers can be used for the structure I really have, so here it is, sorry everyone!:

user
    id
    firstname
    lastname
    ...

user_rel_domain
    user_id
    domain_id
    value

domain
    id
    name

user_rel_mandate
    user_id
    mandate_id
    value

mandate
    id
    cat_id
    name

mandate_cat
    id
    name

Basically I want to search in all the "name" columns and the firstname and lastname of the user table while all the value from the relation tables be higher then 0.

Upvotes: 1

Views: 88

Answers (3)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

EDIT: Hmm... I made a few assumptions about your new requirements, so let me know if this is in the ballpark:

SELECT
    u.firstName,
    u.lastName,
    d.name AS domainName,
    m.name AS mandateName,
    mc.name AS mandateCatName
FROM
    user u
    JOIN user_rel_domain urd ON urd.user_id = u.id AND urd.value > 0
    JOIN domain d ON d.id = urd.domain_id
    JOIN user_rel_mandate urm ON urm.user_id = u.id AND urm.value > 0
    JOIN mandate m ON m.id = urm.mandate_id
    JOIN mandate_cat mc ON mc.id = m.cat_id
WHERE
    u.firstName = @SearchTerm
    OR u.lastName = @SearchTerm
    OR d.Name = @SearchTerm
    OR m.Name = @SearchTerm
    OR mc.Name = @SearchTerm

Upvotes: 3

pQd
pQd

Reputation: 126

SELECT user.firstname, user.lastname FROM user 
 JOIN user_rel_domain ON user.id=user_rel_domain.user_id 
 JOIN domain ON domain.id=user_rel_domain.domain_id 
WHERE user_rel_domain.value>0

edit: are you looking for an exact match eg name in all of the columns should be equal to string you are looking for? or is it enough if any of the names is equal?

maybe this then:

SELECT user.firstname, user.lastname FROM user 
 JOIN user_rel_domain ON user.id=user_rel_domain.user_id 
 JOIN domain ON domain.id=user_rel_domain.domain_id 
 JOIN user_rel_mandate ON user.id=user_rel_mandate.user_id
 JOIN mandate ON user_rel_mandate.mandate_id=mandate.id
 JOIN mandate_cat ON mandate.id=mandate_cat.mandate_ID
WHERE 
 user_rel_domain.value>0 AND 
 (user.firstname="string" OR user.lastname="string" OR domain.name="string" OR mandate.name="string")

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

SELECT u.firstname, u.lastname, d.name
    FROM user_rel_domain urd
        INNER JOIN user u
            ON urd.user_id = u.id
        INNER JOIN domain d
            ON urd.domain_id = d.id
    WHERE urd.value > 0;

Upvotes: 2

Related Questions