question about synchronising results from 2 queries

[paulus4605]

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

--
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=475576

[Michael Austin]

paulus4605 wrote:

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

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]

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]

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
:)

[paulus4605]

"Michael Austin" wrote:

paulus4605 wrote:

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

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.
<font color=purple>*> </font>
<font color=purple>*> </font>
<font color=purple>*> </font>

<font color=purple>*> thanks for your feedback</font>

You’re welcome...
<font color=purple>*> </font>
<font color=purple>*> Paul</font>
<font color=purple>*> </font>
--
Michael Austin.
Consultant - Available.
Donations welcomed.
Http://www.firstdbasource.com/donations.html
:)

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]

"Michael Austin" wrote:

paulus4605 wrote:

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

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.
<font color=purple>*> </font>
<font color=purple>*> </font>
<font color=purple>*> </font>

<font color=purple>*> thanks for your feedback</font>

You’re welcome...
<font color=purple>*> </font>
<font color=purple>*> Paul</font>
<font color=purple>*> </font>
--
Michael Austin.
Consultant - Available.
Donations welcomed.
Http://www.firstdbasource.com/donations.html
:)

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]

paulus4605 wrote:

<snip>

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

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
:)