insert query question

Hi I have two temporary tables in a query and need to combine them as
described below. Thanks.
Table 1-results from query 1 based on a start and end date
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
Table 2 list of all names
***************************
* name * location * Cost * Type *
***************************
*cat * AZ * $2.00 * animal *
***************************
*hamer *Texas *$1.0 *tool *
***************************
*candy *calif *.50 * food *
****************************
table 2 lists all of the named items. I would like to insert records from
table2 into table 1 in a fashion that will leave table 2 with all of the
named items for each date, as shown below. It does not write over what is in
table one but inserts records so all named items show up for every day.
Table1 after updated
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
table2
**********************************************
*2/1/07* calif * food * .50 * candy *NULL *NULL *from
table2
**********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
***********************************************
*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
***********************************************
--
Paul G
Software engineer.Without knowing really basic stuff like keys it is pure guesswork
trying to write a query.
Something like this might get you started. Or not. The general idea
is to use a CROSS JOIN of the dates against the names to get the set
of rows you want in the results, then join that result to the detail
to fill in the rest.
SELECT A.day, B.location, B.type, B.cost, B.Name,
C.color, C.weight
FROM (SELECT DISTINCT day FROM Tbl1) as A
CROSS JOIN
Tbl2 as B
LEFT OUTER
JOIN Tbl1 as C
ON A.day = C.day
AND A.name = C.name
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 13:36:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have two temporary tables in a query and need to combine them as
>described below. Thanks.
>Table 1-results from query 1 based on a start and end date
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>Table 2 list of all names
>***************************
>* name * location * Cost * Type *
>***************************
>*cat * AZ * $2.00 * animal *
>***************************
>*hamer *Texas *$1.0 *tool *
>***************************
>*candy *calif *.50 * food *
>****************************
>table 2 lists all of the named items. I would like to insert records from
>table2 into table 1 in a fashion that will leave table 2 with all of the
>named items for each date, as shown below. It does not write over what is in
>table one but inserts records so all named items show up for every day.
>Table1 after updated
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
>table2
>**********************************************
>*2/1/07* calif * food * .50 * candy *NULL *NULL *from
>table2
>**********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
>***********************************************
>*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
>***********************************************|||thanks for the response. I left off the key column. Table 1 it is
**************************************************
day_id prim key * day (datetime)* type,name cost are all (varchar(20))
and table2 is
***************************************************
name_id prim key * location cost type are all (varchar(20)).
--
I will try what you have provided.
Paul G
Software engineer.
"Roy Harvey" wrote:
> Without knowing really basic stuff like keys it is pure guesswork
> trying to write a query.
> Something like this might get you started. Or not. The general idea
> is to use a CROSS JOIN of the dates against the names to get the set
> of rows you want in the results, then join that result to the detail
> to fill in the rest.
> SELECT A.day, B.location, B.type, B.cost, B.Name,
> C.color, C.weight
> FROM (SELECT DISTINCT day FROM Tbl1) as A
> CROSS JOIN
> Tbl2 as B
> LEFT OUTER
> JOIN Tbl1 as C
> ON A.day = C.day
> AND A.name = C.name
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 20 Aug 2007 13:36:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have two temporary tables in a query and need to combine them as
> >described below. Thanks.
> >
> >Table 1-results from query 1 based on a start and end date
> >
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >Table 2 list of all names
> >***************************
> >* name * location * Cost * Type *
> >***************************
> >*cat * AZ * $2.00 * animal *
> >***************************
> >*hamer *Texas *$1.0 *tool *
> >***************************
> >*candy *calif *.50 * food *
> >****************************
> >table 2 lists all of the named items. I would like to insert records from
> >table2 into table 1 in a fashion that will leave table 2 with all of the
> >named items for each date, as shown below. It does not write over what is in
> >table one but inserts records so all named items show up for every day.
> >Table1 after updated
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
> >table2
> >**********************************************
> >*2/1/07* calif * food * .50 * candy *NULL *NULL *from
> >table2
> >**********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
> >***********************************************
> >*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
> >
> >***********************************************
>