MYSQL LIKE IN / Matching Substring

Question

i have the following two statements and want to get this in only one statement. Is that possible?

Statement One, getting Content from the last 2 Days:

SELECT  
    c.ID SUBTAG
FROM 
    `content` c, 
    posts p   
WHERE 
    c.id = p.id_content AND
    from_unixtime(p.scheduled) BETWEEN TIMESTAMPADD(DAY,-2,now()) AND  from_unixtime(UNIX_TIMESTAMP(NOW()))
ORDER BY from_unixtime(p.scheduled)

With c.id i need to get sum(qty) from Orders. Like that:

 SELECT 
    sum(Qty) Bestellungen, 
    SUBTAG  
 FROM 
    Orders 
 WHERE SubTag Like '%c.id%'
 GROUP BY SubTag

Within the table orders the Column Subtag doesnt exactly has the c.id. Its just part of the Column. That's why i need the like! Subtag in Table Orders looks like 132_subtag_kjsdf4382.

Table Orders looks like:

Date_           Price   Qty     SubTag  
2017-02-20      14.98   1       b1a8cc_2_qgsGairwtape
2017-02-20      14.98   1       b1a8cc_3_qgdfgdirwtape

posts look like:

ID      id_content      scheduled           ad_active   posted 
3015    82              1487754540          0            1

And content looks like:

ascsubid    ID              
b1a8cc      82

Result from the query should look like:

SubTag   Orders
b1a8cc      2

Sample data and desired results would help explain what you want to do. — Gordon Linoff
– Gordon Linoff, Commented Feb 21, 2017 at 11:34

Rick Lee · Accepted Answer · 2017-02-21 10:47:56Z

1

Something like this ?

SELECT 
    sum(Qty) Bestellungen, 
    SUBTAG  
 FROM 
    Orders 
 WHERE SubTag in 
    (SELECT c.ID 
     FROM `content` c, posts p   
     WHERE c.id = p.id_content AND
     from_unixtime(p.scheduled) BETWEEN TIMESTAMPADD(DAY,-2,now()) AND 
     from_unixtime(UNIX_TIMESTAMP(NOW()))
     and c.id Like '%c.id%')
 GROUP BY SubTag;

answered Feb 21, 2017 at 10:47

Rick Lee

7732 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ramu Agrawal · Accepted Answer · 2017-02-21 10:42:44Z

0

I think you are looking for below statement.

 SELECT 
    sum(Qty) Bestellungen, 
    SUBTAG  
 FROM 
    Orders 
 WHERE SubTag Like '%' + c.id + '%';
 GROUP BY SubTag

answered Feb 21, 2017 at 10:42

Ramu Agrawal

5086 silver badges9 bronze badges

1 Comment

Marc Compte Over a year ago

want to get this in only one statement I don't think this is an answer to the question, you are still using two different SQL statements.

Trung Duong · Accepted Answer · 2017-02-21 10:59:11Z

You could try

SELECT 
    SUM(Qty) Bestellungen, 
    SubTag  
 FROM 
    Orders 
 WHERE EXIST
    (SELECT c.ID 
     FROM `content` c, posts p   
     WHERE c.id = p.id_content 
           AND from_unixtime(p.scheduled) BETWEEN TIMESTAMPADD(DAY,-2,now()) AND from_unixtime(UNIX_TIMESTAMP(NOW()))
           AND Orders.SubTag LIKE CONCAT('%', c.ID, '%') )
 GROUP BY SubTag;

OR

SELECT 
    SUM(Orders.Qty) Bestellungen, 
    Orders.SubTag 
FROM
    (SELECT  
        c.ID SUBTAG
    FROM 
        `content` c, 
        posts p   
    WHERE 
        c.id = p.id_content AND
        from_unixtime(p.scheduled) BETWEEN TIMESTAMPADD(DAY,-2,now()) AND  from_unixtime(UNIX_TIMESTAMP(NOW()))
    ORDER BY from_unixtime(p.scheduled)
    ) Tag INNER JOIN Orders ON Orders.SubTag LIKE CONCAT('%', Tag.SubTag, '%') 
GROUP BY 
    Orders.SubTag

Collectives™ on Stack Overflow

MYSQL LIKE IN / Matching Substring

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related