|
Странное дело с LEFT JOIN
#39225780
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Вот `orders`
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
+--------------------------+---------------------------------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------------------------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| order_num | varchar(255) | NO | MUL | NULL | |
| type | enum('order','quote','invoice','drop_ship') | NO | MUL | NULL | |
| status | varchar(255) | NO | MUL | NULL | |
| cdate | datetime | NO | | NULL | |
| mdate | datetime | NO | | 0000-00-00 00:00:00 | |
| is_machine | tinyint(1) | NO | | NULL | |
| warehouse_id | mediumint(7) | NO | | NULL | |
| client_id | int(11) | NO | MUL | NULL | |
| bill_as_shipping | enum('no','yes') | NO | | no | |
| tracking_num | varchar(255) | NO | | NULL | |
| parent_order_id | mediumint(7) | NO | MUL | NULL | |
| parent_order_num | varchar(255) | NO | | NULL | |
| is_drop_ship | tinyint(1) | NO | | NULL | |
| is_ship_complete | tinyint(1) | NO | | NULL | |
| is_quality_ctrl_required | tinyint(1) | NO | | NULL | |
| account_id | bigint(20) | NO | MUL | NULL | |
| cust_po | varchar(255) | NO | | NULL | |
| shipping_type | varchar(255) | NO | | NULL | |
| ship_company | varchar(255) | NO | | NULL | |
| is_commercial_invoice | int(11) | NO | | NULL | |
| customer_shipping_num | varchar(255) | NO | | NULL | |
| ship_date | datetime | NO | | NULL | |
| confirm_date | datetime | NO | | NULL | |
| payment_terms | varchar(255) | NO | | NULL | |
| is_3rd_trans | tinyint(1) | NO | | NULL | |
| note | text | NO | | NULL | |
| total_discount | decimal(10,2) | NO | | NULL | |
| sub_total | decimal(10,2) | NO | | NULL | |
| sales_tax | decimal(10,2) | NO | | NULL | |
| total_weight | int(9) | NO | | NULL | |
| estimated_shipping | decimal(10,2) | NO | | NULL | |
| total | decimal(10,2) | NO | | NULL | |
| total_shipping | decimal(10,2) | NO | | NULL | |
| history | text | NO | | NULL | |
| order_id | mediumint(11) | NO | MUL | NULL | |
| trade_show | varchar(255) | NO | | NULL | |
| prefered_payment_method | varchar(255) | NO | | NULL | |
| is_urgent | int(1) | NO | | NULL | |
| invoice_id | int(11) | NO | | NULL | |
| vendor_id | int(11) unsigned | NO | | 0 | |
| plant | varchar(255) | NO | | NULL | |
| ship_handles | varchar(255) | NO | | NULL | |
| ship_destination | varchar(255) | NO | | NULL | |
| ship_charge | decimal(10,2) | NO | | NULL | |
| ship_payer | varchar(255) | NO | | NULL | |
| memo | text | NO | | NULL | |
| created_by | mediumint(7) | NO | | NULL | |
| actual_ship_date | datetime | NO | | NULL | |
| payment_credits | decimal(10,2) | NO | | NULL | |
| invoiced_per | int(5) | NO | | 0 | |
| qty_hand_changed | int(1) | NO | | NULL | |
| production_date | datetime | NO | | NULL | |
| brand_name | varchar(255) | NO | | NULL | |
| apparel_type | varchar(255) | NO | | NULL | |
| is_manually_closed | int(1) | NO | | NULL | |
| is_not_ship_charge | tinyint(1) | NO | | 0 | |
| is_manager_check | tinyint(1) | NO | | 0 | |
| is_accounting_flag | int(1) | NO | | NULL | |
| cc_last4num | char(4) | NO | | | |
| cc_expiration | char(5) | NO | | | |
| shipping_term | char(3) | NO | | | |
| location | varchar(255) | NO | | | |
+--------------------------+---------------------------------------------+------+-----+---------------------+----------------+
Вот `accounts`
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
+----------------------+------------------------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------------------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| role | varchar(255) | NO | | | |
| email | char(50) | NO | | | |
| password | varchar(255) | NO | | NULL | |
| fname | varchar(255) | NO | | NULL | |
| lname | varchar(255) | NO | | NULL | |
| qb_initial | varchar(255) | NO | | | |
| salesteams_id | int(10) | NO | | 0 | |
| region_id | int(10) | NO | | 0 | |
| account_flag | enum('yes','no') | NO | | no | |
| address | varchar(255) | NO | | NULL | |
| id_template | mediumint(9) | NO | | 0 | |
| city | varchar(255) | NO | | NULL | |
| state | varchar(255) | NO | | NULL | |
| zip | varchar(255) | NO | | NULL | |
| country | varchar(255) | NO | | NULL | |
| phone | varchar(255) | NO | | NULL | |
| mobile | varchar(255) | NO | | NULL | |
| fax | varchar(255) | NO | | NULL | |
| status | enum('active','blocked','deleted') | NO | | active | |
| last_login | datetime | NO | | 0000-00-00 00:00:00 | |
| count_login | mediumint(9) | NO | | 0 | |
| cdate | datetime | NO | | NULL | |
| remember_me | varchar(255) | NO | | NULL | |
| filter_parms_orders | text | NO | | NULL | |
| filter_parms_prodque | text | NO | | NULL | |
| allow_auto_printing | tinyint(1) unsigned | NO | | 0 | |
+----------------------+------------------------------------+------+-----+---------------------+----------------+
И `clients`
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
+----------------------------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| status | enum('deleted','active') | NO | | active | |
| company_name | varchar(255) | NO | UNI | NULL | |
| salutation | varchar(255) | NO | | NULL | |
| fname | varchar(255) | NO | | NULL | |
| lname | varchar(255) | NO | | NULL | |
| is_accounting_flag | int(11) | NO | | NULL | |
| country_id | int(11) | NO | | NULL | |
| default_payment_term | varchar(255) | NO | | NULL | |
| prefered_payment_method | varchar(255) | NO | | NULL | |
| default_shipping_number | varchar(255) | NO | | NULL | |
| notes | text | NO | | NULL | |
| cdate | datetime | NO | | NULL | |
| mdate | datetime | NO | | NULL | |
| tracking_num | varchar(255) | NO | | NULL | |
| orders_qty | int(11) | NO | | 0 | |
| orders_total | decimal(10,2) | NO | | 0.00 | |
| invoices_qty | int(11) | NO | | 0 | |
| invoices_total | decimal(10,2) | NO | | 0.00 | |
| invoice_delivery_method | varchar(255) | NO | | NULL | |
| quotes_qty | int(11) | NO | | 0 | |
| quotes_total | decimal(10,2) | NO | | 0.00 | |
| ship_handles | varchar(255) | NO | | NULL | |
| ship_destination | varchar(255) | NO | | NULL | |
| ship_method | varchar(255) | NO | | NULL | |
| nafta_required | tinyint(1) | NO | | NULL | |
| non_standard_required | varchar(255) | NO | | NULL | |
| additional_haz | varchar(255) | NO | | NULL | |
| type | varchar(255) | NO | | NULL | |
| phone | varchar(255) | NO | | NULL | |
| phone2 | varchar(255) | NO | | NULL | |
| fax | varchar(255) | NO | | NULL | |
| mssql_CompanyID | int(11) unsigned | NO | MUL | 0 | |
| mssql_CompanyName | char(255) | NO | | | |
| account_id | int(11) | NO | | NULL | |
| is_not_export_price | int(11) | NO | | NULL | |
| is_applyotherdiscount | int(11) | NO | | NULL | |
| is_applyotherdiscount_part | int(11) | NO | | NULL | |
| discountcustomer_id | int(11) | NO | MUL | NULL | |
| discountcustomer_part_id | int(11) | NO | | NULL | |
| residential | enum('yes','no','') | NO | | | |
| tmp_field | varchar(255) | NO | | | |
+----------------------------+--------------------------+------+-----+---------+----------------+
|
|
|
