CODEWITHSUNDEEP
sql
user493417
user493417

Reputation: 23

Struggling with making a sql query

I've been struggling with making a sql query that returns the following ;

A list of names of all pairs who are coworkers and friends

my table is build from the following command;

CREATE TABLE relation(
name_1 varchar(255),
name_2 varchar(255),
rel varchar(255)
)

I've tried

SELECT NAME_1, NAME_2 from relation
where rel LIKE 'c%' and rel like 'f%';

but the table returned is empty.

Upvotes: 1

Views: 133

Answers (4)

Andomar
Andomar

Reputation: 238076

No row can have a rel that starts with c and f at once.

One approach would be to create a subquery to classify relations. Here, it classifies coworkers as 1 and friends as 2. To detect relation rows with the names swapped, the query picks the first name in alphabetical order as name_1. The having clause demands that both types of relations are present.

select  name_1
,       name_2
from    (
        select  case when name_1 > name_2 then name_1 else name_2 end as name_1
        ,       case when name_1 > name_2 then name_2 else name_1 end as name_2
        ,       case when rel like 'c%' then 1 
                     when rel like 'f%' then 2 
                end as Type
        from    YourTable
        ) as SubQueryAlias
group by
        name_1
,       name_2
having  count(distinct Type) = 2

Another approach is to select all coworkers, and use exists to demand that they are friends too:

select  distinct
        case when name_1 > name_2 then name_1 else name_2 end as name_1
,       case when name_1 > name_2 then name_2 else name_1 end as name_2
from    YourTable c
where   c.rel like 'c%'
        and exists
        (
        select  *
        from    YourTable f
        where   f.rel like 'f%'
                and 
                (
                    (c.name_1 = f.name_1 and c.name_2 = f.name_2)
                    or (c.name_1 = f.name_2 and c.name_2 = f.name_1)
                )
        )

Upvotes: 1

user623879
user623879

Reputation: 4142

You need to use

SELECT NAME_1, NAME_2 from relation
where rel LIKE 'c%' or rel like 'f%';

rel cannot start with c and start with f.

Upvotes: 1

user330315
user330315

Reputation:

LIKE 'c%' and rel like 'f%';

Think about what you are saying there: return all rows where the column rel starts with a c and at the same time starts with a f.

Clearly not possible

You probably want:

where rel LIKE 'c%' OR rel like 'f%'

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125486

this condition never exist

 rel LIKE 'c%' and rel like 'f%';

you cant have word that start with c and with f !

maybe you mean to OR

Upvotes: 0

Related Questions