SQL statement queries

Transact-SQL
1

Hi all,
Thank you in advance for your time and suggestions.

I am new to SQL and trying to extract rows from table A join Table B on lid such that
for each group of gid having same sid but different ad_name (result look like Table C).

Table A

id name gid lid
1 name_abc G11 11
2 name_def G12 12
3 name_ghi G13 13
4 name_abc G14 14
5 name_ghf G15 15
5 name_ghf G15 16

Table B

lid ad_name sid
11 add_abc 111
11 add_abc 111
11 add_cde 111
14 add_abc 155
11 add_abc 155
14 add_cde 155
12 add_dce 123
12 add_dce 123
13 add_jhg 126
13 add_khg 127
15 add_kih 131
16 add_kik 131

Table C

id name gid lid sid ad_name
1 name_abc G11 11 111 add_abc
1 name_abc G11 11 111 add_cde
1 name_abc G11 11 111 add_abc
4 name_abc G14 14 155 add_abc
4 name_abc G14 14 155 add_cde
5 name_ghf G15 15 131 add_kih
5 name_ghf G15 16 131 add_kik
2

hi

hope this helps

create data script

drop table #Table_A
create table #Table_A (id int, name varchar(10), gid varchar(10), lid int)
insert into #Table_A select 1,'name_abc','G11',11
insert into #Table_A select 2,'name_def','G12',12
insert into #Table_A select 3,'name_ghi','G13',13
insert into #Table_A select 4,'name_abc','G14',14
insert into #Table_A select 5,'name_ghf','G15',15
insert into #Table_A select 5,'name_ghf','G15',16

drop table #Table_B
create table #Table_B(lid int, ad_name varchar(10), sid int)
insert into #Table_B select 11,'add_abc',111
insert into #Table_B select 11,'add_abc',111
insert into #Table_B select 11,'add_cde',111
insert into #Table_B select 14,'add_abc',155
insert into #Table_B select 11,'add_abc',155
insert into #Table_B select 14,'add_cde',155
insert into #Table_B select 12,'add_dce',123
insert into #Table_B select 12,'add_dce',123
insert into #Table_B select 13,'add_jhg',126
insert into #Table_B select 13,'add_khg',127
insert into #Table_B select 15,'add_kih',131
insert into #Table_B select 16,'add_kik',131

; with cte as 
 ( select a.id,a.name,a.gid,a.lid,b.sid,b.ad_name from #Table_A a join #Table_B b  on a.lid = b.lid  )
, cte2 as 
 (select id,name,gid,sid from cte group by id,name,gid,sid having min(ad_name) <> max(ad_name))
	select 
	   b.* 
	from 
	   cte2 a join cte b 
		  on a.id = b.id and a.name = b.name and a.gid = b.gid and a.sid= b.sid

image

3

Thanks for the reply Harish, it work but whether it's possible without creating script just using query ( In postgress db)

4

Hi

That script is to re create the sample data you put

You cannot just put images
How are we going to work on the data

As per forum proper etiquette

You are supposed to do that
And give us

Looks like
You are a total beginner