CODEWITHSUNDEEP
sqloracle11g
Jayaram
Jayaram

Reputation: 1725

How to get one record when there are multiple records using join

Need a help for the sql query to fetch record from the below table structure:

Table A:
trans_id  msgid recivtime 
1          p      dd-mm-yyyy hh24i mi:ss
1          Q      dd-mm-yyyy hh24i mi:ss


Table B:
msgid      status  sendtime 
P           C       dd-mm-yyyy hh24i mi:ss
P           -        -
Q           C       dd-mm-yyyy hh24i mi:ss

I want to fetch for one trans_id from table A, status details from table B (only the first record from the above example), so out put should be

transid   msgid status sendtime
1         P      C      dd-mm-yyyy
1         Q      C      dd-mm-yyyy

I tried with 

SELECT a.msgid, 
       a.recivtime, 
       b.status 
FROM   a a, 
       b b 
WHERE  a.msgid = b.msgid 
       AND a.transid = ?

its giving the duplicate record that present in the table B as well, to remove that I tried with distinct but seems didn't go through.

Thanks for help .

Upvotes: 0

Views: 2137

Answers (2)

Ronnis
Ronnis

Reputation: 12843

If I understand you correctly then {transid, msgid} is key in Table A, meaning that for every transid there will be one or more msgid. For every msgid there will be one or more detail records in Table B. You said there was not a key in the Table B, but you could probably find a natural key anyway, such as {msgid, sendtime}.

The following query will give you the record in Table B with the highest sendtime. Let me know if this is what you want.

select a.msgid
      ,a.recivtime
      ,b.status
  from table_a
  join table_b on(
       b.msgid = a.msgid
  )
 where b.sendtime = (
         select max(b.sendtime)
           from table_b x
          where x.msgid = a.msgid
       )
   and a.transid = ?

Upvotes: 0

some_coder
some_coder

Reputation: 340

For this i'am assuming that you want to solve some kind of "get the newest entry for a specific msgid from tableb"-problem with a 1:n relationship (one table_a.msgid connects to multipble table_b.msgid records):

I do this with three views to accomplish this. Every record must have a primary key with is just an numeric value. using the msgid isn't enough. First i select the "newest" record-id for each msgid from table_b using the hightest "id"-Values (which is my primary column and just a growing value). The idea behind this is "the newest entry in table_b (for each msgid) has the highest primary key:

create view table_b_max as 
select msgid, max(id) from tableb
group by msgid

then i use this "max"-view (which contains only the msgid and the highest corresponding id) to select the full records:

create view table_b_max_full
select b.* from table_b b, table_b_max b_max
where b.id = b_max.max_id and 
      b.msgid = b_max.msg_id

after that i join table_a and table_b_max_full

create view table_a_b   
    select a.*, b.* from table_a a, table_b_max_full b
    where a.msgid = b.msgid (+)

And now you can access the "newest" status of a table_a_b using the msgid.

select * from table_a_b where msgid = ?

Upvotes: 1

Related Questions