[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Show Group Above Details Query.

Posted on 2009-04-20
17
Medium Priority
?
205 Views
Last Modified: 2012-05-06
Hi,

I have a table below:

TableDetail:

1. TrsId.
2. TrsName
3. GrpId

TableGroup:

1. GrpId
2. GrpName

Sample :

TableDetail:

TrsId    TrsName   GrpId
A1        A1             A
A2        A2             A
B1        B1             B

TableGroup:

GrpId    GrpName
A           A
B           B

I want to show :

TrsId    TrsName  
A          A
A1        A1              
A2        A2              
B          B
B1        B1              

How could I do it?

Thank you.
0
Comment
Question by:emi_sastra
  • 9
  • 8
17 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24187986
I presume you want this:
select trsid, trsname from tabledetail
union
select grpid, grpname from tablegroup

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188071
Hi angelIII,

Almost.

I just want to show the group base on GrpId at TableGroup.

Thank you.


0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188247
Sorry, should be

show the group base on GrpId at TableDetail.

Thank you.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188429
I don't understand?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188540
Suppose I want to show A1 and A2  (TrsId LIKE 'A%') then the result will be:

TrsId    TrsName  
A          A
A1        A1              
A2        A2              

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188574
that would be:
select trsid, trsname from tabledetail
where TrsId like 'A%'
union
select grpid, grpname from tablegroup
where grpid like 'A%'

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188621
Ok.

If GrpId has pattern like the above sample, what about below data (unpattern GrpId):

TableDetail:

TrsId    TrsName   GrpId
A1        A1             X
A2        A2             X
B1        B1             Y

TableGroup:

GrpId    GrpName
X           A
X           B

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188652
what should be the result data? I am still not 100% sure what the "rule" should be...
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188690
TableDetail:

TrsId    TrsName   GrpId
A1        A1             X
A2        A2             X
B1        B1             Y

TableGroup:

GrpId    GrpName
X           A
X           B

Let's say I want to show TrsId LIKE 'A%' and the GroupId and Its Name should appeared above them.

Result:

TrsId    TrsName  
X          A
A1        A1              
A2        A2              

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188750
ok, does it really have to be that layout?

should it not really better:


select g.GrpID, g.GrpName, t.TrsID, t.TrsName
from TableDetail t
join TableGroup g
  on g.GrpID = t.GrpID
where t.TrsID LIKE 'A%'

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188781
ok, does it really have to be that layout?

Yes.

Thank you.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24188836
ok, let's see:
select t.TrsID, t.TrsName
from (
 
select g.GrpID, g.GrpName, t.TrsID, t.TrsName
from TableDetail t
join TableGroup g
  on g.GrpID = t.GrpID
where t.TrsID LIKE 'A%'
union all
select g.GrpID, g.GrpName, g.GrpID, g.GrpName
from TableDetail t
join TableGroup g
  on g.GrpID = t.GrpID
where t.TrsID LIKE 'A%'
 
) sq
order by g.GrpID, g.GrpName, t.TrsID, t.TrsName

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188921
How about below:

select trsid, trsname from tabledetail
where TrsId like 'A%'
union
select grpid, grpname from tablegroup
where grpid in (select grpid from tabledetail where TrsId like 'A%')

Does it perfect and has the same result with yours?

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188969
yes, sometimes you can get the same results with different queries...
you might consider using union all , though instead of union.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24188977
you might consider using union all , though instead of union.
Why?

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24189000
because UNION does a DISTINCT implicitly, UNION ALL does not.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24189059
Ok.

Thank you very much for your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question