Mysql – Assistance with MySQL query

join;MySQLsubquery

I have following tables

Tags

id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1

products

id | proudct_name
1|product1
2|product2

product_tags

id | product_id | tag_id 
1|1||1
2|1|2
3|2|1

i need retrieve only those product which belongs to both tag1 and tag2

  select * from `products` INNER JOIN product_tags ON products.id=product_tags.product_id

INNER JOIN tags ON tags.id=product_tags.tag_id WHERE product_tags.tag_id=1 AND product_tags.tag_id=2

But my query return empty result

js fiddle

http://sqlfiddle.com/#!9/34f43a/1/0

Best Answer

If I've understood you correctly, you want something like this (see fiddle here):

SELECT 
  p.id AS p_pid, p.product_name AS p_name, 
  p.product_category_id AS p_cat_id,
  pt.product_id AS pt_pid, pt.tag_id AS pt_tag_id, -- other product_tag fields...
  t.tag_name AS t_name, 
  t.slug AS t_slug_name                            -- other tag fields...
FROM products p
INNER JOIN product_tags pt 
  ON p.id = pt.product_id
INNER JOIN tags t
  ON t.id = pt.tag_id 
WHERE pt.tag_id = 1 OR pt.tag_id = 6
ORDER BY product_id, tag_id;

Result:

p_pid     p_name p_cat_id pt_pid pt_tag_id        t_name    t_slug_name
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         6            Mi              mi
    1   product1        4      1         6            Mi              mi
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         6            Mi              mi
    2   product2        4      2         6            Mi              mi

Notice in the fiddle how I've arranged the SQL. I've used aliases and meaningful names for my fields, prefixing them with the table initials. You mightn't want to do that for the final query, but it sure helps when constructing your query and you have different, say, product_ids coming in from different tables through the JOINs. The aliases also help with legibility along with liberal use of spaces.

I think because of this lack of clarity, you missed this piece of code:

WHERE product_tags.tag_id=1 AND product_tags.tag_id=6

which, when turned into this, becomes much more legible:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

Now, it's glaringly obvious where the error is - you can't have a tag_id = 1 AND = 6 at the same time, hence your empty result set from your original query. So, just changing that line to:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

gives the result which is I believe what you were looking for. If not, let me know and I will endeavour to correct any problems/misunderstandings!

Oh, yes, a couple of final words. Please always include your version of MySQL - it's particularly important with this RDBMS as they've introduced many important features recently! Also, see the importance of providing good sample data - good questions get good answers here and yours has now become a good one with the addition of the thorough fiddle (+1)!

Related Solutions

Mysql – 3 table merge with some condition

As first step I merge the two product tables. I need an full outer join on product1 and product2 tables. To obtain on outer join in mysql we can union a left join and a right join. Start with a left join. In the following query IFNULL(p1.key1, p2.key1) is useful to merge two key column coming from the two product tables and IFNULL(, 'NaN') is useful to obtain 'NaN' in the output.

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial =     p2.serial;

To obtain a full outer join I need to repeat the previous query with 'right join' and union the result with the 'left join'

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

For convenience, I create a view with the previous query:

create or replace view p12 as
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

To check we can try with:

mysql> select * from p12 where key1 = 1 and key2 =1 ;
+------+------+--------+---------------+---------------+
| key1 | key2 | serial | product_data1 | product_data2 |
+------+------+--------+---------------+---------------+
| 1    | 1    | 0      | 15.556        | 5.556         |
| 1    | 1    | 1      | 14.996        | 4.996         |
| 1    | 1    | 2      | 12.556        | NaN           |
| 1    | 1    | 3      | 15.669        | NaN           |
+------+------+--------+---------------+---------------+

So the he final query is:

select mi.key1, mi.key2, p12.serial, mi.info1, mi.info2, mi.date, p12.product_data1, p12.product_data2 
from main_info mi 
inner join p12 on mi.key1 = p12.key1 and mi.key2 = p12.key2
order by mi.key1, mi.key2, p12.serial;


+------+------+--------+-------+-------+----------+---------------+---------------+
| key1 | key2 | serial | info1 | info2 | date     | product_data1 | product_data2 |
+------+------+--------+-------+-------+----------+---------------+---------------+
| 1    | 1    | 0      | 15    | 90    | 20120501 | 15.556        | 5.556         |
| 1    | 1    | 1      | 15    | 90    | 20120501 | 14.996        | 4.996         |
| 1    | 1    | 2      | 15    | 90    | 20120501 | 12.556        | NaN           |
| 1    | 1    | 3      | 15    | 90    | 20120501 | 15.669        | NaN           |
| 1    | 2    | 0      | 14    | 92    | 20120601 | 12.556        | 2.556         |
| 1    | 2    | 1      | 14    | 92    | 20120601 | 13.335        | 3.335         |
| 1    | 2    | 2      | 14    | 92    | 20120601 | NaN           | 2.56          |
| 1    | 2    | 3      | 14    | 92    | 20120601 | NaN           | 3.556         |
| 1    | 3    | 1      | 15    | 82    | 20120801 | 12.225        | 2.225         |
| 1    | 3    | 2      | 15    | 82    | 20120801 | 13.556        | 3.556         |
| 1    | 3    | 3      | 15    | 82    | 20120801 | 14.556        | NaN           |
| 2    | 1    | 0      | 17    | 90    | 20130302 | 12.556        | NaN           |
| 2    | 1    | 1      | 17    | 90    | 20130302 | 13.553        | NaN           |
| 2    | 1    | 2      | 17    | 90    | 20130302 | 12.335        | NaN           |
| 2    | 2    | 0      | 16    | 88    | 20130601 | NaN           | 2.556         |
| 2    | 2    | 1      | 16    | 88    | 20130601 | NaN           | 3.553         |
+------+------+--------+-------+-------+----------+---------------+---------------+

Mysql – Need some assistance on MySQL Sub query

You need to filter you subquery with call_type='SALES', then run:

I use your information:

INSERT INTO test.Dash_Agent_Disp
    (id,emp_id,call_start,call_end,call_type)
VALUES
    ('1','20043','20:20:10','20:21:19','SALES'),
    ('2','20043','20:25:34','20:27:12','NON-SALES'),
    ('3','20044','21:25:34','21:27:12','SALES'),
    ('4','20044','22:25:34','22:27:12','SALES'),
    ('5','20044','22:45:34','22:47:12','NON-SALES'),
    ('6','20043','21:25:34','21:27:12','NON-SALES');

Query:

SELECT 
    d.emp_id, 
    count(d.id) total, 
    (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls,
    d.call_type
FROM Dash_Agent_Disp as d
WHERE d.call_type='SALES'
GROUP BY 
    d.emp_id,
    d.call_type;

Result:

mysql> SELECT  d.emp_id,  count(d.id) total,  (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls, d.call_type FROM Dash_Agent_Disp as d WHERE d.call_type='SALES' GROUP BY  d.emp_id, d.call_type;
+--------+-------+-------------+-----------+
| emp_id | total | total_calls | call_type |
+--------+-------+-------------+-----------+
|  20043 |     1 |           3 | SALES     |
|  20044 |     2 |           3 | SALES     |
+--------+-------+-------------+-----------+
2 rows in set (0.00 sec)

mysql>

Best Answer

Related Solutions

Mysql – 3 table merge with some condition

Mysql – Need some assistance on MySQL Sub query

Related Question