Tricky SQL statement 1

[happykiran]

Hi,
I got these two tables in the database. Now I want the output from activity_log. The cfrom and cto refer to routeid in 'routeprefix' table. If I want to view the names of the routes instead of their IDs in the activity table, how should I frame my SQL statement ?

mysql> select * from activity_log;
+----+-------+---------------------+-------+-----+
| id | user | action_date | cfrom | cto |
+----+-------+---------------------+-------+-----+
| 56 | Kiran | 2004-05-03 22:48:25 | 2 | 1 |
| 57 | Kiran | 2004-05-03 22:48:25 | 2 | 1 |
| 58 | Kiran | 2004-05-03 22:48:25 | 2 | 1 |
| 59 | Kiran | 2004-05-04 12:02:25 | 2 | 2 |
| 60 | Kiran | 2004-05-05 11:58:45 | 2 | 4 |
| 61 | Kiran | 2004-05-05 11:58:45 | 2 | 4 |
| 62 | Kiran | 2004-05-05 11:58:45 | 1 | 4 |
| 63 | Kiran | 2004-05-05 11:58:45 | 2 | 4 |
| 64 | Kiran | 2004-05-05 11:58:45 | 2 | 4 |
| 65 | Kiran | 2004-05-05 11:58:45 | 2 | 4 |
+----+-------+---------------------+-------+-----+

mysql> select * from routeprefix;
+---------+-----------+--------+
| routeid | route | prefix |
+---------+-----------+--------+
| 1 | A | 00 |
| 2 | B | 54 |
| 3 | C | 11 |
| 4 | D | 12 |
+---------+-----------+--------+

expected output
---------------
+----+-------+---------------------+-------+-----+
| id | user | action_date | cfrom | cto |
+----+-------+---------------------+-------+-----+
| 56 | Kiran | 2004-05-03 22:48:25 | B | A |
| 57 | Kiran | 2004-05-03 22:48:25 | B | A |
| 58 | Kiran | 2004-05-03 22:48:25 | B | A |
| 59 | Kiran | 2004-05-04 12:02:25 | B | B |
| 60 | Kiran | 2004-05-05 11:58:45 | B | D |
| 61 | Kiran | 2004-05-05 11:58:45 | B | D |
| 62 | Kiran | 2004-05-05 11:58:45 | A | D |
| 63 | Kiran | 2004-05-05 11:58:45 | B | D |
| 64 | Kiran | 2004-05-05 11:58:45 | B | D |
| 65 | Kiran | 2004-05-05 11:58:45 | B | D |
+----+-------+---------------------+-------+-----+

 

[swampBoogie]

select r1.route as cfrom, r2.route as cto
from route as r1 inner join activity_log as al
on r1.routeid = al.cto inner join route as r2
on al.cfrom = r2.routeid

 

[happykiran]

Thanks,

That worked. Also I tried the same without inner query, just by refering to routeprefix table as two tables with different aliases. Even that worked, the query I used is ..

select r1.route as cfrom, r2.route as cto
from activity_log a, routeprefix r1, routeprefix r2
where a.cfrom=r1.routeid and a.cto=r2.routeid;

Kiran.

 

[Alihaq786]

Dear All, please help
I have written this query

SELECT code,
IF(location = 'PICKING', quant, '') AS "Picking" ,
IF(location = 'LONDON', quant, '') AS "London",
IF(location = 'MANC', quant, '') AS "Manchester",
IF(location = 'BIRM', quant, '') AS "Birmingham",
IF(location = 'BRISTOL', quant, '') AS "Bristol"
FROM newsales

And the output is

code Picking London Manchester Birmingham Bristol
---------------------------------------------------------
A121 5
A121 9
A121 10
A121 12
A122 7
A122 11

and so on

The out put is half right, i.e it gives all the values but in new line. i want it to be in same line i.e

code Picking London Manchester Birmingham Bristol

A121 5 9 10 12


Thanking in advance

 

[TonyGroves]

Ali -

This is a new thread; it's posted in the wrong place.

However, how about something like:
[tt]
SELECT
code,
SUM(IF(location = 'PICKING', quant,0)) Picking,
SUM(IF(location = 'LONDON', quant,0)) London,
SUM(IF(location = 'MANC', quant,0)) Manchester,
SUM(IF(location = 'BIRM', quant,0)) Birmingham,
SUM(IF(location = 'BRISTOL', quant,0)) Bristol
FROM newsales
GROUP BY code
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[Alihaq786]

Dear Tony Groves, Thanks a lot. It worked. you are genius.
May you be successful in your life and get all the happiness. Regards Ali