question about synchronising results from 2 queries

**Michael Austin** · Jul 17 '05, 08:04 AM

Re: question about synchronising results from 2 queries

paulus4605 wrote:
[color=blue]
> Dears
> I have the following problem
>
> I’m using a query to get all the data from my database from the past
> year
>
> the second query is displaying the results by month.
> How can I match the second query with the first query in order to get
> the results from the same store on the same row?
>
> example :
> Ford got $2000,- in January and in February they earned $3000,-
> BMW got $15000 in January and $7500 in February
>
> in this example FORD AND BMW are the results from Query 1 and the
> amounts mentioned are the results of Query 2. How can I print these
> results on in the same row where I Ford or BMW is displayed?
> thanks for your coop and feedback
>
> Paul
>[/color]

without knowing your table definitions or the two queries, it is a bit difficult
to make suggestions, You can use left outer joins or "normal" joins depending
on lots of things...

table 1
id name
1 Ford
2 BMW

table2
id date amount
1 1/1/2004 1000
1 1/2/2004 1000
2 1/1/2004 500
2 1/2/2004 7000
1 2/3/2004 1000
2 2/9/2004 5000

select a.name as name, sum(b.amount) as jan,
sum(c.amount) as feb,
sum(d.amount) as mar....
from table1 a
left outer join table2 b on a.id = b.id
and extract(year from date) = '2004' and extract(MONTH from date) = '1'
left outer join table2 c on a.id = c.id
and extract(year from date) = '2004' and extract(MONTH from date) = '2'
left outer join table2 d on a.id = d.id
and extract(year from date) = '2004' and extract(MONTH from date) = '3'
.....
group by name, jan, feb, mar,....;

YMMV and you may need to tweak the syntax a bit... but should produce the output

name jan feb mar apr ....
FORD 1000 5000 3000
BMW 3000 7500 .....

Then using PHP you can add the "wording" if you want...

--
Michael Austin.
Consultant - Not Available.
Donations still welcomed. Http://www.firstdbasource.com/donations.html
:)

**paulus4605** · Jul 17 '05, 08:04 AM

Re: Re: question about synchronising results from 2 queries

Michael
thanks for your reply

the queries look like this

$Year ="SELECT DISTINCT DEALER_REM FROM ELEC_REM_NOTE WHERE
CURRENT_MONTH LIKE "%2004" GROUP BY DEALER_REM";

the second QUERY = $Month =" SELECT DISTINCT DEALER_REM WHERE
CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN (1,2,3,4,5,7,8, 10,12) group by
DEALER_REM

thanks for your feedback

Paul

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-synchron...ict142196.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=476061

**Michael Austin** · Jul 17 '05, 08:04 AM

Re: question about synchronising results from 2 queries

paulus4605 wrote:
[color=blue]
> Michael
> thanks for your reply
>
> the queries look like this
>
> $Year ="SELECT DISTINCT DEALER_REM FROM ELEC_REM_NOTE WHERE
> CURRENT_MONTH LIKE "%2004" GROUP BY DEALER_REM";
>
> the second QUERY = $Month =" SELECT DISTINCT DEALER_REM WHERE
> CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN (1,2,3,4,5,7,8, 10,12) group by
> DEALER_REM[/color]

and what column (field) contains the Amount information - you are not selecting
it... You have not selected it. The table that contains the dealer id should be
unique and therefore not require a DISTINCT clause. the 2nd query also does not
contain a table name.

show me the output of the describe for table1 and table2.

[color=blue]
>
>
>[/color]
[color=blue]
> thanks for your feedback[/color]

You're welcome...[color=blue]
>
> Paul
>[/color]

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

**paulus4605** · Jul 17 '05, 08:15 AM

Re: Re: question about synchronising results from 2 queries

"Michael Austin" wrote:[color=blue]
> paulus4605 wrote:
>
> > Michael
> > thanks for your reply
> > 
> > the queries look like this 
> > 
> > $Year ="SELECT DISTINCT DEALER_REM
> FROM ELEC_REM_NOTE WHERE
> > CURRENT_MONTH LIKE "%2004" GROUP BY
> DEALER_REM";
> > 
> > the second QUERY = $Month =" SELECT
> DISTINCT DEALER_REM WHERE
> > CURRENT_MONTH =’JAN2004’ AND
> BLOC_ID IN (1,2,3,4,5,7,8, 10,12) group by
> > DEALER_REM
>
> and what column (field) contains the Amount information - you
> are not selecting
> it... You have not selected it. The table that contains the
> dealer id should be
> unique and therefore not require a DISTINCT clause. the 2nd
> query also does not
> contain a table name.
>
> show me the output of the describe for table1 and table2.
>
>
> > 
> > 
> > 
>
> > thanks for your feedback
>
> You’re welcome...
> > 
> > Paul
> > 
>
>
> --
> Michael Austin.
> Consultant - Available.
> Donations welcomed.
> Http://www.firstdbasource.com/donations.html
> :)[/color]

