Re: Pulling data that could be in one of 2 tables
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Wed, 24 Oct 2007 13:31:42 -0400
Matt,
I believe that you are looking for a UNION, such as:
SELECT OriginalOrderNum, Column1, Column2, Column3
FROM Table1
WHERE ...
UNION ALL
SELECT OriginalOrderNum, Column1, Column2, Column3
FROM Table2
WHERE OriginalOrderNum NOT IN
(SELECT OriginalOrderNum FROM Table1)
AND ...
Read about UNION and UNION ALL since they are not identical. The above
query can then be used with whatever other reporting criteria you have.
RLF
"Matt M" <MattM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:84AF08D4-F845-4412-A7BC-A429110F736E@xxxxxxxxxxxxxxxx
Note: I am using this to create a report with SRS
I need to pull some data that could possibly be sitting in one of two
tables. Table1 is the "Current" table that holds Orders that have not
shipped
yet. Once all of items on the Order have shipped the Order moves to Table2
and an invoice is created. However, there are times where only part of the
order ships so there is an Invoice placed in Table2 but the Order still
remains in Table1. The invoice also has a field that always points to the
original Order number. And Table1 and Table2 have the same exact fields as
each other.
Here is what I need to do. I need to go through all of the Invoices in
Table2 for a certain time period, and Group them by Order Type (Which is
stored in the Order record). The problem is, if the order does not exist
in
Table 2 yet, I need to get the Order type from Table1. I am sure how
exactly
to do this. Is it something in the SQL query like an If or Case Statement?
Then I though about looking into Stored Procedures but I dont know much
about
using them, especially in SRS so I would need some pointers.
Maybe something like:
Select *, If(Table1.OriginalOrderNum in Table2 then Table2.OrdType else
Table1.Ordtype)
From Table1
Where....
I know that is totally incorrect but I hope it clarifies things.
Once I have that squared away, I just need to sum up the Invoice amounts
in
each Order Type Group, but that should be easy enough if I can figure out
the
first part.
Was that clear enough? Let me know if you need any clarification.
.
- Follow-Ups:
- Re: Pulling data that could be in one of 2 tables
- From: Matt M
- Re: Pulling data that could be in one of 2 tables
- Prev by Date: Re: Quarterly Data Syntax
- Next by Date: Re: Pulling data that could be in one of 2 tables
- Previous by thread: Re: Quarterly Data Syntax
- Next by thread: Re: Pulling data that could be in one of 2 tables
- Index(es):
Relevant Pages
|
|
