same cardname in diff cardtype

[former_member209725]

Dear Experts,

I was needed a query in which I can find same Cardname from two diff Cardtype ('Customers' and 'Vendors').

Any suggestion will be highly appreciated.

Regards,

Ravi

[former_member197621 · Accepted solution]

Hi,

Try this below query and let me know your feedback,

select cardcode,cardname,Balance,CardType from ocrd G1
where CardName in  (select CardName from OCRD where CardName = g1.CardName and CardType = 'C') 
and CardType = 'S'
union all


select cardcode,cardname,Balance,CardType from ocrd G1
where CardName in  (select CardName from OCRD where CardName = g1.CardName and CardType = 'S') 
and CardType = 'C'

[former_member209725 · Accepted solution]

Hi Prasanna,

Solved.

Thanx .

[kothandaraman_nagarajan]

Hi,

For clarification:

Is there difference in query result between me and Mr.Prasanna?

Thanks & Regards,

Nagarajan

[former_member209725]

Hi Nagarajan,

There is a difference between both queries.

I was needed only those BP Master which must have both Cardtype, and not those who are multiple CardCodes in same Cardtype. So ypur query shows those Cardname also which do not have both cardtype but multiple Cardcodes in same cardtype.

Anyways, I really appreciate your consideration towards my thread.

Thanks,

Ravi

[kothandaraman_nagarajan]

Hi,

Thanks for feedback.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T0.[Balance] FROM OCRD T0 left join ( select cardname from ocrd) t1 on t0.cardname = t1.cardname GROUP BY T0.[CardCode], T0.[CardName], T0.[CardType], T0.[Balance]

having count(t0.cardname) >1

Thanks & Regards,

Nagarajan

[Former Member]

Hi!

This might help also,

SELECT

T1.[CardName], T0.[CardCode] 'Supplier Code', T1.[CardCode] 'Customer Code'

FROM OCRD T0 

INNER JOIN OCRD T1 ON T0.[CardName] = T1.[CardName]

WHERE

T1.[CardType] = 'C' AND 

T0.[CardType] = 'S'

Regards,

[Former Member]

Just some modification on the first query.

SELECT

T1.[CardName], T0.[CardCode] 'Supplier Code', T0.[Balance] 'Supplier Balance',

T1.[CardCode] 'Customer Code', T1.[Balance]  'Customer Balance', (T0.[Balance] + T1.[Balance]) 'Net'

FROM OCRD T0

INNER JOIN OCRD T1 ON T0.[CardName] = T1.[CardName]

WHERE

T1.[CardType] = 'C' AND

T0.[CardType] = 'S'

Regards,

[KennedyT21]

Hi Ravi

Try This

select cardname,COUNT(cardname) from ocrd

group by CardName having COUNT(cardname)>1

Regards,

Kennedy

[former_member209725]

Hi Kennedy,

Thanks for your prompt reply.

I tried above query, but it doesn't segregate Customer and Vendor Master.

Required fields are as under:

CardName, Cardcode, Cardtype, Balance

Kindly see.

regards,

Ravi

[KennedyT21]

Try this

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T0.[Balance] FROM OCRD T0

  GROUP BY T0.[CardCode], T0.[CardName],

T0.[CardType], T0.[Balance]

having count(t0.cardname) >0

order by t0.CardName

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[CardCode], T0.[CardName], T0.[CardType] FROM OCRD T0 WHERE T0.[CardName]  ='TEST'

Thanks & Regards,

Nagarajan