Member Avatar for vishal anand.s

hi my name is vishal for past 10 days i have been going nuts on how to combine selected datas from 3 or more tables into single sql select query in vb6.

i have 4 tables named

table1 name: Inward

column Name                         DataType
mfr_ref_number                       text

no_of_bundle                         integer

supplier_id                          integer

DC_date                             date/time

INV_date                            date/time

row_upd_date                        date/time

courier_name                         text

inward_type                          text

user_id                              long(contains 0 as value for all entries)

DC_NO                                text

closed_status                        boolean(true for some agn only)

to_dept                               text

agn                                   long

table2 name: item_master

Column Name                            DataType

user_id                               long(contains 0 as value for all entries)

row_upd_date                          date/time

deleted_status                        boolean(contains false value for all entries)

item_name                             text

item_parent                           long

item_price                            double

item_desc1                            memo

item_desc2                            memo

item_desc3                            memo

item_type                            long

item_code                            text

agn                                  long

measure_type                         text

table3 name: store_master

Column Name                           DataType

rack_name                             text

rack_desc                             memo

user_id                              long(contains 0 as value for all entries)

row_upd_date                         date/time

status                               boolean

store_no                              long

table4 name: Stock_Register

Column Name                               DataType

row_upd_date                              Date/Time

technician_id                               long

user_id                                    long(contains 0 as value for all entries)

in_out_type                                integer

status                                     boolean

deleted_status                             boolean

item_id                                     long

qty_debit                                  double

qty_credit                                  double

in_out_id                                   long

store_no                                    long

ref_id                                      long

sl_no                                       text

what i want is to write a sql select query which would contain agn values from Inward table,item_name values from item_master table,rack_name values from store_master table,store_no values from store_master table and no_of_bundle values from Inward table.

I myself have written sql query using little knowledge of sql queries and i get partial output. Query given below:
select i.no_of_bundle,sm.rack_name,sr.store_no,sr.in_out_type,i.agn,it.item_name from inward i,store_master sm,stock_register sr,item_master it where sm.status=true and sr.in_out_id=i.agn and sr.status=true and sm.store_no=sr.store_no and i.deleted_status=false

i know that i am in correct direction in achieving my required query. I think i must add or modify my sql query in Where clause but dont know how. Can anyone tell me what should be added or modified in my sql query. Can anyone help me please. Any help or guidance in solving getting the required sql query would be greatly appreciated.

  • 3 Contributors
  • 3 Replies
  • 339 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by abelingaw

Dani AI

Generated

Two quick fixes explain the partial result shown in the thread: an explicit JOIN between the item table and the stock register is missing, and the deleted_status predicate in the original WHERE is applied to the wrong table (the schema shown in Post #1 has deleted_status on item_master and stock_register, not on Inward). was right to suggest using JOINs — they make the relationships and filters explicit and much easier to debug. 's pointer to a JOIN tutorial is also useful background.

A concise example that follows the posted schema (adjust the item primary‑key reference if the real PK is a different column) — use INNER JOIN when only matched rows are wanted, or LEFT JOIN to keep unmatched rows for diagnosis:

SELECT i.no_of_bundle,
       it.item_name,
       sm.rack_name,
       sm.store_no,
       i.agn
FROM Inward AS i
INNER JOIN Stock_Register AS sr ON sr.in_out_id = i.agn
INNER JOIN Store_Master AS sm ON sm.store_no = sr.store_no AND sm.status = TRUE
INNER JOIN Item_Master AS it ON it.agn = sr.item_id
WHERE sr.status = TRUE
  AND it.deleted_status = FALSE;

Practical debugging steps: run each join one step at a time and compare row counts (Inward → Stock_Register; add Store_Master; then add Item_Master). If rows disappear when adding the item join, confirm which item_master column matches sr.item_id and whether NULLs exist. If some inward rows should appear despite missing stock rows, switch relevant INNER JOINs to LEFT JOIN to reveal missing links. Also mind DB differences for boolean literals (Access vs MySQL) and use the exact column names from the live schema.

Recommended Answers

All 3 Replies

Member Avatar for kareythomson

Why do not you try using joins. The result will be a one table.

Member Avatar for vishal anand.s

Well tell me how to use joins in my above query because i tried it before(i have used inner join) and i got error and was unable to fix it so i changed my query to the above query. Reply please how to apply joins correctly in my above query?

Member Avatar for abelingaw

Really no time to tackle this one so read THIS.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.