Mysql Question posted in
The official MySQL documentation can be found here.

mySQL: How to query this properly?

I got this in my homework and can’t figure it out, please help.

I have 3 tables:
owners(id, name), breeds(id, type), dogs(id, ownerid, breedid)

Connections:
owners.id = dogs.ownerid AND breed.id = dogs.breedid

My assignment is to find the the owners name whose name is LIKE the breed type.
Example: ‘John Shepherd’ has a ‘german shepherd’, so write his name, because of the match in the naming of the breed type and the owners name.

Tried to solve this like this:

SELECT owners.name
FROM owners, dogs, breed
WHERE owners.name LIKE CONCAT('%', breeds.type, '%')
AND dogs.breedid = breeds.id
AND dogs.ownerid = owners.id;

I know this is faulty because the owner is not called ‘John German Shepherd’.

Is there a way to LIKE this query a way that the answer doesn’t consider the whole breed.type or considers types with multiple words in it?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    My original answer was correct, I overcomplicated the assignment..:/

    SELECT owners.name
FROM owners, dogs, breed
WHERE owners.name LIKE CONCAT('%', breeds.type, '%')
AND dogs.breedid = breeds.id
AND dogs.ownerid = owners.id;

    Splitting multiple words of data wasn't needed, because the specific breeds.types can only be understood in regards multiple word phrases like 'German shepherd'.

    The assignment stated I needed to find that owner's name who had the type of breed in the name. Doesn't states parts of the owner's dog's breedtype... What a draaaag. :D Thank you again @Shadow and @P.Salmon for your time and effort. Really appreciated !


  2. 0

    Try to use a wildcard in your SQL query to match parts of the owner’s name with the breed type.

    SELECT DISTINCT owners.name
FROM owners
JOIN dogs ON owners.id = dogs.ownerid
JOIN breeds ON dogs.breedid = breeds.id
WHERE LOWER(owners.name) LIKE CONCAT('%', LOWER(breeds.type), '%');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search