July 27, 2020 at 12:23 pm
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:
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
July 27, 2020 at 1:30 pm
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
Change is inevitable... Change for the better is not.
July 27, 2020 at 1:31 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 27, 2020 at 2:34 pm
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.
July 27, 2020 at 2:36 pm
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;
July 27, 2020 at 2:46 pm
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)
July 27, 2020 at 2:54 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 27, 2020 at 3:49 pm
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?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 27, 2020 at 4:14 pm
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.
July 27, 2020 at 4:59 pm
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
Change is inevitable... Change for the better is not.
July 27, 2020 at 5:24 pm
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.
July 29, 2020 at 3:20 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 31, 2020 at 4:17 am
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:
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
July 31, 2020 at 4:32 am
If you want help that includes actual code, read this and do as it says.
July 31, 2020 at 1:22 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply