0

I am trying to link a few tables. In most of the tables my info is not time based. But in one of my tables it is.

In this "time based" table I need to fetch the price based on time and date—meaning the price will be "valid" after a certain time defined in the DB. So stuff like sales on a certain date could be accomplished. And this needs to work with all the LEFT JOINed tables.

The SQL that works without the time clause is below:

SELECT  
conf_mno.mcc AS mcc,
conf_mno.mnc AS mnc,
conf_mno.operator_name AS operator_name, 
conf_countries.country_prefix AS country_prefix,  
conf_countries.country_name AS country_name,
conf_mno_prefix.prefix AS operator_prefix,
conf_mno_price.currency AS currency,
conf_mno_price.price AS price

FROM conf_mno

LEFT JOIN conf_mno_prefix ON conf_mno.mcc = conf_mno_prefix.mcc AND conf_mno.mnc = conf_mno_prefix.mnc

LEFT JOIN conf_countries ON conf_mno.mcc = conf_countries.mcc

LEFT JOIN conf_mno_price ON conf_mno.mcc = conf_mno_price.mcc AND conf_mno.mnc = conf_mno_price.mnc

GROUP BY mcc, mnc, operator_name, country_prefix, country_name

However this only fetches the price which is at the top of the table. And I need it to fetch the one on the bottom by using WHERE conf_mno_price.valid_timestamp <= UTC_TIMESTAMP() and ORDER BY conf_mno_price.valid_timestamp DESC in order to get the one at the bottom.

I've tried a few approaches where I'm using a subquery of SELECT within SELECT and adding this...it does fetch the last and valid price of the first operator however it applies it to all the other operators. 

conf_countries
+-------------------------------------------+
|  mcc  |  country_prefix  |  country_name  |
+-------+------------------+----------------+
|  222  |  39              |  Italy         |
+-------------------------------------------+

conf_mno
+---------------------------------+
|  mcc  |  mnc  |  operator_name  |
+-------+-------+-----------------+
|  222  |  01   |  Telekom1       |
+---------------------------------+
|  222  |  02   |  Telekom2       |
+---------------------------------+
|  222  |  03   |  Telekom3       |
+---------------------------------+
|  222  |  04   |  Telekom4       |
+---------------------------------+
|  222  |  07   |  Telekom7       |
+---------------------------------+
|  222  |  10   |  Telekom10      |
+---------------------------------+

conf_mno_prefix
+--------------------------+
|  mcc  |  mnc  |  prefix  |
+--------------------------+
|  222  |  01   |  70      |
+--------------------------+
|  222  |  01   |  71      |
+--------------------------+
|  222  |  01   |  73      |
+--------------------------+
|  222  |  04   |  78      |
+--------------------------+
|  222  |  07   |  79      |
+--------------------------+

conf_mno_price
+-------+-------+------------+---------+-----------------------+
|  mcc  |  mnc  |  currency  |  price  |  valid_timestamp      |
+-------+-------+------------+---------+-----------------------+
|  222  |  01   |  EUR       |  0.020  |  2016-11-11 00:00:00  |
+-------+-------+------------+---------+-----------------------+
|  222  |  01   |  EUR       |  0.010  |  2016-11-20 00:00:00  |
+-------+-------+------------+---------+-----------------------+
|  222  |  01   |  EUR       |  0.015  |  2016-12-03 00:00:00  |
+-------+-------+------------+---------+-----------------------+
|  222  |  01   |  EUR       |  0.020  |  2016-12-05 00:00:00  |
+-------+-------+------------+---------+-----------------------+
|  222  |  01   |  EUR       |  0.019  |  2016-12-10 00:00:00  |
+-------+-------+------------+---------+-----------------------+
|  222  |  04   |  EUR       |  0.025  |  2016-11-21 00:00:00  |
+-------+-------+------------+---------+-----------------------+

mcc being the county id and mnc is the operator code within that country

The output should be something like this:

stdClass Object ( [mcc] => 222 [mnc] => 1 [operator_name] => Telekom1 [country_prefix] => 39 [country_name] => Italy [operator_prefix] => 70, 71, 73 [currency] => EUR [price] => 0.010)

stdClass Object ( [mcc] => 222 [mnc] => 4 [operator_name] => Telekom4 [country_prefix] => 39 [country_name] => Italy [operator_prefix] => 78 [currency] => EUR [price] => 0.025)

