There are tables:

1. shop_order (id, contact_id,state_id) 2. contact_data (id, contact_id, field, value)

The second table is as follows:

 id | contact_id | field | value 1 | 25 | phone | 89167777777 2 | 25 | city | Москва 3 | 26 | phone | 89164444444 4 | 26 | city | Москва 5 | 27 | phone | 89163333333 6 | 27 | city | Тверь

Table shop_order

 id | contact_id | state_id 1 | 25 | paid 2 | 26 | paid 3 | 27 | notpaid

How to upload all orders (shop_order) and telephone numbers of the contact to these orders (contact_data), which have shop_order.state_id = 'paid', and the city of Moscow?

  • to unload in somewhere in the code, in some language .. or just in sql manager? And provide both tables - Insider
  • @Insider just need a sql query, added - Dizzy221
  • @ Dizzy221 w3schools.com/sql/sql_join_left.asp - DaemonHK am

2 answers 2

If you need to take numbers, I think this one will do:

 SELECT so.id, so.contact_id. cdp.value FROM shop_order so JOIN contact_data cdc ON so.contact_id = cdc.contact_id JOIN contact_data cdp ON so.contact_id = cdp.contact_id WHERE so.state_id = 'paid' and cdc.value = 'Москва' and cdp.field='phone' GROUP BY cdp.value
  • And what if there is an order, but there is no contact? - Viktorov
  • If there is a situation in which with one telephone number 2 orders, then due to group by value second order will be lost - Viktorov
  • @Viktorov order without contact on the application logic is not possible. The idea was to mainly pull out the phone numbers of the last orders, so the loss of the order does not matter here. But thanks for the opinion;) - Dizzy221
  • @Dizzy221 so you can lose recent orders, if this phone number was used previously. Test) - Viktorov
 SELECT * FROM shop_order JOIN contact_data ON shop_order.contact_id = contact_data.contact_id WHERE 1

It seems like it should work

  • You wrote something strange here. And where 1 why?) - Viktorov
  • @Viktorov, WHERE 1 will take all the values ​​from the shop_order table .. But the option for a person above is still better. - MoloF
  • so with the same success you can simply not write. And your option completely ignores that contacts can be not only Moscow and not only phones. You just get everything out. In general, this is not at all - Viktorov