SQL: How to get cells by 2 last dates from 3 different tables?

Question

I have 3 tables (stars mach the ids from the table before):

product:
prod_id*    prod_name   prod_a_id   prod_b_id   prod_user 

keywords:
key_id**    key_word    key_prod*   kay_country 

data: 
id  dat_id**    dat_date    dat_rank_a  dat_traffic_a   dat_rank_b  dat_traffic_b

I want to run a query (in a function that gets a $key_id) that outputs all these columns but only for the last 2 dates(dat_date) from the 'data' table for the key_id inserted - so that for every key_word - I have the two last dat_dates + all the other variables included in my SQL query:

So... This is what I have so far. and I don't know how to get only the MAX vars. I tried using "max(dat_date)" in different ways that didn't work.

SELECT prod_id, prod_name, prod_a_id, prod_b_id, key_id, key_word, kay_country, dat_date, dat_rank_a, dat_rank_b, dat_traffic_a, dat_traffic_b
    FROM   keywords
        INNER JOIN data 
            ON keywords.key_id = data.dat_id
        INNER JOIN prods
            ON keywords.key_prod = prods.prod_id

Is there a possability to do this with only one query?

EDIT (FOR IgorM):

public function newnew() { 

    $query = $this->db->query('WITH CTE AS
                                (
                                   SELECT *,
                                         ROW_NUMBER() OVER (PARTITION BY dat_id ORDER BY dat_date ASC) AS                   
                                   RowNo FROM data
                                )
                                SELECT *
                                FROM CTE
                                INNER JOIN keywords 
                                        ON keywords.key_id = CTE.dat_id
                                    INNER JOIN prods
                                        ON keywords.key_prod = prods.prod_id
                                WHERE RowNo < 3
                            ');

    $result = $query->result();

    return $result;

}

This is the error on the output:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CTE AS ( SELECT *, ROW_NUMBER() OVER (' at line 1

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dat_id ORDER BY dat_date ASC) AS RowNo FROM data ) SELECT * FROM CTE INNER JOIN keywords ON keywords.key_id = CTE.dat_id INNER JOIN prods ON keywords.key_prod = prods.prod_id WHERE RowNo < 3

Please show sample data and desired results. It is unclear what you are actually trying to do, given that you want information from the products table but you seem to be limiting results based on the keywords table. — Gordon Linoff, Feb 15 '16 at 15:17
@RickSanchez, if you run it on MySQL it won't work since MySQL does not support WITH. So I need to know what DB you dealing with. — IgorM, Feb 16 '16 at 13:33

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

For SQL

WITH CTE AS
    (
       SELECT *,
             ROW_NUMBER() OVER (PARTITION BY dat_id ORDER BY dat_date ASC) AS                   
       RowNo FROM data
    )
    SELECT *
    FROM CTE
    INNER JOIN keywords 
            ON keywords.key_id = CTE.dat_id
        INNER JOIN prods
            ON keywords.key_prod = prods.prod_id
    WHERE RowNo < 3

For MySQL (not tested)

SET @row_number:=0;
SET @dat_id = '';

SELECT *, 
@row_number:=CASE WHEN @dat_id=dat_id THEN @row_number+1 ELSE 1 END AS row_number, 
@dat_id:=dat_id AS dat_id_row_count
FROM data d
   INNER JOIN keywords 
         ON keywords.key_id = d.dat_id
   INNER JOIN prods
         ON keywords.key_prod = prods.prod_id
   WHERE d.row_number < 3

The other approach is self joining. I don't want to take credit for somebody else's job, so please look on the following example: ROW_NUMBER() in MySQL

Look for the following there:

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

edited May 23 '17 at 12:23

Community

1
1

answered Feb 15 '16 at 15:47

IgorM

1,348
1
12
28

Is `CTE` a command? What should I research? I will try this in a few minuets. – Imnotapotato Feb 16 '16 at 09:46
@RickSanchez, CTE is not command it a name of sort of temporary table which is populated with the following query SELECT *, ROW_NUMBER() OVER (PARTITION BY dat_id ORDER BY dat_date ASC) AS RowNo FROM data – IgorM Feb 16 '16 at 10:30
So I definetly have something new to research now! :D I am getting syntax errors, I'm using codeigniter, and when writing this down in the text editor words like "SELECT" don't get a special color. which is weird for me. I will update my main post with the whole function and tell me if you find any suspicious errors. – Imnotapotato Feb 16 '16 at 10:39
@RickSanchez, revised – IgorM Feb 16 '16 at 14:37
`SQL query: SET dat_id = ''; said: Documentation #1193 - Unknown system variable 'dat_id'` – Imnotapotato Feb 16 '16 at 14:50
`Error SQL query: Documentation SELECT *, @row_number:=CASE WHEN @dat_id=dat_id THEN @row_number+1 ELSE 1 END AS row_number, @dat_id:=dat_id AS dat_id_row_count FROM data d INNER JOIN keywords ON keywords.key_id = d.dat_id INNER JOIN prods ON keywords.key_prod = prods.prod_id WHERE d.row_number < 3 LIMIT 0, 25 MySQL said: Documentation #1054 - Unknown column 'd.row_number' in 'where clause' ` – Imnotapotato Feb 22 '16 at 12:16
Setting a variable on mysql is only with `=` and not `:=`. I fixed it within my code. What is the `d` for? – Imnotapotato Feb 22 '16 at 13:02
Where do I insert the dat_id i want to run on the query? I edited the code to this: `SET @row_number=0; SET @dat_id = 21; SELECT *, @row_number=CASE WHEN @dat_id=dat_id THEN @row_number+1 ELSE 1 END AS row_number, @dat_id=dat_id AS dat_id_row_count FROM data d INNER JOIN keywords ON keywords.key_id = d.dat_id INNER JOIN prods ON keywords.key_prod = prods.prod_id WHERE d.dat_id < 3` and I always get the firs id in the data table – Imnotapotato Feb 22 '16 at 13:37

score 1 · Answer 2 · answered Feb 22 '16 at 16:14

I think a window function is the best way to go. without knowing a lot about the structure of the data you can try a subquery of what you are trying to restrict and then joining that to the rest of the data. Then within the where clause restrict the rows you pull back.

select  p.prod_id, p.prod_name, p.prod_a_id, p.prod_b_id, 
        t.key_id, t.key_word, t.kay_country, t.dat_date, 
        t.dat_rank_a, t.dat_rank_b, t.dat_traffic_a, t.dat_traffic_b

from 
      (
          select 
                    k.key_id, k.key_word, k.kay_country, d.dat_date, d.dat_rank_a, 
                    d.dat_rank_b, d.dat_traffic_a, d.dat_traffic_b,
                    row_number() over (partition by dat_id order by dat_date desc) as 'RowNum'
           from     keywords as k
                    inner join 
                    data as d on k.key_id = d.dat_id

        ) as t

        inner join 
        prods as p on t.key_prod = p.prod_id

where tmp.RowNum <=2

Where do I insert the 'key_id' variable? – Imnotapotato Feb 23 '16 at 10:23 — Imnotapotato, Feb 23 '16 at 10:23
in the subquery and in the outer query – yoyoyoyo123 Feb 23 '16 at 12:21 — yoyoyoyo123, Feb 23 '16 at 12:21

Arth · Accepted Answer · 2016-02-23T23:41:30.660

If you only want to do this for one key_id at a time (as alluded to in your responses to other answers) and only want two rows, you can just do:

   SELECT p.prod_id,
          p.prod_name,
          p.prod_a_id,
          p.prod_b_id, 
          k.key_id, 
          k.key_word, 
          k.key_country, 
          d.dat_date, 
          d.dat_rank_a,
          d.dat_rank_b,
          d.dat_traffic_a,
          d.dat_traffic_b
     FROM keywords k
     JOIN data d
       ON k.key_id = d.dat_id
     JOIN prods p
       ON k.key_prod = p.prod_id
    WHERE k.key_id = :key_id /* Bind in key id */
 ORDER BY d.dat_date DESC
    LIMIT 2;

Whether you want this depends on your data structure and whether there is more than one key/prod combination per date.

Another option limiting just the data rows would be:

   SELECT p.prod_id,
          p.prod_name,
          p.prod_a_id,
          p.prod_b_id, 
          k.key_id, 
          k.key_word, 
          k.key_country, 
          d.dat_date, 
          d.dat_rank_a,
          d.dat_rank_b,
          d.dat_traffic_a,
          d.dat_traffic_b
     FROM keywords k
     JOIN (
       SELECT dat_id,
              dat_date,
              dat_rank_a,
              dat_rank_b,
              dat_traffic_a,
              dat_traffic_b
         FROM data
        WHERE dat_id = :key_id /* Bind in key id */
     ORDER BY dat_date DESC
        LIMIT 2
          ) d
       ON k.key_id = d.dat_id
     JOIN prods p
       ON k.key_prod = p.prod_id;

If you want some kind of grouped results for all the keywords, you'll need to look at the other answers.

People here took it far. Thanks a lot! – Imnotapotato Feb 24 '16 at 12:29 — Imnotapotato, Feb 24 '16 at 12:29

score 0 · Answer 4 · answered Feb 22 '16 at 22:50

0

This is a "groupwise max" problem. Reference. CTE does not exist in MySQL.

I'm not totally clear on how your tables are linked, but here is a stab:

SELECT
    *
FROM
  ( SELECT  @prev := '', @n := 0 ) init
JOIN
  ( SELECT  @n := if(k.key_id != @prev, 1, @n + 1) AS n,
            @prev := k.key_id,
            d.*, k.*, p.*
        FROM  data d
        JOIN  keywords k  ON k.key_id = d.dat_id
        JOIN  prods p     ON k.key_prod = p.prod_id
        ORDER BY
            k.key_id   ASC,
            d.dat_date ASC
  ) x
WHERE  n <= 2
ORDER BY  k.key_id, n;

answered Feb 22 '16 at 22:50

Rick James

135,179
13
127
222

Where do I insert the 'key_id' variable? – Imnotapotato Feb 23 '16 at 10:23
I don't understand your question about inserting `key_id`. (Or maybe I don't understand the original problem.) – Rick James Feb 23 '16 at 19:30

Gouda Elalfy · Answer 5 · 2016-02-15T15:36:36.420

-1

you can use this query:

select prod_id, prod_name, prod_a_id, prod_b_id, key_id, key_word, 
kay_country, dat_date, dat_rank_a, dat_rank_b, dat_traffic_a, dat_traffic_b
from keywords where dat_date in (
 SELECT MAX(dat_date) FROM keywords temp_1 
where temp_1.prod_id = keywords.prod_id
union all 
SELECT MAX(dat_date) FROM keywords
WHERE dat_date NOT IN (SELECT MAX(dat_date ) FROM keywords temp_2 where
temp_2.prod_id = keywords.prod_id)
 )

edited Feb 15 '16 at 15:36

answered Feb 15 '16 at 15:14

Gouda Elalfy

6,888
1
26
38

4

I understand the answer but not the upvote. The question clearly specifies "last 2 dates(dat_date) from the 'data' table for each key_id", so this is not a correct answer. – Gordon Linoff Feb 15 '16 at 15:18
1

That will end up with a total of two rows, he wants two dates for each ID, not two rows total. – sagi Feb 15 '16 at 15:18

SQL: How to get cells by 2 last dates from 3 different tables?

5 Answers5