0.010 being the last valid price for operator 1 and 0.025 being the last valid price for operator 4 — assuming the current date is 2016-12-02 00:01:00

What would be a way to get all the info linked info like country name and operator name and prefixes of the operators as well as the latest price for each operator?

Is this even possible?

I can easily solve this by doing a second query, but as this is something which is intended to run all the time and it will be taxing on my server so trying to avoid it.

Sample data for conf_mno_price:

INSERT INTO `conf_mno_price` (`unq_id`, `mcc`, `mnc`, `currency`, `price`, `valid_timestamp`, `created_timestamp`, `modified_timestamp`) VALUES
(1, 222, 1, 'EUR', '0.01000000', '2016-08-28 22:00:00', '2016-12-03 18:52:26', '2016-12-03 18:52:26'),
(2, 222, 2, 'EUR', '0.00800000', '2016-08-28 22:00:00', '2016-12-03 18:52:57', '2016-12-03 18:52:57'),
(3, 222, 4, 'EUR', '0.01100000', '2016-08-28 23:00:00', '2016-12-03 18:59:38', '2016-12-03 18:59:38'),
(4, 222, 3, 'EUR', '0.01200000', '2016-08-28 22:00:00', '2016-12-03 18:53:07', '2016-12-03 18:53:07'),
(5, 222, 1, 'EUR', '0.01600000', '2016-09-04 22:00:00', '2016-12-03 18:54:11', '2016-12-03 18:54:11'),
(6, 222, 2, 'EUR', '0.01000000', '2016-09-04 22:00:00', '2016-12-03 18:54:27', '2016-12-03 18:54:27'),
(7, 222, 1, 'EUR', '0.00800000', '2016-09-11 22:00:00', '2016-12-03 18:55:31', '2016-12-03 18:55:31'),
(8, 222, 4, 'EUR', '0.01100000', '2016-09-18 22:00:00', '2016-12-03 18:56:17', '2016-12-03 18:56:17'),
(9, 222, 2, 'EUR', '0.00900000', '2016-09-25 22:00:00', '2016-12-03 18:56:54', '2016-12-03 18:56:54'),
(10, 222, 3, 'EUR', '0.00800000', '2016-09-25 22:00:00', '2016-12-03 18:57:08', '2016-12-03 18:57:08'),
(11, 222, 1, 'EUR', '0.00700000', '2016-10-02 22:00:00', '2016-12-03 18:57:54', '2016-12-03 18:57:54'),
(12, 222, 3, 'EUR', '0.00600000', '2016-10-30 23:00:00', '2016-12-03 19:03:12', '2016-12-03 19:03:12'),
(13, 222, 4, 'EUR', '0.01000000', '2016-10-30 23:00:00', '2016-12-03 18:59:38', '2016-12-03 18:59:38');
Nikk
  • 7,384
  • 8
  • 44
  • 90

1 Answers1

1

There's two things in your question that I believe need separate treatment, because they are both tricky.

First one is to get the prices of the latest timestamp, as opposed to the first one that occurs in the table. For this you were on the right track with grouping and subqueries, but it gets convoluted:

SELECT  
conf_mno.mcc AS mcc,
conf_mno.mnc AS mnc,
conf_mno.operator_name AS operator_name, 
conf_countries.country_prefix AS country_prefix,  
conf_countries.country_name AS country_name,
conf_mno_prefix.prefix AS operator_prefix,
t2.currency AS currency,
t2.price AS price

FROM conf_mno

LEFT JOIN conf_mno_prefix ON conf_mno.mcc = conf_mno_prefix.mcc AND conf_mno.mnc = conf_mno_prefix.mnc
LEFT JOIN conf_countries ON conf_mno.mcc = conf_countries.mcc
INNER JOIN 
   (SELECT t.mcc,t.mnc,t.currency,t.price,t.valid_timestamp from 
       (select * from conf_mno_price where valid_timestamp<=UTC_TIMESTAMP() order by valid_timestamp DESC) t 
   group by mcc,mnc) t2 ON conf_mno.mcc = t2.mcc AND conf_mno.mnc = t2.mnc 
;

