0

Hi There,

I have 2 tables, one with dst codes and one with fees. I want to match the fees with the dst code. I've been trying a for loop but it's not working 100%.

What is the best way of matching dst code with the corresponding fee? If TABLE A (dst_code 011) matches TABLE B's (fee 011) then the cost will be 0.38 etc...

[U]TABLE A[/U]
dst_code 011
dst code 012
dst_code 013
 
[U]TABLE B[/U]
fee 011 0.38
fee 012 0.55
fee 013 0.55

Kind Regards

3
Contributors
18
Replies
23
Views
6 Years
Discussion Span
Last Post by LRNPHP
Featured Replies
  • 1) what are columns name of both tables? 2) Is you table 2 holding value as 'fee 011' (will it always have fee prefix in all records) or table 2 is holding 011, 012 like that Read More

  • [CODE]SELECT * FROM billing order by tbl_code SELECT * FROM fees order by tbl_fee [/CODE] You run this 2 queries separately and post both exact results here (few top rows) Read More

  • 2

    If you add an area code as a foreign key to billing table then this query might help you: [CODE]SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM `fees` RIGHT JOIN billing ON `tbl_code`=`tbl_code_fk`[/CODE] Note that table names are omitted from the query for simplicity. Also a field name for date is maybe not … Read More

  • 2

    Apart from slight size increase you will get some redundancy since area code is stored in two fields (not a problem if area codes do not change, but you never know - from this perspective the tbl_id is better candidate for a foreign key) Your destination number could be without … Read More

  • Following query may work but this may cause to slow down performance. [CODE]SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM billing left JOIN `fees` ON substring(destination,1,length(tbl_code)) =tbl_code[/CODE] Read More

1

1) what are columns name of both tables?
2) Is you table 2 holding value as 'fee 011' (will it always have fee prefix in all records) or table 2 is holding 011, 012 like that

0

Introduce a foreign key in Table B which then have two fields: dst_code_fk with values 011, 012, 013... and fee with values 0.38, 0.55, 0.55... Then use join and if you want to find out a fee for dst_code 011 do it like this:

SELECT table_a.fee FROM table_a LEFT JOIN table_b ON table_a.dst_code=table_b.dst_code_fk WHERE table_a.dst_code='011'

The join type (left, right, inner, outer) depends of structure of your data. There are some nice examples of joins on the web.

0

1) what are columns name of both tables?
2) Is you table 2 holding value as 'fee 011' (will it always have fee prefix in all records) or table 2 is holding 011, 012 like that

Hi There,

1. table A name = billing
   table B name = fees

2. tbl_code, Field (This is the primary TABLE where all the data is stored)
        011
        012
        013

   tbl_fee, tbl_cost Field (This is a TABLE I created to calculate my Cost)
        011, 0.38
        012, 0,55
        013, 0,55

Hope this makes more sense. Thanks for you assistance.

Kind Regards

0

I hope this is what you are looking for:

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011'
0

I hope this is what you are looking for:

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011'

Hi Broj1,

When I run your command it show the same result the whole time.

Kind Regards

0

When I run your command it show the same result the whole time.

What do you mean by that, The query is perfect.

0

I hope this is what you are looking for:

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011'

Hi Broj1,

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011' limit 0,10; 

This is my output when running your MySQL query.

+---------------------+------+---------+---------+
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
+---------------------+------+---------+---------+

Thanks for everyone's assistance.

Kind Regards

0

What do you mean by that, The query is perfect.

Hi Urtrivedi,

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011' limit 0,10; 

This is my output when running your MySQL query.
+---------------------+------+---------+---------+
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
+---------------------+------+---------+---------+
1
SELECT * FROM billing order by tbl_code
SELECT * FROM  fees order by tbl_fee

You run this 2 queries separately and post both exact results here (few top rows)

Edited by urtrivedi: n/a

0
SELECT * FROM billing order by tbl_code
SELECT * FROM  fees order by tbl_fee

You run this 2 queries separately and post both exact results here (few top rows)

Hi Urtrivedi,

select * from billing limit 0,10;
+---------------------+-------------+---------+
| date                | destination | callsec |
+---------------------+-------------+---------+
| 2011-08-17 07:56:16 | 0116895596  |     253 |
| 2011-08-17 08:14:48 | 0124215665  |      61 |
| 2011-08-17 08:16:59 | 0116904299  |       7 |
| 2011-08-17 08:15:54 | 01144778000 |     112 |
| 2011-08-17 08:19:44 | 0124215665  |      19 |
| 2011-08-17 08:17:37 | 0152961509  |     185 |
| 2011-08-17 08:20:01 | 0136933833  |     228 |
| 2011-08-17 08:28:26 | 0118004607  |       0 |
| 2011-08-17 08:37:47 | 01242153    |       0 |
| 2011-08-17 08:38:53 | 0178014749  |       5 |
+---------------------+-------------+---------+
10 rows in set (0.00 sec)

