Odd Query 1

[Solo4357]

I have to get some records out of the database for a text file for import. The fields are Jobcode, jobphase, and jobname, jobphasename. However the format needs to be:
for the first record of the job:
Jobcode Blank jobname
and subsequent records
jobcode jobphase jobphasename

so for the results would look similiar to:
Jobcode,Phase,Job or Jobphase name
100000,,Install Job
100000,0100,Lights
100000,0200,switches
100000,0300,rentals
100001,,RepairJob
100001,0100,Lights
100001,0200,switches
100001,0300,ServiceLabor


Any ideas?

 

[carp]

Well, right off the top (no testing yet), maybe something like:

SELECT * FROM 
(SELECT jobcode, NULL as phase, jobphasename FROM my_table
 UNION ALL
 SELECT jobcode, phase, jobphasename FROM my_table)
ORDER BY jobcode, phase;

 

[Solo4357]

Oo that almost did it. Here's my changes:

SELECT * FROM (SELECT jobcode, NULL as phase, jobphasename FROM my_table UNION ALL SELECT jobcode, phase, jobphasename FROM my_table)
Group By Jobcode, Phase,JobPhasename
ORDER BY jobcode, phase;

Otherwise I got a name record for each phase record that I didn't need. Thank you so much!

 

[carp]

Oh - they need to be DISTINCT? Then you could also try

SELECT DISTINCT * FROM 
(SELECT jobcode, NULL as phase, jobphasename FROM my_table UNION ALL SELECT jobcode, phase, jobphasename FROM my_table)
ORDER BY jobcode, phase;

I'm not sure if you'll see a performance difference or not - my guess is it will be marginal if present at all. However, it DOES make your code one line shorter, which might make it a little easier to read.

 

[Solo4357]

True enough. Since I only need it to do a one time data dump, 6 to one-half dozen to the other. Either way, it's getting the job done. Many thanks.