Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Red face Unanswered: Help With T-SQL Query

    Dear All SQL Guru,

    Currently I am stuck with a t-SQL query for retriving a user defined out put.

    Sample query and data given below

    DECLARE @t TABLE (
    EXCEPTION_ID INT IDENTITY,
    Exception_Type char (15),
    Main_Product varchar(60),
    Sub_Product varchar(60),
    Product_Code varchar (40),
    Relation varchar (40)
    )
    INSERT INTO @t(Exception_Type, Main_Product, Sub_Product,Product_Code,Relation)
    SELECT 'INVALID', 'DP', 'DP', '10092535', 'Client'
    UNION
    SELECT 'INVALID', 'MF', 'MF', 'KSEC2', 'FIRST USER'
    UNION
    SELECT 'INVALID','PMS', 'PMS Wealth', 'PMSC2', 'Client'
    --SELECT * FROM @t

    DECLARE @clnt TABLE (
    CRN_ID INT IDENTITY,
    Main_Product varchar(60),
    Sub_Product varchar(60),
    Product_Code varchar (40),
    Relation varchar (40)
    )
    INSERT INTO @clnt(Main_Product, Sub_Product,Product_Code,Relation)
    SELECT 'DP', 'DP', '10092535', 'Second Holder'
    UNION
    SELECT 'MF', 'MF', 'KSEC2', 'FIRST USER'
    UNION
    SELECT 'DP', 'DP', '11092535', 'Client'
    UNION
    SELECT 'DP', 'DP', '11092535', 'Second Holder'
    UNION
    SELECT 'PMS', 'PMS Wealth', 'PMSC2', 'Client'
    --SELECT * FROM @clnt ORDER BY MAIN_Product
    Hear I have two table 1 is @t (Invalid data table) and second @clnt (Client Table)

    I have client data with the relation as Client and their belonging data with the relation as Second holder. All other field should be remain same (i.e Main Product, Sub Product and Product Code) for both relation record.

    From above query I want output like this


    main_product sub_product product_code relation
    DP DP 11092535 Client
    DP DP 11092535 Second Holder
    MF MF KSEC2 FIRST USER
    PMS PMS Wealth PMSC2 Client

    If a client having relation Client and exist in both table then record should be come from clnt table.

    If a client having relation Client and exist in @t table and Second Holder record exist in @clnt table then none of the record come.

    If a client having relation Client and Second Holder record exist in @clnt table then both of the record should be come.

    Kindly provide me way to achive this task.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    afraid that you've not given enough information there

    what is a client?
    how do you identity a client in the sample data?
    how do t and cint relate to each other?

    and any more information you can give
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Oct 2009
    Posts
    2

    Post

    Dear Sir,

    Client is a type of data which is exist in the column of Relation.
    In Sample data client is identify by with the combination of below mention column:
    Main_Product
    Sub_Product
    Product_Code
    Relation

    I written a query for required output, but this query may be cause of performance isue.
    Query for output mention below
    select a.main_product, a.sub_product, a.product_code, a.relation
    froM @clnt a
    inner join @t b
    ON a.main_product = b.main_product
    and a.sub_product = b.sub_product
    and a.product_code = b.product_code
    and b.Exception_Type in ('INVALID', 'PARTIAL', 'PROB')
    and a.relation in ('Client', 'First Holder', 'First User')
    group by a.main_product, a.sub_product, a.product_code, a.relation
    UNION
    Select a.main_product, a.sub_product, a.product_code, a.relation
    froM @clnt a
    left join @t b
    ON a.main_product = b.main_product
    and a.sub_product = b.sub_product
    and a.product_code = b.product_code
    and b.Exception_Type in ('INVALID', 'PARTIAL', 'PROB')
    and b.relation in ('Client', 'First Holder', 'First User')
    where b.exception_id is null
    group by a.main_product, a.sub_product, a.product_code, a.relation
    order by a.product_code, a.main_product

    I want a very short query for the same. Kindly provide me a way to achieve this.


    Regards
    Vijay

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    still having a little trouble getting my head around what your trying to do

    but just cleaning up your query give you
    select a.main_product, a.sub_product, a.product_code, a.relation,Exception_Type
    froM @clnt a
    inner join @t b
    ON a.product_code = b.product_code
    and a.relation in ('Client', 'First Holder', 'First User')
    and b.Exception_Type in ('INVALID', 'PARTIAL', 'PROB')
    UNION
    Select a.main_product, a.sub_product, a.product_code, a.relation
    froM @clnt a
    left join @t b
    ON a.product_code = b.product_code
    and b.Exception_Type in ('INVALID', 'PARTIAL', 'PROB')
    and b.relation in ('Client', 'First Holder', 'First User')
    where b.exception_id is null

    a product code should be a unique ID so matching on the text of the product names is a waste of time and resource. also as you are not using aggregates there group bys are pointless and again a waste. finally the database does not care about the order so only ever sort the data reight before you show it to the user never as part of a work query

    also in the examples given the
    and b.Exception_Type in ('INVALID', 'PARTIAL', 'PROB')
    and b.relation in ('Client', 'First Holder', 'First User')
    are redundant however this is because the sample always matches these criteria if the real data wont then they are not. hence why i have not removed them
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •