sql - Query 2 different items within the same field and the same table -

i'm new sql , don't know how query 2 different items within same field , same table. i'm writing in excel vba using sql via oledb attach postgresql datasource

basically have 2 queries need combine 1 query. first query primary group. need first find people code c10%. of c10 have code r110%

the codes in srch table , people names in person table, these joined master_id=p.entity_id

here 2 queries need combine:

dim diag string     diag = "select distinct master_id, eventdate, code, term, surname, forename " _     & "from srch inner join person p on master_id=p.entity_id " _     & "where code 'c10..%' " _     & "order master_id "   dim diag string     diag = "select distinct master_id, eventdate, code, term, surname, forename " _     & "from srch inner join person p on master_id=p.entity_id " _     & "where code 'r110%' " _     & "order master_id "   

the tables have 100 of rows , each has master_id identifies person. therefore row master_id = 1 , code = c10. next master_id= 1 , code r110.

you correct in different codes cannot exist on same row

does help.

person table entity_id | surname | forename 1         | smith   | john 2         | mouse   | mickey 3  srch table master_id | code | term | eventdate 1         |c10   |  dm  | 01/01/2000 2         |r110  |  al  | 01/01/2001 1         |r110  |  al  | 01/01/2002 

i need find person 1

result master_id|code|term| eventdate |surname|forename    1     |r110| al | 01/01/2002| smith | john   

since column references aren't qualified, can't tell table each column reference refers to.

---

suggestion: aid future readers of sql, please consider qualifying all column references table name, or better, short (unique) table alias. future reader won't scouring table definitions figure out table contains column.) consider going happen sql statement when column of same name added table used in query.)

---

we'll have guess @ table contains columns.

i'd suggest pattern using group clause, rather distinct.

and suggest computing aggregate on result of expression tests whether included row satisfies particular condition.

and performing test on aggregate, see if rows existed (within group) condition evaluated true.

as example: (not tested)

select s.master_id      , s.eventdate      , s.code      , s.term      , p.surname      , p.forename   srch s  inner   join person p      on p.entity_id = s.master_id  ( s.code 'c10..%' or s.code 'r110%' )  group     s.master_id      , s.eventdate      , s.code      , s.term      , p.surname      , p.forename having sum(case when s.code 'c10..%' 1 else 0 end) > 0    , sum(case when s.code 'r110%'  1 else 0 end) > 0  order s.master_id 

the case expressions evaluate either 1 or 0, each row. sum() aggregate total 1s , 0s up. return aggregate greater 0 if there row (within "group") satisfied condition.

if there no rows (within group) code 'r110%', sum() evaluate zero, , comparison evaluate false, , row not returned.

note: comparison of aggregates in having clause because results aggregates not available when conditions in clause evaluated, when rows accessed.

followup

doh! query above isn't going return rows. that's bad. there's reason test against test cases. helps identify doofus problems 1 in query suggested above.

it's impossible query return rows. code column (as hadn't noticed) in group clause. @ least 1 of aggregate functions in having clause guaranteed evaluate zero.

(my problem hadn't noticed code column in group by. doh!)

---

if of columns in select list need match, except "code" column... (i hate use expensive correlated subquery when don't have to...) add "exists (correlated subquery).

if master_id foreign key reference entity_id in person, , entity_id primary key of person... put off join operation until after had results srch.

does term , event need match, or code? how write query depends on that...

---

based on responses in comments, term , event_date don't need match. we're looking rows in srch same person (master_id) have @ least 1 row c10 code , @ least 1 row r110 code.

identifying values of master_id follows same pattern in query above, using group , conditional tests on aggregates in having clause.

this query should return master_id values have both c10 code , r110 code. can test... doesn't return whole resultset, in gets master_id values want return:

select r.master_id   srch r  ( r.code 'c10..%' or r.code 'r110%' )  group     r.master_id having sum(case when r.code 'c10..%' 1 else 0 end) > 0    , sum(case when r.code 'r110%'  1 else 0 end) > 0     

once that, can use query inline view... wrap in parens, assign alias , reference table. example:

select q.*   ( select r.master_id            srch r           ( r.code 'c10..%' or r.code 'r110%' )           group              r.master_id          having sum(case when r.code 'c10..%' 1 else 0 end) > 0             , sum(case when r.code 'r110%'  1 else 0 end) > 0        ) q     

we should test whether postgresql run that. if can't run, there's no point in building on it. once confirm runs, can add join srch table, rows have matching master_id c10 or r110 code.

select q.master_id      , s.code      , s.term      , s.event_date   ( select r.master_id            srch r           ( r.code 'c10..%' or r.code 'r110%' )           group              r.master_id          having sum(case when r.code 'c10..%' 1 else 0 end) > 0             , sum(case when r.code 'r110%'  1 else 0 end) > 0        ) q   join srch s     on s.master_id = q.master_id    , s.code 'r110%' 

we can add join person table, retrieve given name , surname primary key lookup.

  join person p     on p.entity_id = s.master_id 

add appropriate column references select list. original queries had distinct keyword. can add that, or add group clause. whichever.

we can reference master_id either inline view or srch table, or entity_id person table. join conditions guarantee non-null , equal each other.

and wind (desk checked only, not tested):

select s.master_id      , s.code      , s.term      , s.event_date      , p.surname      , p.forename   ( select r.master_id            srch r           ( r.code 'c10..%' or r.code 'r110%' )           group              r.master_id          having sum(case when r.code 'c10..%' 1 else 0 end) > 0             , sum(case when r.code 'r110%'  1 else 0 end) > 0        ) q   join srch s     on s.master_id = q.master_id    , s.code 'r110%'   join person p     on p.entity_id = s.master_id  group     s.master_id      , s.code      , s.term      , s.event_date      , p.surname      , p.forename  order     s.master_id      , s.code 

and, if haven't made other doofus mistake again, expect return result specified op. (i've attempted provide few comments along way, how went building query.)

i'd interested in finding out how big of smoke ball 1 makes.

another followup

as alternative, since don't need return c10 rows, query little simpler. inline view return master_id values related c10 codes, , can dispense having clause tests on aggregates. should return result equivalent 1 above, perhaps little faster:

select s.master_id      , s.code      , s.term      , s.event_date      , p.surname      , p.forename   ( select r.master_id            srch r           r.code 'c10..%'           group              r.master_id        ) q   join srch s     on s.master_id = q.master_id    , s.code 'r110%'   join person p     on p.entity_id = s.master_id  group     s.master_id      , s.code      , s.term      , s.event_date      , p.surname      , p.forename  order     s.master_id      , s.code