Need help in mysql query

Hi Guys,

  1. I need data from mysql table freddyshipment comparing with table podetails,
  2. I need to match some field with freddyshipmnet & podetails. And two (col & sizes) field of table freddyshipment, if any one is not matching with table podetails.

But with below query I’m getting also data from the table freddyshipmnet, which is matching (col & sizes) with table podetails.

Please tell me, what I’m doing wrong here!!!

SELECT * FROM freddyshipment,podetails
WHERE freddyshipment.kcgmt = podetails.posrefno  
AND freddyshipment.orderno = podetails.posorderno
AND freddyshipment.style = podetails.postyle
AND (freddyshipment.col <> podetails.poscol
OR freddyshipment.sizes <> podetails.posize)

You’re using a CROSS JOIN instead of an INNER JOIN.

could you please explain a bit more? if you could give me some hints on my query , would help me to understand.

have you read the link about joins?

You’re using a CROSS JOIN instead of an INNER JOIN.

technically, yes… but that’s not what’s giving him trouble

he could rewrite it as an INNER JOIN and get exactly the same results

SELECT * 
  FROM freddyshipment
INNER
  JOIN podetails
    ON podetails.posrefno   = freddyshipment.kcgmt  
   AND podetails.posorderno = freddyshipment.orderno
   AND podetails.postyle    = freddyshipment.style  
 WHERE podetails.poscol <> freddyshipment.col
    OR podetails.posize <> freddyshipment.sizes 

This is how i got my correct result.

$details = "SELECT
 A.kcgmt,A.style,A.orderno,A.col,A.sizes,A.qty,A.ctnqty,A.invoice,A.id,A.season,A.buyer,A.factory
  FROM freddyshipment A
WHERE kcgmt = 'PDSFRDSS1' and CONCAT(A.kcgmt , A.style ,A.orderno ,A.col ,A.sizes)
NOT IN 
(SELECT CONCAT(B.posrefno , B.postyle ,B.posorderno ,B.poscol ,B.posize) as X 
from podetails B)";

Also with this…

$details = "SELECT *
FROM freddyshipment
WHERE kcgmt = 'PDSFRDSS1' and (kcgmt,style,orderno,col,sizes) NOT IN
      ( SELECT posrefno,postyle,posorderno,poscol,posize
        FROM podetails
       )";

And with this too…

$details = "SELECT c.*
FROM freddyshipment AS c
WHERE kcgmt = 'PDSFRDSS1' and
NOT EXISTS 
      ( SELECT 1
        FROM podetails AS p
        WHERE p.posrefno = c.kcgmt
          AND p.postyle = c.style
		  AND p.posorderno = c.orderno
		  AND p.poscol = c.col
		  AND p.posize = c.sizes
      )";

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.