user3604481 user3604481 - 1 year ago 73
SQL Question

MYSQL select query based on another tables entry

I have a SQL query that looks like the following

FROM invoices_product
WHERE FROM_UNIXTIME(unixstamp, '%y%m%d') >= '160701'
AND FROM_UNIXTIME(unixstamp, '%y%m%d') <= '160730'

The problem I am facing now is that I would like to only get rows for a specific user based on
which is located in another table called
, and I would also like to select

Table invoices (`id`,`user_id`,`zname` etc)

Table invoices_product (`invoice_id`,`unixstamp`,etc)

Answer Source

Using an INNER JOIN between the two tables:

SELECT ip.*, i.zname
FROM invoices_product ip
INNER JOIN invoices i
    ON ip.invoice_id =
WHERE FROM_UNIXTIME(unixstamp, '%y%m%d') >= '160701' AND
      FROM_UNIXTIME(unixstamp, '%y%m%d') <= '160730' AND
      i.user_id = 'some_value'   -- this condition restricts to a given user
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download