Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Nested Select SQL Question

    Hi guys, I am a newbie in db world and I am stuck in a situation, I need to select all companies with its default address if one exist. Here is an example of schema. (ID column is using Guid data type just in case)

    Company and address have 1:n relation
    CompanyID.ID => Address.CompanyID

    Code:
    Company			
    C1	ABC		
    C2	XYZ		
    C3	ZMR		
    			
    Address			
    A1	C3	Adderss1	TRUE (IsDefault='True')
    A2	C3	Address2	FALSE
    A3	C1	Address3	TRUE
    A4	C1	Address4	FALSE
    			
    Required Result			
    C1	ABC	A3	Address3
    C2	XYZ		
    C3	ZMR	A1	Adderss1
    Regards,
    Sahil
    Last edited by Sahil91; 04-17-11 at 23:38. Reason: thread title update

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    No nested select required, just left join from company to address on address.companyID = company.companyID and address.IsDefault = true

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Thanks for reply EngadaSQL

    I had tried the same approach but it wouldn't work. It won't show company record if it doesn't have Default Address and I want to show all companies whether it has address or not. Address should only be there in case of Address.IsDefault = 'TRUE'

    Here is the original query I had tried:

    Code:
    SELECT  Company.CompanyID,
            Company.DisplayID,
            Company.CompanyGroupID,
            CompanyGroup.Name AS Header ,
            ( ( Company.Name + ' (' ) + Company.DisplayName ) + ')' AS Company ,
            Company.Description,
            Company.TickerSymbol AS Ticker ,
            Company.Employees,
            Company.WebAddress AS Website ,
            Company.CreateUserID,
            Creator.Username AS CreateUsername ,
            Company.CreateDate,
            Company.EditUserID,
            Editor.Username AS EditUsername ,
            Company.EditDate,
            Company.OptimisticLockField ,
            Company.GCRecord AS Gcrecord ,
            Company.ImportDate,
            ( Address.Street + ' ' ) + Address.Locality AS AddressStreet ,
            Address.PINCode
    FROM    ( ( ( ( Accessor Creator
                    RIGHT JOIN Company ON Creator.AccessorID = Company.CreateUserID
                  )
                  LEFT JOIN Accessor Editor ON Editor.AccessorID = Company.EditUserID
                )
                LEFT JOIN CompanyGroup CompanyGroup ON CompanyGroup.GroupID = Company.CompanyGroupID
              )
              LEFT JOIN Address ON Company.CompanyID = Address.CompanyID
            )
    WHERE   ( ( Company.TypeID = 1 AND Address.IsDefault='TRUE') )
    Last edited by Sahil91; 04-18-11 at 09:18. Reason: Edit 1: made more descriptive

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT  Company.CompanyID,
    	Company.DisplayID,
    	Company.CompanyGroupID,
    	CompanyGroup.Name AS Header ,
    	Company.Name + ' (' + Company.DisplayName + ')' AS Company ,
    	Company.Description,
    	Company.TickerSymbol AS Ticker ,
    	Company.Employees,
    	Company.WebAddress AS Website ,
    	Company.CreateUserID,
    	Creator.Username AS CreateUsername ,
    	Company.CreateDate,
    	Company.EditUserID,
    	Editor.Username AS EditUsername ,
    	Company.EditDate,
    	Company.OptimisticLockField ,
    	Company.GCRecord AS Gcrecord ,
    	Company.ImportDate,
    	Address.Street + ' ' + Address.Locality AS AddressStreet ,
    	Address.PINCode
    FROM Company
    	LEFT OUTER JOIN Accessor AS Creator ON
            Creator.AccessorID = Company.CreateUserID
    	LEFT OUTER JOIN Accessor AS Editor ON 
    		Editor.AccessorID = Company.EditUserID
    	LEFT OUTER JOIN CompanyGroup AS CompanyGroup ON 
    		CompanyGroup.GroupID = Company.CompanyGroupID
    	LEFT OUTER JOIN Address ON 
    		Company.CompanyID = Address.CompanyID AND
    		Address.IsDefault = 'TRUE'
    WHERE	Company.TypeID = 1
    Don't mix RIGHT and LEFT OUTER JOINS. It is confusing. When you use OUTER JOINS, only use LEFT OUTER JOINs. It is easier to understand for the developer and I read the performance may be even better.

    Are you sure you have to use LEFT OUTER JOINs for all the joins? It only seems logical for the Address.
    Is it possible for companies to have no Creator or no Editor or to belong to no CompanyGroup? When they always have one, change those lefties to INNER JOINs.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    @Wim, thanks for the query, it worked.

    About your question, companies always have creator but editor gets a value once it gets updated otherwise it is Null, and yes it is not necessary to have a CompanyGroup for a company, this can be Null as well.

    I had the impression that this can be done using Nested Select (hence the Thread Title). Please, let me know if theres another way of writing this query. Thanks
    Last edited by Sahil91; 04-18-11 at 15:01.

  6. #6
    Join Date
    Nov 2011
    Posts
    2

    field of sub total and and relatıon show sub total record show

    my problem is i have a 1 table table structure is
    fkod=firm code
    sno=record index number
    sy1 =stock code
    sy2=date
    sy3=stock kilogram
    sy4=stock per

    fkod sno sy1 sy2 sy3 sy4
    CL0 1 980 1
    CL1 1 920 1
    CL1 2 920 1
    CL1 1 980 01.01.201 1 1
    CL1 2 980 01.01.201 1 1
    CL1 3 980 01.01.201 1 1
    CL1 4 980 01.02.201 1 1
    CL1 5 980 01.01.201 1 1
    CL1 6 980 01.01.201 1 1
    CL1 7 980 01.01.201 1 1
    CL1 8 980 01.01.201 1 1
    CL1 9 980 1
    CL1 10 980 1
    CL1 11 980 1
    CL1 12 980 1

    vısual basic basıc index key =fkod+sy1+sno
    sub total
    sy3 sy4
    cl0 980 1
    cl1 920 2
    cl1 980 12 8

    ı want create show grope records and sub total in combo box how can sql create syntax

    ı wrıte but ı cant suspended


    Dim sql1 As String
    scmb.Clear

    Set sorgu1 = db.OpenRecordset(sql1)
    sql1 = "select fkod ,sy1 from stok WHERE fkod order BY fkod,sy1 in (select fkod,sy1,sum(sy3) as sy33,sum(sy4) as sy44 from stok WHERE fkod GROUP BY fkod,sy1)"

    Do While Not sorgu1.EOF
    scmb.AddItem sorgu1!fkod & " -" & sorgu1!sy1 & " -" & sorgu1!sy3& " -" & sorgu1!sy4
    sorgu1.MoveNext
    Loop



    pls someone help me

  7. #7
    Join Date
    Nov 2011
    Posts
    2

    query each record show and sub total and general total

    figure 1
    stock code dbase in stock table
    index key= fkod+sy1+sno

    i'll want and prepare listbox in each record show and under fkod and sy1 and sno subtotal show
    i wrıte sql1 query for listbox object but ı cant succesfull ıt

    Set sorgu1 = db.OpenRecordset(sql1)


    be carefull syntax thıs ==>>> sql1 = "select fkod ,sy1 from stok WHERE fkod order BY fkod,sy1 in (select fkod,sy1,sum(sy3) as sy33,sum(sy4) as sy44 from stok WHERE fkod GROUP BY fkod,sy1)"

    Do While Not sorgu1.EOF
    scmb.AddItem sorgu1!fkod & " -" & sorgu1!sy1
    sorgu1.MoveNext
    Loop


    ı want listbox each record list in listbox object and subtotal show and generaltotal show

    under fıgure result fıgure

    fkod sno sy1 sy2 sy3 sy4
    CL0 1 980 1 0
    cl0 980 0 1
    subt 1 1

    CL1 1 920 1 1
    CL1 2 920 1 1
    subt 2 2

    CL1 1 980 01.01.2011 1 5,5
    CL1 2 980 01.01.2011 1 1
    CL1 3 980 01.01.2011 1 1
    CL1 4 980 01.02.2011 1 1
    CL1 5 980 01.01.2011 1 1
    CL1 6 980 01.01.2011 1 1
    CL1 7 980 01.01.2011 1 1
    CL1 8 980 01.01.2011 1 1
    CL1 9 980 1 1
    CL1 10 980 1 1
    CL1 11 980 1 1
    CL1 12 980 1 1
    subt 12 16.5

    general total 15 19.5


    may you someone help me thıs syntax for sql1

Posting Permissions

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