• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

SQL Query Problem

Hi, Friends  I have  Problem with jonning table

Table 1  ProcMast
Field  Name
MastCode     Number
MastName   Text
Depcode        Text  
ProdCode    Number

Table 2 SubProc

Field Name
ProcCode      Number  (Link with ProcMast.MastCode)
MastCode     Number
MastName   Text


I am using bellow query but it’s give
SELECT ProcMast.MastName, SubProc.MastName, ProcMast.MastCode
FROM ProcMast INNER JOIN SubProc ON ProcMast.MastCode=SubProc.ProcCode
ORDER BY ProcMast.MastCode;

ProcMast.MastName       Subproc.MastName
Machine Maintenance    Machine maintenance
Machine Maintenance     Machine Oiling      
Adhesive Application      Sheet Removing
Adhesive Application      Sheet Feeding
Adhesive Application      Sheet Cutting
Adhesive Application      Adhesive Roll Cutting

I want Result  given bellow

ProcMast.MastName       Subproc.MastName
Machine Maintenance     Machine maintenance
                         Machine Oiling      
Adhesive Application      Sheet Removing
                                        Sheet Feeding
                                        Sheet Cutting
                                        Adhesive Roll Cutting
0
brijesh2703
Asked:
brijesh2703
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please clarify if you have ms access or ms sql server 2005.

with sql 2005, you could do it, but this is normally rather a presentation issue, something that you should not solve with sql
;with data as ( 
SELECT ProcMast.MastName, SubProc.MastName SubName, ProcMast.MastCode
  , ROW_NUMBER() OVER (PARTITION BY ProcMast.MastName ORDER BY SubProc.MastName) rn
FROM ProcMast INNER JOIN SubProc ON ProcMast.MastCode=SubProc.ProcCode
ORDER BY ProcMast.MastCode
)
select case when rn = 1 then MastName else null end MastName
   , SubName
   , MastCode
  from data
order by data.MastName, rn

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
shouldn't this be:
INNER JOIN SubProc ON ProcMast.MastCode=SubProc.Mastcode
0
 
brijesh2703Author Commented:
i am using MS Access2007 in intranet  
on internet ms sql server 2005
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
You will not be able to get that output from the SQL statement.  However, if you create a report from this query, you can supress the redundent ProcMast.MastName values.

If you create the query, then use the report wizard to create your report, it will ask for the Layout, choose Block.  That will display the left column once for each new value, and all of the values from the right column.  You can also export this report to Excel.
0
 
brijesh2703Author Commented:
In  all table mastcode is Primary key for that table
0
 
brijesh2703Author Commented:
no iam using this for my webpage with listview control
0
 
JoeNuvoCommented:
SELECT
	CASE RN WHEN 1 THEN PM_MastName ELSE '' END AS PM_MastName,
	SP_MastName, PM_MastCode
FROM 
(
	SELECT
		ProcMast.MastName AS PM_MastName, SubProc.MastName AS SP_MastName, ProcMast.MastCode AS PM_MastCode,
		ROW_NUMBER() OVER(PARTITION BY ProcMast.MastCode ORDER BY SubProc.MastName) AS RN
	FROM ProcMast INNER JOIN SubProc
	ON ProcMast.MastCode = SubProc.ProcCode
) SubQuery
ORDER BY SubQuery.PM_MastName, SubQuery.SP_MastName

Open in new window

0
 
JoeNuvoCommented:
you also can use answer from angelIII

just have to remove line 5
(and maybe change null on line 7, if you preferred blank value)
0
 
brijesh2703Author Commented:
Thank you very much all of you given for your Precious Time for me.
Good solution and support
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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