asked on

Retrieve all record from table

Hi ,

I have two tables, Items and Rate.
Items table having columns - itemcode,uid,itemname and rate.
Rate table having columns - itemcode, uid, rate and acntcode.

Records in Items table

Records in Rate table

I have written a query that used the left join to display the records

select i.itemcode,i.rate,i.itemname,r.rate,r.acntcode 
from rate r left join items i on i.itemcode=r.itemcode

Open in new window

I Want the records in the manner that all items along with rate from Rate table and append NULL for acntcode and Rate if no record found in Rate table as explained below:

So i can retrieve all the items for a particular acntcode type.

Thanks in advance for the help.

Nitin Sontakke

Always a good practice to give tables as script or text, which we can use to quickly regenerate scenario in SSMS...just a thought. Images are not quite useful for such.

Nitin Sontakke

Hello,

Here is a script, if someone else is also attempting to solve:

declare @items table
(
 itemcode varchar(10)
 ,[uid] integer
 ,[rate] decimal(10, 2)
 ,itemname varchar(20)
)

insert into @items values
('I-00001', 7, 12.50, 'TESTITEM')
,('I-00002', 7, 125.00, 'TEST2345')
,('I-00003', 7, 120.00, 'TOP')
,('I-00004', 7, 135.0, 'XYZ')

declare @rate table
(
 itemcode varchar(10)
 ,[uid] integer
 ,[rate] decimal(10, 2)
 ,acntcode varchar(20)
)

insert into @rate values
('I-00001', 7, 40.00, '000001')
,('I-00002', 7, 145.00, '000002')
,('I-00003', 7, 789.00, '000003')
,('I-00004', 7, 500.00, '000001')
,('I-00004', 7, 34567.00, '000004')

Open in new window

Somehow, I am just not been able to identify the pattern...if you could help with that

amrish80

ASKER

Hello Nitin,

Thanks for the help. I need all the records from Items table along with modified rate for a particular acntcode and if no entry exists in rate table then the query should append NULL for the rate in rate table.

As in the above picture if rate exists only for itemcode "I-00001" and "I-00004" in the rate table for a particular ACNTCODE '000001', then query must display all the four items from the items table for acntcode '000001' instead of two.

Thanks
Amrish

HainKurt

I could not get what is all about but

with a as (select distinct acntcode from @rate)
select *
  from @items i inner join a on 1=1
  left join @rate r on i.itemcode=r.itemcode and r.acntcode=a.acntcode
 order by a.acntcode, i.itemcode

Open in new window

giving

itemcode	uid	rate	itemname	acntcode	itemcode	uid	rate	acntcode
I-00001	7	12.50	TESTITEM	000001	I-00001	7	40.00	000001
I-00002	7	125.00	TEST2345	000001	NULL	NULL	NULL	NULL
I-00003	7	120.00	TOP	000001	NULL	NULL	NULL	NULL
I-00004	7	135.00	XYZ	000001	I-00004	7	500.00	000001
I-00001	7	12.50	TESTITEM	000002	NULL	NULL	NULL	NULL
I-00002	7	125.00	TEST2345	000002	I-00002	7	145.00	000002
I-00003	7	120.00	TOP	000002	NULL	NULL	NULL	NULL
I-00004	7	135.00	XYZ	000002	NULL	NULL	NULL	NULL
I-00001	7	12.50	TESTITEM	000003	NULL	NULL	NULL	NULL
I-00002	7	125.00	TEST2345	000003	NULL	NULL	NULL	NULL
I-00003	7	120.00	TOP	000003	I-00003	7	789.00	000003
I-00004	7	135.00	XYZ	000003	NULL	NULL	NULL	NULL
I-00001	7	12.50	TESTITEM	000004	NULL	NULL	NULL	NULL
I-00002	7	125.00	TEST2345	000004	NULL	NULL	NULL	NULL
I-00003	7	120.00	TOP	000004	NULL	NULL	NULL	NULL
I-00004	7	135.00	XYZ	000004	I-00004	7	34567.00	000004

Open in new window

then select from here what you need...

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amrish80

ASKER

Thanks, HainKurt, that is exactly what is required.

Thanks
Amrish