compcrk compcrk - 5 months ago 10
MySQL Question

How to combine sql queries into one

I have the following queries from 2 different tables as follows:

SELECT `TRANSPORTATION_NAME_OF_TRANSPORTATION`, `PARTNER_CODE`, `INCOTERM_NAME`,
`SHIPPING_DATE`, `SHIPPING_ETA_TO_PORT_AIR_DATE`, `TOTAL_WEIGHT`, `ORDER_DESCRIPTION`,
`MORE_DESCRIPTION`, `UNIT_OF_MEASURE`, `QUANTITY`,`PERFORMANCE_REMARKS`, `AWB_DATE`, `FFF_NAME`
FROM INBOUND_ORDER
WHERE `AWB_NUM` ='999129456'

SELECT `PARTNER_NAME`
FROM `PARTNER`
WHERE `PARTNER_CODE` ='$partnercode'


I am getting the partner code from the first query and want to use that in the second but in one single sql statement.

The table structure is as follows:

enter image description here

Answer

Use nested query

SELECT `PARTNER_NAME`
FROM `PARTNER`
WHERE `PARTNER_CODE` = (SELECT `PARTNER_CODE`
                        FROM INBOUND_ORDER
                        WHERE `AWB_NUM` ='999129456')

Or inner join

SELECT p.`PARTNER_NAME`
FROM INBOUND_ORDER o
INNER JOIN `PARTNER` p ON p.`PARTNER_CODE` = o.`PARTNER_CODE`
WHERE o.`AWB_NUM` = '999129456'
Comments