?
Solved

SQL Query Problem

Posted on 2011-02-18
9
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 48

Expert Comment

by:Dale Fye
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 1600 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 1600 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

752 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