user3604481 user3604481 - 4 months ago 8
SQL Question

MYSQL select query based on another tables entry

I have a SQL query that looks like the following

SELECT *
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
user_id
which is located in another table called
invoices
, and I would also like to select
zname
from
invoices
.

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

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

Answer

Using an INNER JOIN between the two tables:

SELECT ip.*, i.zname
FROM invoices_product ip
INNER JOIN invoices i
    ON ip.invoice_id = i.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