Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Query Syntax to join 2 tables and get records without repetation

Dear Team,

I have an oracle database. If I run a query like

select Ticket,Priority from incident where assignment = 'Network Team'

Open in new window


Result

Ticket      Priority
1234      P1
2345      P2

select i.Ticket, i.Priority,	a.Type, a.Desc from incident i join Activity a
on (i.Ticket = a.Ticket_no) where assignment = 'Network Team'

Open in new window


Result

Ticket      Priority      Type      Desc
1234      P1      Logged      Ticket Logged
1234      P1      Assigned      Ticket Assigned to Network Team
1234      P1      Priority Changed      Ticket Priority Changed
2345      P2      Logged      Ticket Logged
2345      P2      Contact Changed      Contact Number Changed
2345      P2      Assigned      Ticket Assigned to Network Team

The ticket row will definitely repeat.

Below is what I want to do and facing little problem.

1. I want to write a query which will give me Total Ticket Count, Ticket counts where activity type is like Contact Changed.

Total count      Contact changed count
2                      1

2. I want to write a query which will give me the records where activity type is like Contact Changed.

Ticket      Priority      Contact Chnaged      Type                              Desc
2345      P2              Yes                             Contact Changed      Contact Number Changed

Please guide me in right direction.
0
Aks001
Asked:
Aks001
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Aks001Author Commented:
Thanks angelIII,

I am testing the examples given by you in the article and update you soon.

Please can you also check my first question and guide  me ?
0
 
Aks001Author Commented:
In the 2nd question I also want to show the desc associated with 'Contact Changed'  and the last desc for that record in a different column.

How can we achieve this ?
0
 
SharathData EngineerCommented:
Did you try these queries?
select count(i.Ticket) as Total_Count, sum(case when a.Type = 'Contact Changed' then 1 else 0 end) as Contact_Changed_Count
  from incident i 
  join Activity a
    on i.Ticket = a.Ticket_no 
 where assignment = 'Network Team'
 
 select i.Ticket, i.Priority,	a.Type, a.Desc 
   from incident i 
   join Activity a
     on (i.Ticket = a.Ticket_no) 
 where assignment = 'Network Team' and [Type] = 'Contact Changed'
 

Open in new window

0
 
Aks001Author Commented:
In the 2nd query, Can we put another column showing desc of last activity ?
Sharath_123 -- Plz can u suggest also ?
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now