Solved

SQL Query Problem

Posted on 2011-02-18
9
230 Views
Last Modified: 2012-05-11
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
Comment
Question by:brijesh2703
9 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 34927195
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34927211
shouldn't this be:
INNER JOIN SubProc ON ProcMast.MastCode=SubProc.Mastcode
0
 

Author Comment

by:brijesh2703
ID: 34927243
i am using MS Access2007 in intranet  
on internet ms sql server 2005
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 34927260
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
 

Author Comment

by:brijesh2703
ID: 34927270
In  all table mastcode is Primary key for that table
0
 

Author Comment

by:brijesh2703
ID: 34927292
no iam using this for my webpage with listview control
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 400 total points
ID: 34927426
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
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 400 total points
ID: 34927450
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
 

Author Closing Comment

by:brijesh2703
ID: 34931677
Thank you very much all of you given for your Precious Time for me.
Good solution and support
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 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