Solved

SQL Query Problem

Posted on 2011-02-18
9
228 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now