Joint table query fetch a record

1212412

This is the case, two tables ...

Product table tb_product
p_code (product code) p_status (status)
111 7
222 8
333 9
This table p_code is unique, no duplicates

Usage table tb_usemode
u_code (product code) u_status (status)
111 1
111 1
222 3
222 2
333 0
This table has duplicates in u_code and also in u_status

How to join tables to query:
All unique records with p_status = 8 in tb_product or u_status = 1 in tb_usemode
The product code cannot be repeated.

The result should look like this
p_code u_code p_status u_status
111 111 7 1
222 222 8 2 (3)

If u_status has two, take one of them.

Thank you for your trouble.

恶魔右指

select p_code, u_code, p_status, u_status = min (u_status)
from tb_product a inner join tb_usemode b where a.p_code = b.u_code
group by p_code, u_code, p_status

我是杀人狂

select a.p_code, b.u_code, a.p_status, b.u_status from table1 a join table2 b on a.p_code = b.u_code where b.u_status = 1 group by .p_code, b.u_code, a.p_status
union all
select a.p_code, b.u_code, a.p_status, b.u_status from table1 a join table2 b on a.p_code = b.u_code where a.p_status = 8 and b.u_status in (select top 1 u_status from table2) group by .p_code, b.u_code, a.p_status

恶魔右指

declare @tb_product table (p_code int, p_status int)
insert @tb_product
select 111, 7
union all select 222, 8
union all select 333, 9


declare @tb_usemode table (u_code int, u_status int)
insert @tb_usemode
select 111, 1
union all select 111, 1
union all select 222, 3
union all select 222, 2
union all select 333, 0

select p_code, u_code, p_status, u_status = min (u_status)
from @tb_product a inner join @tb_usemode b on a.p_code = b.u_code--where where is changed to on
group by p_code, u_code, p_status


(The number of rows affected is 3)


(The number of rows affected is 5)

p_code u_code p_status u_status
----------- ----------- ----------- -----------
111 111 7 1
222 222 8 2
333 333 9 0

(The number of rows affected is 3)

恶魔右指

declare @tb_product table (p_code int, p_status int)
insert @tb_product
select 111, 7
union all select 222, 8
union all select 333, 9


declare @tb_usemode table (u_code int, u_status int)
insert @tb_usemode
select 111, 1
union all select 111, 1
union all select 222, 3
union all select 222, 2
union all select 333, 0

select p_code, u_code, p_status, u_status = min (u_status)
from @tb_product a inner join @tb_usemode b on a.p_code = b.u_code--where where is changed to on
where u_status> 0--Exclude the landlord from showing that this record is 0
group by p_code, u_code, p_status


(The number of rows affected is 3)


(The number of rows affected is 5)

p_code u_code p_status u_status
----------- ----------- ----------- -----------
111 111 7 1
222 222 8 2

(The number of rows affected is 2)

1212412

Ok

select p_code, u_code, p_status, u_status = min (u_status)
from tb_product a
inner join tb_usemode b on a.p_code = b.u_code
where a.p_status = 8 or b.u_status = 1
group by p_code, u_code, p_status

Thank you.

1212412

Thank you all.
I changed it ...

select p_code, u_code, p_status, u_status = min (u_status)
from tb_product a
inner join tb_usemode b on a.p_code = b.u_code
where a.p_status = 8 or b.u_status = 1
group by p_code, u_code, p_status

Yes, thank you again.