Merge two queries in Mysql

Question

I have a table which includes all the users video downloading history:

src_ip   vlan_id  video_id area  date_pl
aaaa     A        1        123   xxxx-xx-xx
aaaa     A        2        123   xxxx-xx-xx
aaaa     B        1        456   xxxx-xx-xx
bbbb     A        4        123   xxxx-xx-xx
bbbb     C        6        567   xxxx-xx-xx
...

I use the combination of src_ip and vlan_id to identify different users, Now I want to randomly select 50 different users (which means 50 distinct combinations of src_ip and vlan_id )and list all their downloading history.

So first I did:

   SELECT distinct src_ip, vlan_id from video_2 as table2 
   WHERE date_pl >= '2011-11-29 00:00' AND date_pl <= '2011-12-05 23:55' order by rand() limit 50

Then I did:

   SELECT src_ip, vlan_id, video_id, area from video_2 
   where video_2.src_ip = table2.src_ip AND video_2.vlan_id = table2.vlan_id 
   AND date_pl >= '2011-11-29 00:00' AND date_pl <= '2011-12-05 23:55'

But the problem is, since they are two independent queries, in the second query, it doesn't know what table2 is.

How can I solve this problem or how to merge these two queries into one?

gbn · Accepted Answer · 2012-01-16T14:03:35.110

2

CREATE TEMPORARY TABLE table2
AS
SELECT distinct src_ip, vlan_id from video_2
WHERE date_pl >= '2011-11-29 00:00' AND date_pl <= '2011-12-05 23:55'
ORDER BY rand() 
LIMIT 50;

SELECT src_ip, vlan_id, video_id, area
FROM 
   video_2
   JOIN 
   table2 ON video_2.src_ip = table2.src_ip AND video_2.vlan_id = table2.vlan_id';

You can ~~can't~~ make "table2" derived in line ~~because of restrictions on the use of LIMIT in subqueries. Otherwise it'd be~~

SELECT 
   v2.src_ip, v2.vlan_id, v2.video_id, v2.area 
FROM 
   video_2 v2
   JOIN 
   (SELECT distinct v.src_ip, v.vlan_id from video_2 v
   WHERE v.date_pl >= '2011-11-29 00:00' AND v.date_pl <= '2011-12-05 23:55'
   ORDER BY rand() 
   LIMIT 50
   ) table2 ON v2.src_ip = table2.src_ip AND v2.vlan_id = table2.vlan_id;

Edit: Restrictions LIMITs in subqueries apply to IN mostly, not derived tables

Edit 2: added more aliases

edited Jan 16 '12 at 14:03

answered Jan 16 '12 at 13:26

gbn

422,506
82
585
676

Second query works fine in MySQL. `LIMIT` is not allowed in `IN/ALL/ANY/SOME` subqueries. It is allowed in derived tables used as your example. – ypercubeᵀᴹ Jan 16 '12 at 13:32
@ypercube: ah, I've seen it with IN then: http://dev.mysql.com/doc/refman/5.5/en/subquery-errors.html – gbn Jan 16 '12 at 13:34
@gbn thanks for your reply. I used the second query, but Here I got another error: src_ip' in field list is ambiguous. Do you have any suggestion to solve this problem? – manxing Jan 16 '12 at 13:37
@gbn Hi, again, I have another question about this. I noticed that there are only 50 items returned. My intention is to to select 50 different users, but actually each user has more than entry in the video_2 table with different video_id, I want to list all of them, so the results should be more than 50...I don't know which part is wrong... – manxing Jan 16 '12 at 14:00
@manxing: I've added more aliases in case there is some ambuiguity – gbn Jan 16 '12 at 14:04

Merge two queries in Mysql

1 Answers1

Linked