CODEWITHSUNDEEP
mysql
Elamurugan
Elamurugan

Reputation: 3214

MYSQL select query with two tables - never giving result

I need to get one set of rows (it will be more than 70k records) from one table which are not in another table.

Table1 : Number of records will be more than 14000k Table2 : Number of records will be more than 110k

So my SQL query is 

SELECT  pre.`id` , pre.`deal_id` , pre.`coupon_code` , pre.`csv` 
FROM `customerorders` AS oc, `precoupon` AS pre 
WHERE oc.`uniqueid` != pre.`coupon_code`

The problem is it never able to completes.

It kept loading and at the end a white page on phpmyadmin I get this message.

memory exhausted - fatal error on PHP whatever size i have in ini_set

No results in SSH for long long time.

Is there any problem with the query or is there any optimized query for this?

I need to export these second table records if the particular column not available in table1.

Please help me someone. Thanks in advance

Upvotes: 2

Views: 123

Answers (2)

silly
silly

Reputation: 7887

try it with left joins like this:

SELECT
     pre.`id` ,
     pre.`deal_id` ,
     pre.`coupon_code` ,
     pre.`csv`
FROM `precoupon` AS pre
LEFT OUTER JOIN `customerorders` AS oc
    ON oc.`uniqueid` = pre.`coupon_code`
WHERE oc.uniuqueid IS NULL

Upvotes: 4

Maksym Polshcha
Maksym Polshcha

Reputation: 18368

Try something like that:

SELECT  id , deal_id , coupon_code, csv 
FROM precoupon
WHERE coupon_code not in (select uniqueid from customerorders)

using full product is not very good idea :-)

Upvotes: 1

Related Questions