SQL code Example Analysis

1. Conditional Filtering & having

Table structure

#tab_a

#tab_b

Table relationships

Tab_a.id = tab_b.relation_id

Table Data

Demand

Inquiry the Tab_a ID corresponding to the table Tab_b member_id, when not found in table Tab_b, assignment is null

SelectTab_a.id, Case       when(SELECT Count(member_id) fromTab_bWHERErelation_id=Tab_a.id)= 0  Then NULL      ELSE(SELECTmember_id fromTab_bWHERErelation_id=tab_a.id)End  asresult fromTab_a

Results

Problem

When the ID of tab_a and the member_id of Tab_b exist 1-to-many relationships, such as Tab_b

Making the above SQL query again is an error:

[ERR] ERROR: More  than one row returned by a subquery used as an expression

Problem

How do I find the ID in tab_a corresponding to the tab_b in member_id there are multiple?

Select Count (*fromwhere tab_a.id=Groupby havecount (*) > 1

Results

　　

SQL code Example Analysis