Hi, I want to match the 'CODE' of PUBL table from the 'CODE' of Service table. I need to pick all record from Service table which matches the all Code from PUBL table. Thanks.

Select Serv, Code, from Service

CServ Code

100   DHR

100  HYR

100  JTY

200 DHR

200 HYR

300 DHR

300 HYR

300 JTY

300 QTY

Select * from PUBL

Code

DHR

HYR

JTY

Desired output:

100   DHR

100  HYR

100  JTY

300 DHR

300 HYR

300 JTY

300 QTY

Your expected output doesn't seem to match with the description - can you explain further?

This is how you give us example data and DDL:

DECLARE @service TABLE (cServ INT, code CHAR(3))
INSERT INTO @service (cServ, Code) VALUES 
(100, 'DHR'),(100, 'HYR'),(100, 'JTY'),(200, 'DHR'),(200, 'HYR'),(300, 'DHR'),(300, 'HYR'),(300, 'JTY'),(300, 'QTY')
DECLARE @publ TABLE (code CHAR(3))
INSERT INTO @publ (Code) VALUES
('DHR'),('HYR'),('JTY')

It's clear what's what, and we can take it and go.

Here's an answer to your question:

SELECT *
  FROM @publ p
    INNER JOIN @service s
	  ON p.code = s.code

Hi, Output is wrong. I need to get the above desired output. All code of PUBL table should match with the code of Service table. Thanks.

• Edited by Kevin Ruth 10 hours 31 minutes ago

Would something like this work?

DECLARE @service TABLE (cServ INT, code CHAR(3))
INSERT INTO @service (cServ, Code) VALUES 
(100, 'DHR'),(100, 'HYR'),(100, 'JTY'),(200, 'DHR'),(200, 'HYR'),(300, 'DHR'),(300, 'HYR'),(300, 'JTY'),(300, 'QTY')
DECLARE @publ TABLE (code CHAR(3))
INSERT INTO @publ (Code) VALUES
('DHR'),('HYR'),('JTY');

with cds as (
select count(distinct Code) as NumCodes from  @publ p1 
) , inc as (
select cServ, count(distinct s.Code) as NumCode, pc.NumCodes
from @service s
inner join @publ p
on s.code = p.code
cross join cds pc
group by cServ, pc.NumCodes
having count(distinct s.Code) = pc.NumCodes
)
select s.* from @service s
inner join inc i
on s.cServ = i.cServ

If the output is wrong, then either the example data you gave us, or your requirements are not correct.

Please check and clarify as needed.

If Russ' interpretation is right, then maybe this:

DECLARE @service TABLE (cServ INT, code CHAR(3))
INSERT INTO @service (cServ, Code) VALUES 
(100, 'DHR'),(100, 'HYR'),(100, 'JTY'),(200, 'DHR'),(200, 'HYR'),(300, 'DHR'),(300, 'HYR'),(300, 'JTY'),(300, 'QTY')
DECLARE @publ TABLE (code CHAR(3))
INSERT INTO @publ (Code) VALUES
('DHR'),('HYR'),('JTY')

;WITH basePubl AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY code) AS seq
  FROM @publ
), rCTEPubl AS (
SELECT CAST(code AS VARCHAR(MAX)) AS code, seq
  FROM basePubl
 WHERE seq = 1
UNION ALL
SELECT a.code + ', ' + r.code, r.seq
  FROM rCTEPubl a
    INNER JOIN basePubl r
	  ON a.seq + 1 = r.seq
), baseService AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY cServ ORDER BY code) AS seq
  FROM @service
), rCTEService AS (
SELECT cServ, CAST(code AS VARCHAR(MAX)) AS code, seq
  FROM baseService
 WHERE seq = 1
UNION ALL
SELECT r.cServ, a.code + ', ' + r.code, r.seq
  FROM rCTEService a
    INNER JOIN baseService r
	  ON a.seq + 1 = r.seq
	  AND a.cServ = r.cServ
)
SELECT *
  FROM @service
 WHERE cServ IN (
                 SELECT cServ
                   FROM ( 
                         SELECT *
                           FROM rCTEPubl
                          WHERE seq = (SELECT MAX(seq) FROM rCTEPubl)
                        ) pub
                     LEFT OUTER JOIN (
                 	            SELECT *
                                   FROM rCTEService s
                                  WHERE seq = (SELECT MAX(seq) FROM rCTEService WHERE cServ = s.cServ)
                 	           ) s
                       ON s.code+' ' LIKE pub.code+'%'
                )

I think your solution is checking for the right number of entries, rather than the specific entries Russ.

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

And you need to read and download the PDF for: 
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

There is no generic, universal code in RDBMS; because of the Law of Identity, it has to be <something in particular>_code by definition. A table is a set, so its name has to be a plural or collective name. 

Rows are not records. What you want to do is called a relational division. It was one of Codd's original 8 operators and you can Google it.  If you will not DDL for us, why should we post DML for you?