Joining 2 select statement with like fields

20 Jul 2006 11:12 PM

Stopher

Hi All,

I have 2 select statement that return 2 sets of data.

The tables have one field with like results.

What I am trying to do is list the like field on the left and the 2
sets of results beside each other on the same data set ie.

select Results 1

Name | Result1

select Result 2

Name | Result 2

Desired outcome

Name | Result1 | Result2

Regards

Stopher

21 Jul 2006 12:35 AM

Arnie Rowland

To JOIN the 2 tables, you would use something like this...

SELECT
     t1.[Name]
   , t1.Result1
   , t2.Result2
FROM MyFirstTable t1
   JOIN MySecondTable t2
      ON t1.[Name] = t2.[Name]

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Stopher" <chris.sommervi***@zinifex.com> wrote in message news:1153437172.168000.209390@i42g2000cwa.googlegroups.com...
> Hi All,
>
> I have 2 select statement that return 2 sets of data.
>
> The tables have one field with like results.
>
> What I am trying to do is list the like field on the left and the 2
> sets of results beside each other on the same data set ie.
>
> select Results 1
>
> Name | Result1
>
> select Result 2
>
> Name | Result 2
>
> Desired outcome
>
> Name | Result1 | Result2
>
> Regards
>
> Stopher
>

22 Jul 2006 12:28 AM

Stopher

