CODEWITHSUNDEEP
sql
suji
suji

Reputation: 53

SQL, select rows with only a certain value type

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

Answers (5)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use exists with like predicate :

SQL DEMO

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

Chris
Chris

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

Felipe Siqueira
Felipe Siqueira

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

Jay Black
Jay Black

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

Nutan
Nutan

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

Related Questions