> I need to list all the master inventory table (plus the details) where a > specific stock appear in the details table. > > The following SQL shows me the result except that 'suppid' becomes > 'cardno'. > > SELECT cardmst.cardno, cardmst.card_date, cardmst.suppid, ; > carddet.stkcode, carddet.qty, carddet.price ; > FROM cardmst JOIN carddet ; > ON cardmst.cardid=carddet.cardid ; > WHERE cardmst.cardno IN ( ; > SELECT DISTINCT cardmst.cardno ; > FROM cardmst JOIN carddet ; > ON cardmst.cardid=carddet.cardid ; > WHERE carddet.stkcode='1' ) You mean that the cardmst.cardno values show up in the suppid column? This doesn't make any sense. IAC, try chopping down the statement, one clause at a time, until the problem goes away. First remove the WHERE cluse, then remove the carddet table. /Paul