What you have there is a SELECT inside a SELECT inside a JOIN.

  • The inner most SELECT orders the conf_mno_table by timestamp so that the first one is the one you want: the latest
  • The other select does the grouping in the conf_mno_table so that you obtain the list of different operators.
  • You then join this with the rest, but be careful because you where using a LEFT join and that's going to leave you with a bunch of rows that contains NULL fields for the conf_mno_prices table. You want an INNER JOIN to make sure that there are matches at both sides of the join.
+------+------+---------------+----------------+--------------+-----------------+----------+-------+
| mcc  | mnc  | operator_name | country_prefix | country_name | operator_prefix | currency | price |
+------+------+---------------+----------------+--------------+-----------------+----------+-------+
|  222 |    1 | Telekom1      |             39 | Italy        |              70 | EUR      |  0.01 |
|  222 |    1 | Telekom1      |             39 | Italy        |              71 | EUR      |  0.01 |
|  222 |    1 | Telekom1      |             39 | Italy        |              73 | EUR      |  0.01 |
|  222 |    4 | Telekom4      |             39 | Italy        |              78 | EUR      | 0.025 |
+------+------+---------------+----------------+--------------+-----------------+----------+-------+

Now as you see, you get different rows for the different operator_prefix values. I believe from your question you would like to get only two rows with the concatenation of those values. That is tricky, and if you want that, you need to look at a quite creative use of FOR XML, like in this question. If you do this, you then add the final GROUP BY that you had originally in your query.

Community
  • 1
  • 1
palako
  • 3,342
  • 2
  • 23
  • 33
  • Wow! Thank you that actually worked. Um...I'm a bit confused as to where `t` and `t2` come from? And without the `FOR XML` it wont group like it did before? And also another thing I noticed...when running it on my DB, I have a AI id as a beginning row in the prices table, when I ran this code with ID 1 and 2, the prices for operator 1 didn't take into effect. When I changed the id to 7 they worked perfectly. Do you have a clue why that would be happening? – Nikk Dec 02 '16 at 01:50
  • t and t2 are query aliases, so that you can refer to the results of the subselects as if they where tables. About the "group like it did before" where you obtaining those values concatenated as a comma separated list with your queries? I tried your queries but didn't see that happening. Sorry, I didn't understand this last question about that AI id row. maybe edit the original post or add a separate question with that? – palako Dec 02 '16 at 07:31
  • I was just adding some real data into the table and it's not working. It is only reading the first values. You can see the dataset in this **[screen shot](https://postimg.org/image/4fcjhk5d1/)** basically it is outputting the first 3 values where the blue line is (mnc 1,2 & 3). And instead it should be outputting the last 3 values where the red line is. I tried changing the `ORDER BY` to `ASC` just to see if it will make a difference inside the `INNER JOIN` but that didn't do anything. What could be causing this? – Nikk Dec 03 '16 at 18:22
  • I think I still have the schema of this from when I answered you a couple of days ago. Post a mysqldump of your data if that's not a problem for you, or at least a subset of that data that you don't mind sharing, so that I can import it into and play with the query. – palako Dec 04 '16 at 09:34
  • Posted some data for `conf_mno_price` when queried with the script, I get data from: `2016-08-28` or the very first entry for each `mnc`. @palako – Nikk Dec 04 '16 at 15:52
  • With that data I get [this result](http://pastebin.com/XjRcmXNr). The NULLs are because I don't have your operator prefixes (watch those left joins!). Is that not what you get? Is it not what you expect to get? The inner subselect returns everything in conf_mno_price, since all dates are previous to today. The outer subquery returns the group by, and you get [this records](http://pastebin.com/dnUaB9P2). – palako Dec 05 '16 at 00:46
  • No, thats not what I get...I get data with timestamp `2016-08-28 22:00:00`. Or the very first ones. Haven't modified the SQL you provided at all. Is there anything else that could be causing this problem? I'm running this within MySQLi if that makes a difference. – Nikk Dec 05 '16 at 06:15
  • No, mysqli has nothing to do with it, you could be running this from the mysql console or whichever mysql client application you use for admin. I removed the records from my conf_mno_price table and imported yours, so that is the same. Might be a difference in the data in the other tables that makes the joins match differently? My advise would be to execute the parts of the big query one by one in your console and see if each part gives you the expected results, you'll be able to debug the problem that way. – palako Dec 05 '16 at 08:44