i coming project , have little mysql background basic selects , inserts , whatnot. but, making me beat head against wall.
i have typical user information table in mysql:
users +-------+----------+---------+-----+ |user_id|first_name|last_name|email| +-------+----------+---------+-----+ 1 tim jones tj@acme.com 2 sarah peteres sp@acme.com 3 larry doe ld@acme.com then have multiple product tables:
products_one +-------+-------+---------+----------+--------------+ |prod_id|user_id|prod_name|prod_width|prod_ship_date| +-------+-------+---------+----------+--------------+ 1 1 bowl 9 1-1-16 2 1 fork 1 1-2-16 3 2 plate eleven 1-3-16 products_two +-------+-------+----------+--------+--------------+ |prod_id|user_id|prod_state|prod_job|prod_ship_date| +-------+-------+----------+--------+--------------+ 1 3 maine min 1-1-16 2 2 texas max 1-2-16 3 1 ohio min 1-1-16 i have 15 total product tables have prod_id, users_id, , prod_ship_date. other fields might different based on product table in. but, different product tables have 3 common fields.
what trying accomplish list of user info , product info products match ship date.
i want find users , product table , product id getting on date.
so, if searched on ship date of 1-1-16, like:
+----------------+-----------+-------------+-----------+ |users.first_name|users.email|product_table|products_id| +----------------+-----------+-------------+-----------+ tim tj@acme.com one,two 1,3 larry ld@acme.com 1 3 if searched on ship date of 1-2-16, like:
+----------------+-----------+-------------+-----------+ |users.first_name|users.email|product_table|products_id| +----------------+-----------+-------------+-----------+ tim tj@acme.com 1 2 sarah sp@acme.com 2 2 i hope makes sense. unfortunately, cannot change structure or layout of various product tables due legacy issues.
i can't figure out mysql statement use this.
the above results used reporting purposes.
you write query this:
select first_name,email,prod_id,group_concat(product_table) product_table ( select u.user_id ,first_name,email,prod_id, 'one' product_table users u join products_one p on u.user_id = p.user_id prod_ship_date = '2016-01-01 00:00:00' union select u.user_id,first_name,email,prod_id, 'two' product_table users u join products_two p on u.user_id = p.user_id prod_ship_date = '2016-01-01 00:00:00' ) group a.user_id
order user_id,product_table
and on.
with second group concat , order by
select first_name,email,group_concat(prod_id),group_concat(product_table) product_table ( select u.user_id ,first_name,email,prod_id, 'one' product_table users u join products_one p on u.user_id = p.user_id prod_ship_date = '2016-01-01 00:00:00' union select u.user_id,first_name,email,prod_id, 'two' product_table users u join products_two p on u.user_id = p.user_id prod_ship_date = '2016-01-01 00:00:00' ) group a.user_id order user_id,product_table check out sqlfiddle
Comments
Post a Comment