Reputation: 53
Consider these 2 tables
1)Pets
---------------
Pet_Type |Bird
---------------
Dog |N
Owl |Y
Eagle |Y
Cat |N
2)customer_pets
------------------
Customer |pet_type
-------------------
Steve | dog, owl
john | owl, eagle
brad | eagle
cooper | cat
Jeff | dog, cat, owl
I want to query out those customers from customer_pets table who hold only birds (bird = y in the first table). Query result for the above set up will - John and brad
Thanks
Upvotes: 0
Views: 1831
Reputation: 50173
You can use exists
with like predicate :
select cp.*
from customer_pets cp
where not exists (select 1
from Pets p
where cp.pet_type LIKE CONCAT ('%', p.pet_type ,'%') and
p.Bird = 'N'
);
OUTPUT
| Customer | pet_type |
|----------|------------|
| john | owl, eagle |
| brad | eagle |
Upvotes: 0
Reputation: 42
This database structure is not proper, and will make your problem harder than it needs to be. You should never have multiple values in one field, like having dog & owl both in one field. What you need is 3 tables, one of which will be a "briding" table.
Table 1: Customers This table will hold the customer information, for example: id, name
Table 2: Pet types This table will hold the pet types, ie: id, typename
Table 3: Customer pet types This table will map pet types to customers, ie: id, customer_id (this field is a foreign key, identifying what customer this record is assigned to), pet_type_id (this field is a foreign key, identifying what pet type this record is).
When you look at a record in table 3, you'll see a customer id, which you can lookup in table 1 to see what customer it is. The pet type id can then be looked up in table 2 to see what pet type it is.
So for each pet type a customer has, they will have 1 record in table 3. If they have 3 pet types, they will have 3 records. For example:
Customers
----------
id name
1 john
2 joseph
3 jingleheimer
Pet Types
------------
id Name
1 Bird
2 Reptile
3 Dinosaur
Customer Pet Types
------------------
id customer_id pet_type_id
1 1 2
2 1 3
3 2 1
4 3 1
5 3 2
6 3 3
What this means, by each record in customer pet types
record 1: Customer 1 (John), has pet type 2 (reptile)
record 2: Customer 1 (John), has pet type 3 (dinosaur)
record 3: Customer 2 (Joseph), has pet type 1 (bird)
record 4: Customer 3 (Jingleheimer), has pet type 1 (bird)
record 4: Customer 3 (Jingleheimer), has pet type 2 (reptile)
record 4: Customer 3 (Jingleheimer), has pet type 3 (dinosaur)
Upvotes: 1
Reputation: 21
What is the type of column pet_type? If it's varchar (string), you can use something like this:
select * from customer_pets
inner join pets on(customer_pets.pet_type like '%' || pets.pet_type || '%')
where pets.bird = 'N'
Upvotes: 1
Reputation: 107
You need to read about 1st, 2nd, 3rd form normalisation. Each pet should be in its own columns type. then you'd use a join to return the types you want.
read about normalisation here: https://www.studytonight.com/dbms/database-normalization.php
read about table joins here:
https://www.w3schools.com/SQL/sql_join_inner.asp
Upvotes: 0
Reputation: 786
U can try this way
Select customer
from customer_pet
where customer_pet in (select pet from pets where bird='Y')
Upvotes: 0