select sample_day, sum(cnt) from (

select
convert(varchar, convert(varchar,year(AUDITJOB.AUDITDATE))
+ '-' + right('0' + convert(varchar,month(AUDITJOB.AUDITDATE)), 2)
+ '-' + right('0' + convert(varchar, Day(AUDITJOB.AUDITDATE)), 2)) as
'Sample_day',

count(*) as cnt from Auditjob where sch_code = 'XRF1'
        and ANALYTECODE = 'fluwwt'
        and Action = 'ovw'
        and description = 'Sample numeric result overwritten'
        and Auditdate >= convert(DateTime, '20/07/2006' + ' 00:00:00')

        and Auditdate <= convert(DateTime, '22/07/2006' + ' 23:59:59')
        and staff_code like '%'

group by   convert(varchar, convert(varchar,year(AUDITJOB.AUDITDATE))

+ '-' + right('0' + convert(varchar,month(AUDITJOB.AUDITDATE)), 2)
+ '-' + right('0' + convert(varchar, Day(AUDITJOB.AUDITDATE)), 2))

union

select
convert(varchar,
convert(varchar,year(ce_cclasel_archive.dbo.Auditjob.Auditdate))
+ '-' + right('0' +
convert(varchar,month(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2)
+ '-' + right('0' + convert(varchar,
Day(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2)) as 'Sample_day',

count(*) as cnt from ce_cclasel_archive.dbo.Auditjob where
ce_cclasel_archive.dbo.Auditjob.sch_code = 'XRF1'
        and ce_cclasel_archive.dbo.Auditjob.ANALYTECODE = 'fluwwt'
        and ce_cclasel_archive.dbo.Auditjob.Action = 'ovw'
        and ce_cclasel_archive.dbo.Auditjob.description = 'Sample
numeric result overwritten'
        and ce_cclasel_archive.dbo.Auditjob.Auditdate >=
convert(DateTime, '20/07/2006' + ' 00:00:00')
        and ce_cclasel_archive.dbo.Auditjob.Auditdate <=
convert(DateTime, '22/07/2006' + ' 23:59:59')
        and ce_cclasel_archive.dbo.Auditjob.staff_code like '%'

group by   convert(varchar,
convert(varchar,year(ce_cclasel_archive.dbo.Auditjob.Auditdate))
+ '-' + right('0' +
convert(varchar,month(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2)
+ '-' + right('0' + convert(varchar,
Day(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2))) as cnt

group by sample_day

order by sample_day desc

This is my select statement, I want to join 2 of these together.

The above solution gives me an invalid object name on "cnt"

Any other suggestions

22 Jul 2006 3:03 AM

Arnie Rowland

One thing I noticed: when you do a union, it is only necessary to 'name'
derived columns in the first SELECT statement.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Stopher" <chris.sommervi***@zinifex.com> wrote in message
news:1153528118.466973.289060@i3g2000cwc.googlegroups.com...
> select sample_day, sum(cnt) from (
>
> select
> convert(varchar, convert(varchar,year(AUDITJOB.AUDITDATE))
> + '-' + right('0' + convert(varchar,month(AUDITJOB.AUDITDATE)), 2)
> + '-' + right('0' + convert(varchar, Day(AUDITJOB.AUDITDATE)), 2)) as
> 'Sample_day',
>
> count(*) as cnt from Auditjob where sch_code = 'XRF1'
>        and ANALYTECODE = 'fluwwt'
>        and Action = 'ovw'
>        and description = 'Sample numeric result overwritten'
>        and Auditdate >= convert(DateTime, '20/07/2006' + ' 00:00:00')
>
>        and Auditdate <= convert(DateTime, '22/07/2006' + ' 23:59:59')
>        and staff_code like '%'
>
> group by   convert(varchar, convert(varchar,year(AUDITJOB.AUDITDATE))
>
> + '-' + right('0' + convert(varchar,month(AUDITJOB.AUDITDATE)), 2)
> + '-' + right('0' + convert(varchar, Day(AUDITJOB.AUDITDATE)), 2))
>
> union
>
> select
> convert(varchar,
> convert(varchar,year(ce_cclasel_archive.dbo.Auditjob.Auditdate))
> + '-' + right('0' +
> convert(varchar,month(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2)
> + '-' + right('0' + convert(varchar,
> Day(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2)) as 'Sample_day',
>
> count(*) as cnt from ce_cclasel_archive.dbo.Auditjob where
> ce_cclasel_archive.dbo.Auditjob.sch_code = 'XRF1'
>        and ce_cclasel_archive.dbo.Auditjob.ANALYTECODE = 'fluwwt'
>        and ce_cclasel_archive.dbo.Auditjob.Action = 'ovw'
>        and ce_cclasel_archive.dbo.Auditjob.description = 'Sample
> numeric result overwritten'
>        and ce_cclasel_archive.dbo.Auditjob.Auditdate >=
> convert(DateTime, '20/07/2006' + ' 00:00:00')
>        and ce_cclasel_archive.dbo.Auditjob.Auditdate <=
> convert(DateTime, '22/07/2006' + ' 23:59:59')
>        and ce_cclasel_archive.dbo.Auditjob.staff_code like '%'
>
> group by   convert(varchar,
> convert(varchar,year(ce_cclasel_archive.dbo.Auditjob.Auditdate))
> + '-' + right('0' +
> convert(varchar,month(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2)
> + '-' + right('0' + convert(varchar,
> Day(ce_cclasel_archive.dbo.Auditjob.Auditdate)), 2))) as cnt
>
> group by sample_day
>
> order by sample_day desc
>
> This is my select statement, I want to join 2 of these together.
>
> The above solution gives me an invalid object name on "cnt"
>
> Any other suggestions
>

22 Jul 2006 5:36 AM

Stopher

So any suggestions to join 2 of these selects together so that the
first select goes in the first result column (Result1) and a second
Result (result2) goes in a column beside it and the Sample_day is the
common field on the left (Name)??

23 Jul 2006 3:19 PM

Hugo Kornelis

On 21 Jul 2006 17:28:38 -0700, Stopher wrote:

(snip)

>This is my select statement, I want to join 2 of these together.
>
>The above solution gives me an invalid object name on "cnt"
>
>Any other suggestions

Hi Stopher,

So you want just the total number of rows from both tables combined, in
one single column?? No need for a JOIN, then. Try this query (note that
I also optimized the date handling and removed some unneeded code).

SELECT   CONVERT(char(10), AuditDate, 120) AS Sample_day,
         COUNT(*) AS cnt
FROM    (SELECT AuditDate, Sch_code, AnalyteCode,
                Action, Description, Staff_Code
         FROM   Auditjob
         UNION ALL
         SELECT AuditDate, Sch_code, AnalyteCode,
                Action, Description, Staff_Code
         FROM   ce_cclasel_archive.dbo.Auditjob) AS der
WHERE    Sch_Code = 'XRF1'
AND      AnalyteCode = 'fluwwt'
AND      Action = 'ovw'
AND      Description = 'Sample numeric result overwritten'
AND      Auditdate >= '20060720'
AND      Auditdate < '20060723'
AND      Staff_Code IS NOT NULL
GROUP BY CONVERT(char(10), AuditDate, 120);

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP

4 Aug 2006 10:19 PM

Stopher

Hi Hugo,

Thnx for the cut down mumbo jumbo code from above.

This gives me the same as the code above, what I need is for 2 of these
joined together so that the Sample_day is the left column, and results
from 2 of these selects are displayed beside each other.

So above query gives Sample_day | Result1

Next query with slightly different where gives Sample_day | Result2

and the end table looks like Sample_day | Result 1 | Result 2

Regards

Stopher

18 Aug 2006 9:40 PM

Hugo Kornelis

On 4 Aug 2006 15:19:34 -0700, Stopher wrote:

>Hi Hugo,
>
>Thnx for the cut down mumbo jumbo code from above.

Hi Stopher,

You're welcome. :-)

And apologies for the delayed reply - I was on a holiday, enjoying life
without the Internet.

>This gives me the same as the code above, what I need is for 2 of these
>joined together so that the Sample_day is the left column, and results
>from 2 of these selects are displayed beside each other.
>
>So above query gives Sample_day | Result1
>
>Next query with slightly different where gives Sample_day | Result2
>
>and the end table looks like Sample_day | Result 1 | Result 2

Well, it's a pity that you didn't visit www.aspfaq.com/5006 like I
suggested in my previous reply, since it's lots easier to provide a
query if I know the structure of the tables, have some sample data to
test my query on and expected output to compare the results to.

Based on "what I need is for 2 of these joined together", here's the
rough outline of a solution that will probably work, but is also likely
to perform less than optimal:

SELECT      a.Sample_day, a.Result1, b.Result2
FROM       (first one of these) AS a
INNER JOIN (other one of these) AS b
      ON    a.Sample_day = b.Sample_day

--
Hugo Kornelis, SQL Server MVP

19 Aug 2006 1:43 AM

Stopher

Yer i figured it out, but thnx for the responce.

Stopher