Two Tables in One Dataset

[vellaima]

Hello,

I have created "items" table and "spl_items" table. In items table i have item_id, item_desc. In spl_items i have item_id,qty. The item_id found in items table need not neccessary be in spl_items table.

Is it possible to write a select query to display in a datagrid all data from items table and the qty from spl_items. If there is no record for a particular item_id is found in spl_items table i would like to have qty value displayed as zero.

Items table

ID Desc

I001 HI
I002 IO


Spl Item Table

ID QTY

I001 2

I would like to display

ID Desc Qty
I001 HI 2
I002 IO 0

Can it be acheived? Please do help me out as i am desperate.

 

[quwiltw]

Did you want to do this in the dataset or in the database?

in the database just do a left join on Item_id:
select items.item_id, desc, qty from items left join spl_item on items.item_id = spl_item.item_id

in the dataset I think youll have to create a relationship, then a DataView to do this, if thats what you wanted, let me know and Ill see what I can do.

 

[vellaima]

Thanks for replying. I tried your query in the MS Access database. It asked me to enter "qty" value of spl_item table.

 

[quwiltw]

Is qty really the name of your quantity column? Change it to whatever the actual name of that column is.

 

[vellaima]

Oops... Sorry. Its working fine now. Thanks for your help. But it is not displaying zero where qty is blank. Can this be solved quwiltw.

 

[quwiltw]

Didnt catch that. Use the Nz function and replace the qty in the select with it. Something like this:
select items.item_id, desc, Nz(qty,0) As Quantity from items left join spl_item on items.item_id = spl_item.item_id

remember to change the qty to your column name for quantity.

 

[vellaima]

Thanks for providing me with a solution. It is working fine.