how to join two tables problem

Nirmal KumarC

12y1.5k

I want two tables join using table id not match or any one table id null means not get that value.

CO_BATCH_MASTER Table: get it data
cmn_minor_code Course_start_date Batch_ID
FPFF 18 Jan 2010 B6252
ARPA 18 Jan 2010 B6298
HUET 19 Jan 2010 B7595
ECP 19 Jan 2010 B7621
HUET 20 Jan 2010 B7622
MFA 20 Jan 2010 B6509
CTF 11 Jan 2010 B4985

BATCHID Table get it data

BID EID B_ACTIV UserId
B6252 FPFF/B699 AVEDHA
B6509 MFA/B516 AVEDHA

select top 1 @EID=EID from BATCHID,CO_BATCH_MASTER CBM where BID= CBM.cbm_batch_id and B_ACTIV<>'D' and CBM.cbm_active<>'D' and cbm_batch_start_dt<@cbm_batch_start_dt and CBM.cmn_minor_code=@cmn_minor_code ORDER BY cbm_batch_start_dt DESC

when i run the above query

output as follows
CERBID CEREID B_ACTIVE UserId E_dt
B6252 FPFF/B700A A Nirmal 2014-09-23
B6298 FPFF/B700A A Nirmal 2014-09-23
B7595 FPFF/B700A A Nirmal 2014-09-23

But i want output as follows

B6252 FPFF/B700 A Nirmal 2014-09-23 14:53:30.687

from my above query what is the mistake i made?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

DRISHTY12y ago
Accepted answer
Hi,

Comment last two conditions and run your query. you will get needed output :

SELECT TOP 1 EID,BID
FROM BATCHID
    ,CO_BATCH_MASTER CBM
WHERE BID = CBM.Batch_ID
    AND B_ACTIV <> 'D'
    AND CBM.cbm_active <> 'D'
    --AND cbm_batch_start_dt < @cbm_batch_start_dt
    --AND CBM.cmn_minor_code = @cmn_minor_code
--ORDER BY cbm_batch_start_dt DESC

Check that proper values are coming in last two conditions like this :

SELECT TOP 1 EID,BID,cbm_batch_start_dt,@cbm_batch_start_dt,CBM.cmn_minor_code,@cmn_minor_code
FROM BATCHID
    ,CO_BATCH_MASTER CBM
WHERE BID = CBM.Batch_ID
    AND B_ACTIV <> 'D'
    AND CBM.cbm_active <> 'D'
    --AND cbm_batch_start_dt < @cbm_batch_start_dt
    --AND CBM.cmn_minor_code = @cmn_minor_code
--ORDER BY cbm_batch_start_dt DESC

OR USE INNER JOIN IN YOUR QUERY :

SELECT TOP 1 EID
FROM BATCHID BI INNER JOIN
    CO_BATCH_MASTER CBM ON BI.BID = CBM.Batch_ID
WHERE B_ACTIV <> 'D'
    AND CBM.cbm_active <> 'D'
    AND cbm_batch_start_dt < @cbm_batch_start_dt
    AND CBM.cmn_minor_code = @cmn_minor_code
ORDER BY cbm_batch_start_dt DESC
0
Nirmal KumarC12y ago
Very Useful Thanks
0
Jignesh Trivedi12y ago
Hi,
Can you please provide more information...
If I use you data it give me a different result...
0
DRISHTY12y ago
Hi,

Can you please show data of rest of the column which are used in where condition?
0