Hello Experts,
We need your help regarding a complex mysql query. I've used the word complex as it is difficult for us to figure out the solution and we have tried all known approaches.
We have 3 tables as explain below.
tbl_products, tbl_orders, tbl_shipping
and fields of each table are
Table: tbl_products
product_id, INT(11), Primary Key
product_name, Varchar(100)
product_description, TEXT
product_status, Enum('pending','approved')
Table: tbl_orders
order_id, INT(11), Primary Key
order_details, TEXT
order_status, Enum ('shipped','pending')
Table: tbl_shipping
shipping_id, INT(11), PK
order_id, INT(11)
shipping_details, TEXT
Note: The field order_id is the relation between tbl_orders and tbl_shipping because tbl_shipping can contain many records with same order_id.
We need a query that would search all three tables for a given keyword and return the matching results (without any duplicate results) of records those are approved.
Sample data of these tables are:
Table: tbl_products
product_id | product_name | product_description | product_status
--------------------------
----------
----------
----------
--------
1 | Mobile Phone | This is a mobile phone from nokia company | approved
2 | Cars | I love this car | pending
3 | Music Player | iPod is a very nice music player | approved
4 | Calculator | This calculator is the best in market | approved
Table: tbl_orders
order_id | order_details | order_status
--------------------------
----------
-
1 | mobile phone order is under processing | pending
2 | nokia mobile phone is being shipped | approved
3 | ipod order from smith | approved
4 | best mobile with calculator is shipped | approved
Table: tbl_shipping
shipping_id | order_id | shipping_details
--------------------------
----------
--
1 | 2 | test data of mobile
2 | 2 | mobile phone
3 | 1 | bla bla bla
4 | 2 | ipod shipping details
5 | 3 | again test data
6 | 1 | bla ipod mobile bla
We need to have a query that will create relation between tbl_orders and tbl_shipping using the field order_id i.e. WHERE tbl_orders.order_id = tbl_shipping_order_id so that only those orders are included who are approved i.e. AND tbl_orders_order_status='a
pproved'. This query has to search the text fields of al 3 tables for a give search keyword and display the records. Test keyword can be "mobile", "best" or "ipod".
Any help would be greatly appreciated.
Start Free Trial
