Multiple tables, one-to-many relationship, multiple matches using AND

[zq29]

I know the subject of this topic maybe somewhat confusing, but I was unsure how to word it. Consider this database:

 

registration

id, name

1, Kris

 

country

id, name

1, United Kingdom

2, British Virgin Islands

3, United States of America

 

registration_country

id,registration,country

1,1,1

1,1,2

 

What I am trying to do is select all of the registrations that have selected both United Kingdom and British Virgin Islands as countries based on keywords. This is my query which isn't working:

 

SELECT r.`name` 
FROM `registration` as r, `country` as c, `registration_country` as rc 
WHERE r.`id`=rc.`registration` AND c.`id`=rc.`country` AND (c.`name` LIKE '%united%' AND c.`name` LIKE '%british%')

 

I have also tried doing it with LEFT JOINS in a similar fashion which yielded similar results. I am getting results if I change the AND to an OR inside the parenthesis containing the keyword matching, but that's not giving me what I want, expectedly. I have a feeling I should be playing with nested queries, maybe?

[roopurt18]

Hmmmm.  Does this help?

 

SELECT
  r.`name`
FROM `registration_country` rc
INNER JOIN `registration` r ON rc.`registration`=r.`id`
INNER JOIN `country` c ON rc.`country`=c.`id`
WHERE
  c.`name` LIKE '%united%' OR
  c.`name` LIKE '%british%'
ORDER BY r.`name`
GROUP BY r.`id`

 

They're almost identical but with a GROUP BY thrown in to remove duplicate records from showing up.  I guess a distinct could handle that as well.

 

Also, I'm not keen on the:

c.`name` LIKE '' AND / OR c.`name` LIKE '' AND / OR ...

 

If there's a way to do LIKE matching but using an IN ( 'val1', 'val2', ... ) I think it'd be much cleaner; but I don't know if that functionality is possible.

[zq29]

Hmmmm.  Does this help?

Not really, I'm trying to return registrations that have selected multiple countries defined in the query, but the query doesnt return anything when using AND, which I can kinda understand why, but I can't figure out how to actually do it...

 

 

If there's a way to do LIKE matching but using an IN ( 'val1', 'val2', ... ) I think it'd be much cleaner; but I don't know if that functionality is possible.

I think that I could do it with FULLTEXT matching to get rid of the LIKE, but this is actually part of a query builder I'm putting together for a project, and that might get a bit more complex to do - I'll look into it, but thats for another day at the moment.

[PFMaBiSmAd]

If you want to find results where both specified values (not just one or the other) have matching rows, you need to do a WHERE that selects the rows with either matching value (using an OR or the IN() function), then have a GROUP BY the registration id and COUNT(*) as an alias to tell you how many in each group, then a HAVING clause where the count/alias = 2.

 

Pseudo sql (I did not attempt to match all your tables/columns/joins, but you get the idea) -

 

SELECT your_columns, COUNT(*) as cnt FROM your_joined_tables
WHERE country IN('United Kingdom','British Virgin Islands')
GROUP BY id HAVING cnt = 2

[zq29]

Thanks, I'll experiment with that.

[fenway]

That's generally the way that I do it, too.

[roopurt18]

Add in a good night of sleep and I see why my solution doesn't work!