none
Query Require RRS feed

  • Question

  • Below is my data

    Create Table #tbl_item (IT_ID int,IT_Name varchar(50))
    insert INTO #tbl_Item values(1 ,A)
    insert into #tbl_Item values(2,B)
    
    CREATE TABLE #tbl_Import (Impno INT,Imp_bl_no int,imp_bl_date date,EntryDate date) 
    CREATE TABLE #tbl_Import_Detail(D_CID INT,Impno int,CN_ID int)  
    
    INSERT INTO #tbl_Import VALUES(1001,111,'01-06-2020','21-06-2020') 
    INSERT INTO #tbl_Import_Detail VALUES(1,1001,11)
    INSERT INTO #tbl_Import_Detail VALUES(2,1001,12) 
    
    CREATE TABLE #tbl_GrnM (GRN_ID INT,imp_no int,D_CQTY int,EntryDate  date)  
    CREATE TABLE #tbl_GRND (GRN_DID INT,GRN_ID int,IT_ID int,QTY int,Gweight int)
     
    INSERT INTO #tbl_GrnM VALUES(211,1001,1,'01-06-2020') 
    INSERT INTO #tbl_GRND VALUES(1,211,1,35,400)
    INSERT INTO #tbl_GRND VALUES(2,211,2,22,350) 
    
    
    CREATE TABLE #tbl_INVM (I_ID INT,imp_no int,EntryDate  date)  
    CREATE TABLE #tbl_INVD (ID INT,I_ID int,GRN_DID int,GRN_ID int,IT_ID int)
    
    insert into #tbl_INVM(2001 ,1001,'01-06-2020')
    insert into #tbl_INVD(1,2001,1,211,1)

    Output


    akhter


    • Edited by Akhterhussain Monday, July 13, 2020 11:03 AM Image upload
    Monday, July 13, 2020 11:02 AM

Answers

  • Hi

    select it.Imp_bl_no,it.imp_bl_date,i.IT_Name, gd.QTY,gd.Gweight,g.D_CQTY from #tbl_Import it
    inner join #tbl_GrnM g on it.Impno = g.imp_no
    inner join #tbl_GRND gd on g.GRN_ID = gd.GRN_ID
    inner join #tbl_INVM im on g.imp_no = im.imp_no
    inner join #tbl_INVD id on im.I_ID = id.I_ID
    inner join #tbl_item i on gd.IT_ID = i.IT_ID
    
    where id.IT_ID = i.IT_ID

    Thanks and Regards

    • Marked as answer by Akhterhussain Tuesday, July 14, 2020 12:13 PM
    Tuesday, July 14, 2020 12:03 PM