oeps stupid of me

the both tables that I use are "FROM ELEC_REM_NOTE"

so the first query is $Year ="SELECT DISTINCT DEALER_REM FROM
ELEC_REM_NOTE WHERE CURRENT_MONTH LIKE"%2004" GROUP BY DEALER_REM";

the second query looks like this
$MONTH = "SELECT DISTINCT DEALER_REM ,SUM(TOTAL_REM_ AMOUNT) FROM
ELEC_REM_NOTE WHERE CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN
(1,2,3,4,5,7,8, 10,12) GROUP BY DEALER_REM

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-synchron...ict142196.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=476807

**paulus4605** · Jul 17 '05, 08:15 AM

Re: Re: question about synchronising results from 2 queries

"Michael Austin" wrote:[color=blue]
> paulus4605 wrote:
>
> > Michael
> > thanks for your reply
> > 
> > the queries look like this 
> > 
> > $Year ="SELECT DISTINCT DEALER_REM
> FROM ELEC_REM_NOTE WHERE
> > CURRENT_MONTH LIKE "%2004" GROUP BY
> DEALER_REM";
> > 
> > the second QUERY = $Month =" SELECT
> DISTINCT DEALER_REM WHERE
> > CURRENT_MONTH =’JAN2004’ AND
> BLOC_ID IN (1,2,3,4,5,7,8, 10,12) group by
> > DEALER_REM
>
> and what column (field) contains the Amount information - you
> are not selecting
> it... You have not selected it. The table that contains the
> dealer id should be
> unique and therefore not require a DISTINCT clause. the 2nd
> query also does not
> contain a table name.
>
> show me the output of the describe for table1 and table2.
>
>
> > 
> > 
> > 
>
> > thanks for your feedback
>
> You’re welcome...
> > 
> > Paul
> > 
>
>
> --
> Michael Austin.
> Consultant - Available.
> Donations welcomed.
> Http://www.firstdbasource.com/donations.html
> :)[/color]

oeps stupid of me

the both tables that I use are "FROM ELEC_REM_NOTE"

so the first query is $Year ="SELECT DISTINCT DEALER_REM FROM
ELEC_REM_NOTE WHERE CURRENT_MONTH LIKE"%2004" GROUP BY DEALER_REM";

the second query looks like this
$MONTH = "SELECT DISTINCT DEALER_REM ,SUM(TOTAL_REM_ AMOUNT) FROM
ELEC_REM_NOTE WHERE CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN
(1,2,3,4,5,7,8, 10,12) GROUP BY DEALER_REM

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-synchron...ict142196.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=476807

**Michael Austin** · Jul 17 '05, 08:17 AM

Re: question about synchronising results from 2 queries

paulus4605 wrote:

<snip>
[color=blue]
> so the first query is $Year ="SELECT DISTINCT DEALER_REM FROM
> ELEC_REM_NOTE WHERE CURRENT_MONTH LIKE"%2004" GROUP BY DEALER_REM";
>
> the second query looks like this
> $MONTH = "SELECT DISTINCT DEALER_REM ,SUM(TOTAL_REM_ AMOUNT) FROM
> ELEC_REM_NOTE WHERE CURRENT_MONTH =’JAN2004’ AND BLOC_ID IN
> (1,2,3,4,5,7,8, 10,12) GROUP BY DEALER_REM
>[/color]

Same as I previously described:

select a.name as name, sum(b.amount) as jan,
sum(c.amount) as feb,
sum(d.amount) as mar....
from table1 a
left outer join table1 b on a.name = b.name
and CURRENT_MONTH =’JAN2004’
AND BLOC_ID IN (1,2,3,4,5,7,8, 10,12)
left outer join table1 c on a.name = c.name
and CURRENT_MONTH =’FEB2004’
AND BLOC_ID IN (1,2,3,4,5,7,8, 10,12)
....
group by a.name, b.amount, c.amount, d.amount,,,,;

You should get the distinct names and sums for each month.

if you want to insert the actual text, then it gets a bit more conveluted and
may not fit on one line... If you are using PHP then display the results in a table

NAME JAN FEB MAR ...
FORD 2000 4000 ...
BMW 9000 3000 ...

--
Michael Austin.
Consultant -Not Available.
Donations still welcomed. Http://www.firstdbasource.com/donations.html
:)

question about synchronising results from 2 queries

question about synchronising results from 2 queries

Comment

Comment

Comment

Comment

Comment

Comment