select * from fees limit 0,10;
+--------+---------------+----------+----------+
| tbl_id | tbl_area      | tbl_code | tbl_cost |
+--------+---------------+----------+----------+
|   1269 | Aberdeen      |  049     |  0.55    |
|   1270 | Aberfeldy     |  058     |  0.55    |
|   1271 | Acornhoek     |  013     |  0.55    |
|   1272 | Addo          |  042     |  0.55    |
|   1273 | Adelaide      |  046     |  0.55    |
|   1274 | Aggeneys      |  054     |  0.55    |
|   1275 | Albertinia    |  028     |  0.55    |
|   1277 | Alettasrus    |  053922  |  0.55    |
|   1278 | Alexander Bay |  027     |  0.55    |
|   1279 | Alexandria    |  046     |  0.55    |
+--------+---------------+----------+----------+
10 rows in set (0.00 sec)

Kind Regards

0

If I get it right you have two tables for billing phone calls. The fees table has area codes and cost for each area code with tbl_id being primary key, the billing table holds data about calls made. The thing is that the tables are not linked with the same data, which means you should have a foreign key in the billing table (eg. tbl_id or tbl_code if it is unique). The area code (tbl_code) is actualy part of destination field but since it is not the same lenght in all records, you can't use a mysql SUBSTRING function to extract it. I would add a foreign key to the billing table (an area code if it is unique or tbl_id). Then the JOIN can be made.

2

If you add an area code as a foreign key to billing table then this query might help you:

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM `fees` RIGHT JOIN billing ON `tbl_code`=`tbl_code_fk`

Note that table names are omitted from the query for simplicity. Also a field name for date is maybe not a good idea since it is mysql function name and can cause errors.

0

If you add an area code as a foreign key to billing table then this query might help you:

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM `fees` RIGHT JOIN billing ON `tbl_code`=`tbl_code_fk`

Note that table names are omitted from the query for simplicity. Also a field name for date is maybe not a good idea since it is mysql function name and can cause errors.

Hi Broj1,

How will this affect my current billing table? I have more than 20 000 records in the billing table all ready.

Kind Regards

2

Apart from slight size increase you will get some redundancy since area code is stored in two fields (not a problem if area codes do not change, but you never know - from this perspective the tbl_id is better candidate for a foreign key) Your destination number could be without the area code, only the local phone number but I doubt you can change this now. You can add a foreign key with an UPDATE query so number of records should not be a problem if there arent too many area codes. You can prepare a php script to run the query.

Edited by broj1: n/a

1

Following query may work but this may cause to slow down performance.

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM  billing left JOIN `fees`  ON  substring(destination,1,length(tbl_code)) =tbl_code
0

Following query may work but this may cause to slow down performance.

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM  billing left JOIN `fees`  ON  substring(destination,1,length(tbl_code)) =tbl_code

Hi Urtrivedi,

I'm getting NULL values running your query.

+---------------------+-------------+---------+----------+-------+
| date                | destination | callsec | tbl_cost | price |
+---------------------+-------------+---------+----------+-------+
| 2011-08-16 18:20:54 | 8300        |       7 | NULL     |  NULL |
| 2011-08-16 18:21:49 | s           |       4 | NULL     |  NULL |
| 2011-08-16 18:22:00 | s           |      10 | NULL     |  NULL |
| 2011-08-16 18:25:06 | 8335        |       0 | NULL     |  NULL |
| 2011-08-16 18:25:52 | 8300        |       0 | NULL     |  NULL |
| 2011-08-16 18:31:25 | 8320        |       0 | NULL     |  NULL |
| 2011-08-16 18:42:39 | 0832301392  |       5 | NULL     |  NULL |
| 2011-08-16 18:50:06 | 0823283373  |       0 | NULL     |  NULL |
| 2011-08-16 18:54:54 | 8334        |       0 | NULL     |  NULL |
| 2011-08-16 21:54:58 | 8334        |       0 | NULL     |  NULL |
+---------------------+-------------+---------+----------+-------+
10 rows in set (0.00 sec)

Kind Regards

Edited by LRNPHP: n/a

1
SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price,
trim(substring(destination,1,length(tbl_code))) billcode , trim(tbl_code) feecode
 FROM  billing left JOIN `fees`  ON  trim(substring(destination,1,length(tbl_code))) =trim(tbl_code)

1) What is output of this query?

2) check Do you have code starting with 083 or 83 in fees table?

Edited by urtrivedi: n/a

0
SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price,
trim(substring(destination,1,length(tbl_code))) billcode , trim(tbl_code) feecode
 FROM  billing left JOIN `fees`  ON  trim(substring(destination,1,length(tbl_code))) =trim(tbl_code)

1) What is output of this query?

2) check Do you have code starting with 083 or 83 in fees table?

Hi Urtrivedi,

I had a space before and after my tbl_code, removed spaces and now everything is working 100%.
Thanks for all your and Broj1's help in assisting me today. Have a great day.

Kind Regards

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.