Solved

SQL Query Problem

Posted on 2011-02-18
9
234 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 48

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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