0

I have 3 tables. Namely Job_Master,Print_details,Paper_Details.

The Structure of the table is as follows:

Job_Master:

jobno jobname amount

1 A 100
2 B 200
3 C 300

Print_Details id being the PK and jobno being FK

id jobno color

1 1 Cyan
2 1 Red
3 2 Black
4 3 Black
5 3 Green

Paper Details id being the PK and jobno being FK

id jobno Type

1 1 Art Paper
2 1 Photo Paper
3 2 Art Paper
4 3 Copier
5 3 Glossy Paper

I want a write a query in SQL server or perform Dataset operations in ASP.net so as to display the below resultset in the grid view:

Desired Resultset:

jobno jobname printDetails                    paperDetails                  amount

1     A       CYAN,RED                        Art Paper,Photo Paper         100
2     B       Black                           Art Paper                     200
3     C       Black,Green                     Copier,Glossy Paper           300

is this possible using dataset operations in ASP.net or in a SQL server query. I am using SQL server 2008 R2 as my database and the fromt end has been designed using ASP.net3.5.

Any help on this is much appreciated.Thanks in advance.

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
user1534206
  • 1
  • i have stored the result of the below query in the data set SELECT a.Jobno, a.jobname, b.color,c.type FROM job_master a left JOIN cprint_details b ON a.jobno=b.jobno left join paper_details c on a.jobno=c.jobno ORDER BY a.jobno but it does not give me a the desired result. how to combine the color and type columns in resultset based on the jobno to get a single row. – user1534206 Jul 18 '12 at 09:50

1 Answers1

1

Try this

SELECT DISTINCT a.Jobno, a.jobname,  COALESCE(b.color + ', ', '') + b.color,COALESCE(c.type + ', ', '') + c.type
FROM job_master a left JOIN cprint_details b ON a.jobno=b.jobno 
left join paper_details c on a.jobno=c.jobno 
ORDER BY a.jobno

Read here for more info LINK

Community
  • 1
  • 1
Prince Jea
  • 5,524
  • 7
  • 28
  • 46