All replies

  • You're doing some things that no competent SQL programmer would do. For example, by definition, a table must have a key, but none of your code can ever have a key, because columns can all be NULL! You also don't know the correct format for dates in ANSI/ISO standard SQL. You don't know how the INSERT INTO statement works. The "tbl_" affix is called a "tibble" because it is such bad programming it has a name. Identifers can not be numeric because they are a nominal scale. Have you had a course in basic data modeling yet?

    CREATE TABLE Items 
    (item_id CHAR(4) NOT NULL PRIMARY KEY, 
     item_name VARCHAR(50) NOT NULL);

    INSERT INTO Items 
    VALUES ('0001', 'Alpha'), ('0002', 'Beta');

    CREATE TABLE Imports 
    (import_id CHAR(4) NOT NULL PRIMARY KEY, 
     import_bl_nbr INTEGER NOT NULL, 
     import_bl_date DATE NOT NULL, 
     entry_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

    INSERT INTO Imports
    VALUES ('1001', 111, '2010-06-01', '2020-06-21');

    CREATE TABLE Import_Details
    (import_detail_id CHAR(4)  NOT NULL,
     import_id CHAR(4) NOT NULL
        REFERENCES Imports,
     PRIMARY KEY (import_detail_id, import_id),
    cn_id  CHAR(2) NOT NULL);

    Your data element names are are so cryptic I cannot figure out what they mean or how they relate to each other. Please notice how the details (called a weak entity) has to reference its strong entity. This is a standard programming idiom in SQL.

    INSERT INTO Import_Details 
    VALUES
    ('0001', '0001', '11'),
    ('0002', '0001', '12') ;

    I guessed at primary keys, constraints and defaults, but these tables still make no sense. If you would like a real answer, then please post real DDL that makes sense, follows good design practices, etc.

    CREATE TABLE GrnM
    (grn_id CHAR(4) NOT NULL PRIMARY KEY,
      import_nbr INTEGER NOT NULL, 
     d_c_qty INTEGER NOT NULL CHECK (d_c_qty >= 0), 
     entry_date DATE DEFAULT CURRENT_TIMSTAMP NOT NULL);

    CREATE TABLE Grnd 
    (grnd_id CHAR(4) NOT NULL PRIMARY KEY,
      grn_id INTEGER NOT NULL,
      item_id INTEGER NOT NULL, 
     something_qty INTEGER NOT NULL,
       CHECK(something_qty >= 0),
     g_weight INTEGER NOT NULL);


     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Enric Vives Tuesday, July 14, 2020 1:00 PM
    Monday, July 13, 2020 6:32 PM
  • It's great that you post CREATE TABLE + INSERT statements. However, you should test your script before you post it. It produced several error messages, and I gave up correcting the errors after a while.

    It also helps if you explain what these cryptic table names stand for, and you describe the bueiness rules for why you want that particulr result.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 13, 2020 9:10 PM
  • Hi Akhterhussain,

    Please refer to below sql:

    Create Table #tbl_item (IT_ID int,IT_Name varchar(50))
    insert INTO #tbl_Item values(1 ,'A')
    insert into #tbl_Item values(2,'B')
    
    CREATE TABLE #tbl_Import (Impno INT,Imp_bl_no int,imp_bl_date date,EntryDate date) 
    CREATE TABLE #tbl_Import_Detail(D_CID INT,Impno int,CN_ID int)  
    INSERT INTO #tbl_Import VALUES(1001,111,'2020/06/01','2020/06/21') 
    INSERT INTO #tbl_Import_Detail VALUES(1,1001,11)
    INSERT INTO #tbl_Import_Detail VALUES(2,1001,12) 
    
    CREATE TABLE #tbl_GrnM (GRN_ID INT,imp_no int,D_CQTY int,EntryDate  date)  
    CREATE TABLE #tbl_GRND (GRN_DID INT,GRN_ID int,IT_ID int,QTY int,Gweight int)
    INSERT INTO #tbl_GrnM VALUES(211,1001,1,'2020/06/01') 
    INSERT INTO #tbl_GRND VALUES(1,211,1,35,400)
    INSERT INTO #tbl_GRND VALUES(2,211,2,22,350) 
    
    CREATE TABLE #tbl_INVM (I_ID INT,imp_no int,EntryDate  date)  
    CREATE TABLE #tbl_INVD (ID INT,I_ID int,GRN_DID int,GRN_ID int,IT_ID int)
    insert into #tbl_INVM VALUES(2001 ,1001,'01-06-2020')
    insert into #tbl_INVD VALUES(1,2001,1,211,1)
    
    
    select a.Imp_bl_no,a.imp_bl_date,d.IT_Name ,c.QTY,c.Gweight,b.D_CQTY
    from #tbl_Import a  
    join #tbl_GrnM b on a.Impno=b.imp_no
    join #tbl_GRND c on b.GRN_ID=c.GRN_ID
    join #tbl_item d on c.IT_ID=d.IT_ID
    where IT_Name='A'
    
     
    drop table #tbl_item
    drop table #tbl_Import
    drop table #tbl_Import_Detail
    drop table #tbl_GrnM
    drop table #tbl_GRND
    drop table #tbl_INVM
    drop table #tbl_INVD

    /*111 2020-06-01 A 35 400 1*/

    Best Regards

    Echo



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    • Edited by Echo Liuz Tuesday, July 14, 2020 6:17 AM
    Tuesday, July 14, 2020 6:15 AM
  • Hi Echo,

      Where is #tbl_INVM table,i have to retrieve record against  I_ID column 


    akhter

    Tuesday, July 14, 2020 7:43 AM
  • Hi

    select it.Imp_bl_no,it.imp_bl_date,i.IT_Name, gd.QTY,gd.Gweight,g.D_CQTY from #tbl_Import it
    inner join #tbl_GrnM g on it.Impno = g.imp_no
    inner join #tbl_GRND gd on g.GRN_ID = gd.GRN_ID
    inner join #tbl_INVM im on g.imp_no = im.imp_no
    inner join #tbl_INVD id on im.I_ID = id.I_ID
    inner join #tbl_item i on gd.IT_ID = i.IT_ID
    
    where id.IT_ID = i.IT_ID

    Thanks and Regards

    • Marked as answer by Akhterhussain Tuesday, July 14, 2020 12:13 PM
    Tuesday, July 14, 2020 12:03 PM
  • Hi Akhterhussain,

    select a.Imp_bl_no,a.imp_bl_date,d.IT_Name ,c.QTY,c.Gweight,b.D_CQTY
    from #tbl_Import a  
    join #tbl_GrnM b on a.Impno=b.imp_no
    join #tbl_GRND c on b.GRN_ID=c.GRN_ID
    join #tbl_item d on c.IT_ID=d.IT_ID
    join #tbl_INVD e on c.GRN_ID=e.GRN_ID
    join #tbl_INVM f on e.I_ID=f.I_ID
    where d.IT_ID=e.IT_ID

    Best Regards 
    Echo



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    • Edited by Echo Liuz Wednesday, July 15, 2020 8:59 AM
    Wednesday, July 15, 2020 8:01 AM