CODEWITHSUNDEEP
sqlselect
didiroro
didiroro

Reputation: 23

about select query, one table multiple result

I searched before asking my question:

I have two tables.

Table info:

no    | name
1     | david
2     | kang

Table info_kill:

no   | staffno | skillno
------------------------
1    |    1    |    1
2    |    2    |    1
3    |    2    |    2
4    |    2    |    3

staffno in info_skill table and no in info table is foreign key.

I want to get:

  1. name contains 'ka'
  2. have skillno 1, 2, 3.

so, this is my sql,

SELECT a.NAME
FROM   (SELECT no,
               NAME
        FROM   info
        WHERE  NAME LIKE '%ka%') AS a
       INNER JOIN info_skill AS b
               ON a.no = b.staffno
       INNER JOIN info_skill AS c
               ON a.no = c.staffno
       INNER JOIN info_skill AS d
               ON a.no = d.staffno
WHERE  b.skillno = '1'
       AND c.skillno = '2'
       AND d.skillno = '3'

what I wonder is this part.

       INNER JOIN info_skill AS b
               ON a.no = b.staffno
       INNER JOIN info_skill AS c
               ON a.no = c.staffno
       INNER JOIN info_skill AS d
               ON a.no = d.staffno
WHERE  b.skillno = '1'
       AND c.skillno = '2'
       AND d.skillno = '3'

Is there any other way?

Upvotes: 1

Views: 67

Answers (2)

juergen d
juergen d

Reputation: 204766

select a.name 
from info a
inner join info_skill as b on a.no = b.staffno
where b.skillno in (1,2,3)
and name like '%ka%'
group by a.name
having count(distinct b.skillno) = 3

SQLFiddle demo

Upvotes: 1

user4380525
user4380525

Reputation:

Try this:

SELECT I.name
FROM info I
    JOIN info_skill S
         ON I.no = S.staffno
WHERE I.name LIKE'%ka%" AND
      S.skillno=1 OR
      S.skillno=2 OR
      S.skillno=3

This means:

  • You join the two tables following the foreign key
  • Then you search for any name containing ka word using LIKE %ka%
  • Now, you need to check if skillno is equal to 1, 2 or 3

If this is not what you need exactly, then please make your question more clear by explaining what you are looking for exactly.

Upvotes: 2

Related Questions