CODEWITHSUNDEEP
sql-server
None
None

Reputation: 5670

Get union of two table and taking data with a condition

I have two tables table-a 

id   name
100    asd
101    ass
102    gdd
103    hgf
104    cvd
105    erf

table-b

id     filter
100     red
101     blue
100     green
100     yellow
102     black
102     red
103     dark

Table-a is the master table and that have all the id's.but Table two is the one which has 'filter' data. from these two table I want to find out all those 'id's which does not have minimum 2 filters. note that table-b does not have all the itemnumbers in table-a, and i want all that itemnumber irrespective of if that is in table-a or table-b.I have tried inner joining these two tables and getting data out but nothing worked.

Upvotes: 0

Views: 205

Answers (3)

Tim
Tim

Reputation: 8921

I think this would work in SQL Server (tested in SQLite and usually the two are fairly compatible when it comes to inline view syntax). But syntax issues aside, inline views can make working with sets easier to visualize.

    select TA.id, name 
    from  TA  
    inner join
    (     

     select id from TA
     where not exists (select id from TB where TA.id = TB.id)       

     UNION         

     select id from TB
     group by id having count(filter) < 2         

    )  as FOO
   on TA.id = FOO.id

The default behavior of UNION is to remove duplicates.

The first UNIONed set consists of the ids from table A that have no filter (no counterpart in the filters table B).

The second UNIONed set consists of the ids from the filters table, table B, that have only 1 filter.

We inner join those unioned sets back to Table A to get the entity Name.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Select A.ID, A.Name, count(*)
from tableA A
LEFT JOIN tableB B on A.ID = B.ID
Group By A.ID, A.name
having count(*) <= 1
  • LEFT JOIN gives all records from A and only those in B which match.
  • The group by ID and name let us count the number of filters found in each

  • The having says give me any items with a count less than or equal to 1. (or less than the minimum 2)

    Thus results would be.

    101 ass 1
103 hgf 1
104 cvd 0 
105 erf 0

Upvotes: 2

tschmit007
tschmit007

Reputation: 7790

select
    *
from
    table-a a
    left join (
        select id, count(id) as c from table-b group by id
    ) v on a.id = v.id
where isnull(v.id, 0) < 2

Upvotes: 0

Related Questions