Post reply

Help the query

  • July 19, 2012 at 12:09 pm

    #264442

    Hi Guys,

    Thanks for advance,

    i have 3 tables like 1.MainProduct,2.sub product,3. product this tables

    main product :

    1. mainproduct_id,mainproduct_name,mainproduct_id

    example data

    1,sample,1

    2,sample1,2

    subproduct:

    1.subproduct_id,subproduct_name,sub_mainproduct_id

    example data

    1,testing,2

    2,testing1,2

    3,testing2,3

    product:

    1.productID,product_name,mainproduct_id,subproduct_id

    exampledata

    1,vegtables1,1,2

    2,vegitables2,1,3

    i need a query result like single row in where condition main product

    Thanks for advance

  • July 19, 2012 at 12:15 pm

    #1515081

    my wild guess:

    SELECT *

    FROM [mainproduct]

    LEFT OUTER JOIN [subproduct]

    ON [mainproduct].mainproduct_id = [subproduct].mainproduct_id

    LEFT OUTER JOIN [product]

    ON [subproduct].subproduct_id = [product].subproduct_id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2012 at 12:24 pm

    #1515087

    I can't tell what you're asking for.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 19, 2012 at 12:31 pm

    #1515095

    Thanks for your reply,

    Actually i am passing the product id only so if 2 sub product we are getting 2 set of results, i need to avoid those type of result set.

    Except Example result set :

    1,sample,testing1,2,vegtables1,1,2

    but i am getting multiple result set while i am using joins please help me to reslove this query.Thanks for advance

    Thanks

    Ranganathan palanisamy

  • July 19, 2012 at 12:34 pm

    #1515100

    ranganathanmca (7/19/2012)

    Thanks for your reply,

    Actually i am passing the product id only so if 2 sub product we are getting 2 set of results, i need to avoid those type of result set.

    Except Example result set :

    1,sample,testing1,2,vegtables1,1,2

    but i am getting multiple result set while i am using joins please help me to reslove this query.Thanks for advance

    Thanks

    Ranganathan palanisamy

    the problem is, we are on the other side of the interwebz.

    unless you provide CREATE TABLE... code, INSERT INTO.. code for the same data, and the current query you are actually using, we can't help. we can make wild guess, but that it.

    help us help you...provide the code we can copy and paste to test your query, and we can offer suggestions.

    CREATE TABLE mainproduct (

    mainproduct_id varchar(max),

    mainproduct_name varchar(max))

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2012 at 12:49 pm

    #1515108

    Thanks for your suggestion,

    find the insert command 3 tables

    1.INSERT INTO LINEUPBACKUP

    ([FLOW_ID]

    ,[FLOW_NAME]

    ,[FLOW_DESC]

    ,[METER_ID]

    ,[FLOW_ID])

    VALUES

    (<FLOW_LINEUP_ID, int,>

    ,<FLOW_LINEUP_NAME, varchar(50),>

    ,<FLOW_LINEUP_DESC, varchar(150),>

    ,<METER_ID, int,>

    ,<FLOW_ID, int,>)

    GO

    2.INSERT INTO [ProMIS].[dbo].[PROMIS_UMB_REPORT]

    ([REPORT_ID]

    ,[REPORT_NAME]

    ,[REPORT_UNIT_ID]

    VALUES

    (<REPORT_ID, int,>

    ,<REPORT_NAME, varchar(100),>

    ,<REPORT_UNIT_ID, int,>

    GO

    3.INSERT INTO FLOW

    ([FLOW_ID]

    ,[FLOW_NAME]

    ,[FLOW_TYPE]

    ,[UNIT_ID])

    VALUES

    (<FLOW_ID, int,>

    ,<FLOW_NAME, char(20),>

    ,<FLOW_TYPE, char(1),>

    ,<UNIT_ID, int,>)

    GO

    My query

    SELECT

    REPORT_ID AS REPORT_ID,

    rtrim(ltrim(REPORT_NAME)) AS REPORT_NAME,

    ISNULL(REPORT_UNIT_ID,0) as REPORT_UNIT_ID ,

    FROM

    REPORT AS REPORT

    LEFT OUTER JOIN BIFLOW AS BIFLOW ON REPORT.REPORT_UNIT_ID=BIFLOW.UNIT_ID

    LEFT OUTER JOIN LINEUP AS LINEUP ON BIFLOW.FLOW_ID= LINEUP.FLOW_ID

    WHERE

    REPORT_ID=44

    ORDER BY

    REPORT_NAME

  • July 19, 2012 at 1:01 pm

    #1515120

    i tried pasting your code in SQLserver management studio, and it gives errors;

    are you working in another language, or did you paste pseudocode?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2012 at 1:05 pm

    #1515124

    No , i am not english only.

    Thanks for your effort i got result.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply