Hello Friends I have two tables one is customer and the other one is new_party_estimate. But My following query is not working. Please help me to solve this problem.
SELECT
acc_name,
customer_id
FROM
customers
WHERE STATUS = 'e'
AND acc_name NOT IN (
SELECT DISTINCT
customer
FROM
new_party_estimate
WHERE closed = '0'
AND (
customer_alt = ''
OR customer_alt IS NULL
)
)
ORDER BY acc_name
I am running the sub query separately and it is giving the output. But When i run the full query at once the mysql shows empty result. Please tell what would be the problem!
Table Customers is Following with few records.
acc_name customer_id
CAMPUS FASHION_khyati CAM-11
PAPPU SUIT HOUSE PAPAAR5
R K FASHION R KAAR6
SELECTION MENS WEAR SELAAR7
Table new_party_estimate is Following with few records.
customer
LOVELY DRESSES
ASHIRWAD GARMENTS
AKASH DEEP
ABDUL LATIF READYMADE SALE
Nothing obvious in you query, although IN can be inefficient (using EXISTS would be more efficient).
Another alternative would be to do a LEFT JOIN but only bring back records where there is no match:-
SELECT acc_name,
customer_id
FROM customers
LEFT OUTER JOIN new_party_estimate
ON customers.acc_name = new_party_estimate.customer
AND closed = '0'
AND (customer_alt = ''
OR customer_alt IS NULL)
WHERE new_party_estimate.customer IS NULL
AND STATUS = 'e'
ORDER BY acc_name
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加