SQL View Question

  • sdotnet

    SSC Enthusiast

    Points: 174

    Hi everyone,

    I've a little problem with a SQL View that there's no way to resolve (from my side). I've three tables:

    • Manage - Details - Payments (there's a GUID to link manage with other two)

    I would create a view to see in each row some information about each table. The point is that Details and Payments could have more than one entry and this means that the record is replicated many time. I tried with TOP 1, to restrict the result but there's no way to achieve the goal.

    This is what I'm using today

    SELECT DISTINCT dbo.Manage.ID, dbo.Manage.Type, dbo.Manage.Customer, dbo.Manage.Date, dbo.Details.Order, dbo.Details_Order_Date, dbo.Payments.Amount, dbo.Payments.PayDate, dbo.Payments.PayState

    FROM dbo.Manage INNER JOIN

    dbo.Details ON dbo.Manage.GUID = dbo.Details.Manage_GUID

    INNER JOIN

    dbo.Payments ON dbo.Manage.GUID = dbo.Payments.Manage_GUID

    WHERE (Details.ID =(SELECT TOP (1) ID

    FROM dbo.Details AS A2

    WHERE (Manage.GUID = Manage_GUID)))

    The idea is, even there are more values, to show one row per record? Any clue for this situation?

    Thanks

  • Jeff Moden

    SSC Guru

    Points: 996829

    WHICH ONE RECORD would be the right one to show?  Should it be based on one of the dates in columns for those tables?  If so, which column?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Phil Parkin

    SSC Guru

    Points: 244656

    Could you provide an example of the source data in the three tables, also what you are currently seeing, followed by what you would like to see?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jason A. Long

    SSC-Insane

    Points: 23682

    It sounds like you've already identified the cause of the problem. For any given "manage guid" every Detail row is related to every Payment row.

    You have to determine if this actually correct from a business point of view. My assumption is that it is not but you haven't provided nearly enough information to make that determination...

    Assuming that it IS NOT... You have to figure out how a given Detail row relates directly to a given Payment row.

    Of course, that all sounds "well and good" until you find that, for example, you submit 5 different invoices (all different amounts) totaling $5000 and the customer sends you two payments of $2500 each. The "total" figures match but the individual payments themselves don't match any of the individual invoices.

    That's is more complex but still doable and typically involves a "many to many" table that subdivides both invoices and payments.

  • Jonathan AC Roberts

    SSCoach

    Points: 17319

    Try TOP(1) in a CROSS APPLY.

    e.g.:

    SELECT DISTINCT 
    m.ID,
    m.Type,
    m.Customer,
    m.Date,
    d.[Order],
    d.Details_Order_Date,
    p.Amount,
    p.PayDate,
    p.PayState
    FROM dbo.Manage m
    CROSS APPLY(SELECT TOP(1) *
    FROM dbo.Details d
    WHERE m.GUID = d.Manage_GUID) d
    INNER JOIN dbo.Payments p
    ON m.GUID = p.Manage_GUID;
  • sdotnet

    SSC Enthusiast

    Points: 174

    Thank for the many replies....the idea is show the first row of others. The point is that I need to "call" same columns just to apply a filter into the query from asp.net.

    Maybe am I wrong but I don't think there's a way to create a view in a way and apply a filter into another (show me these but the query must be done from this -> with more columns)

  • Phil Parkin

    SSC Guru

    Points: 244656

    sdotnet wrote:

    Thank for the many replies....the idea is show the first row of others. The point is that I need to "call" same columns just to apply a filter into the query from asp.net.

    Maybe am I wrong but I don't think there's a way to create a view in a way and apply a filter into another (show me these but the query must be done from this -> with more columns)

    It's really very difficult to understand exactly what you mean without seeing some examples ... use dummy data if you like, but a few pictures and some actual code will really help here.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Collins

    SSC Eights!

    Points: 883

    sdotnet wrote:

    Thank for the many replies....the idea is show the first row of others. The point is that I need to "call" same columns just to apply a filter into the query from asp.net.

    Maybe am I wrong but I don't think there's a way to create a view in a way and apply a filter into another (show me these but the query must be done from this -> with more columns)

    Are you saying you'd like to return to C# (single item) properties from dbo.Manage and lists and/or properties from dbo.Details and/or dbo.Payments?

  • Jonathan AC Roberts

    SSCoach

    Points: 17319

    sdotnet wrote:

    Thank for the many replies....the idea is show the first row of others. The point is that I need to "call" same columns just to apply a filter into the query from asp.net.

    Maybe am I wrong but I don't think there's a way to create a view in a way and apply a filter into another (show me these but the query must be done from this -> with more columns)

    Have you tried using CROSS APPLY with SELECT TOP(1) with an ORDER BY?

    You can put this in a view.

  • Jeff Moden

    SSC Guru

    Points: 996829

    sdotnet wrote:

    Thank for the many replies....the idea is show the first row of others. The point is that I need to "call" same columns just to apply a filter into the query from asp.net.

    Maybe am I wrong but I don't think there's a way to create a view in a way and apply a filter into another (show me these but the query must be done from this -> with more columns)

    You have to describe HOW to determine the "first" row for the data you have.  You cannot rely on the implicit order as you think it currently appears.  There has to be a column in the data that you can test as the "First" row because SQL Server is otherwise likely to change what it thinks is the first row.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 9021

    The description my body. A something>> I've a little problem with a SQL View ... <<

    No, your actual problem is with the DDL. You don't know how relational databases work so you got a really bad schema design. You also have no manners because you failed to post the DDL as required.

    All we have is three table names; manage is a verb and not a tablename, details doesn't tell us what their details of and finally we have some kind of P. Could you be vaguer?

    We have references in RDBMS and not links. Huge difference! A GUID means a "global unique identifier" and it is properly used, to reference objects and data elements external to the current schema. Inside the schema, we have references to the columns of tables. You also don't understand the difference between a row and a record.

    >> I've three tables: ...<<

    And where is there DDL? I think you and us to design your entire system for you and you won't even show us what you've done. In a commercial database, a details table is usually what is called a weak entity. It exists only because of references to a strong entity. The usual thing is orders and order details, but other models are also common.

    >> The point is that Details and Payments could have more than one entry and this means that the record [sic] is replicated many time. <<

    Again, a row is not a record. But more important is your misconception about the purpose of databases in general. Even before there was SQL, we were trying to create systems that reduce redundancy. But your increasing redundancy! Boy I sure wish we had that DDL so it seemed exactly how messed up. This is

    >> I tried with TOP 1, to restrict the result but there's no way to achieve the goal. <<

    The highly proprietary, non-relational TOP(n) syntax is another sign of bad programming, as well as bad language design. RDBMS is supposed to be based on sets and sets have no ordering. If you have an ordering relationship, it should be represented according to Dr. Codd's rules with scalar values in the columns of the rows of a table. Also, the syntax has to have an ORDER BY clause to convert the table into a cursor, which can be ordered.

    Also, I been at this for over 30 years, and I've written less than five SELECT DISTINCT clauses in my entire career. They are very rare, if your schema is normalized improperly design. Your other column names are absurdly vague; there is no such thing in the universe is a generic type, a universal date, etc. Please read ISO 11179 naming rules.

    Also, why do you think that "Details AS A2" is a helpful alias for someone trying to read your code?

    >> The idea is, even there are more values, to show one row per record [sic]? <<

    Since we have no idea what your schema looks like, I would make a wild guess that somewhere in here you want to pick something with a MAX() or MIN() test on a row. Somewhere in one of these undefined tables

    . Would you like to be polite and try again with the DDL?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • sgmunson

    SSC Guru

    Points: 110504

    Oh for crying out loud, Joe...   just because the poster might not have the best skillset for what they are trying to accomplish, doesn't mean you should insult them.   Try asking more pleasant probing questions...  It's clear that there's a need for the poster to provide a lot more detail on these tables, as well as the nature of the relationships between them, even though the tech detail of the GUID value being the link has been provided.   That link knowledge is useful, but lacks any info on whether it's 1 to 1 or 1 to many or many to many.

    It seems likely that the sub-query to be joined to for each of the ancillary tables will need to either aggregate data (perhaps the payments table) or use a TOP (1) with an ORDER BY to pull some specific detail record.   We also don't know enough to make these judgments, as the poster hasn't provided that level of detail, and the existing posts just aren't very clear on what's what.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • sdotnet

    SSC Enthusiast

    Points: 174

    Thank you guys....I really appreciate your reply but nothing of you give me a solution. Everyone talking about the weather, the child and how to dry the rocks with towel but no solution. I don't want discuss about my method....I just asking if there's a way to achieve this goal:

    • 3 different tables
    • 1 guid that link two of them with main
    • table 1 is always a single row
    • table 2 and 3 can contains multi rows
    • I want create a view to see the merge of these table without have multi row because the table 2/3 have multi values - even the table 3 doesn't contains rows

    the TOP 1 doesn't works and this is the reason why I'm here. If there's a query to do this, and you want help me, good.....the rest is my problem. How to adjust the name, the position, the data and other.

    Thanks

  • Jason A. Long

    SSC-Insane

    Points: 23682

    If you want help that includes actual code, read this and do as it says.

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • Jeff Moden

    SSC Guru

    Points: 996829

    sdotnet wrote:

    Thank you guys....I really appreciate your reply but nothing of you give me a solution. Everyone talking about the weather, the child and how to dry the rocks with towel but no solution. I don't want discuss about my method....I just asking if there's a way to achieve this goal:

     

      <li style="list-style-type: none;">

    • 3 different tables

     

      <li style="list-style-type: none;">

    • 1 guid that link two of them with main

     

      <li style="list-style-type: none;">

    • table 1 is always a single row

     

      <li style="list-style-type: none;">

    • table 2 and 3 can contains multi rows

     

      <li style="list-style-type: none;">

    • I want create a view to see the merge of these table without have multi row because the table 2/3 have multi values - even the table 3 doesn't contains rows

     

    the TOP 1 doesn't works and this is the reason why I'm here. If there's a query to do this, and you want help me, good.....the rest is my problem. How to adjust the name, the position, the data and other.

    Thanks

    Being a smart ass about the replies isn't going to help.  You've not answered my specific question about how to determine which "one row" you want to use in the view.  You made several comments in that area but nothing useful for us to help.

    Good luck with your task.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

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