March 16, 2018 at 3:37 am
Hi
I have a query that return more than one records can any one help me?
My query is
select LineItem.Price, document.Date, LineItem.fItemGID
from Document
left join LineItem on LineItem.DocumentGID = document.GID
left join
(select max(T1.Date) AS Date, max(T2.fItemGID) as fItemGID
from Document T1
left join LineItem T2 on T2.DocumentGID = T1.GID
) TB1 on TB1.Date = document.Date and TB1.fItemGID = LineItem.fItemGID
where document.Code like 'INV%'
AND LineItem.fItemGID = 'D28994F1-E883-4744-9376-0141A5C5D0C0'
and the result is
Price Date fItemGID
-------- --------------------- ------------------------------------
0,3535 19/7/2017 12:00:00 πμ D28994F1-E883-4744-9376-0141A5C5D0C0
0,4208 12/2/2018 12:00:00 πμ D28994F1-E883-4744-9376-0141A5C5D0C0
0,3675 23/2/2018 12:00:00 πμ D28994F1-E883-4744-9376-0141A5C5D0C0
But it must be
Price Date fItemGID
-------- --------------------- ------------------------------------
0,3675 23/2/2018 12:00:00 πμ D28994F1-E883-4744-9376-0141A5C5D0C0
Why?
Any help??
March 16, 2018 at 12:44 pm
It returns three rows because they all match your WHERE clause. The question is by what logic do you expect to only get your single result? Do you only want the most recent item? The middle price? Your solution might involve adding something like:this to your SELECT:
ROW_NUMBER OVER(PARTITION BY LineItem.fItemGID ORDER BY <something> ) as row
And then in an outer query selecting WHERE row = x (where x depends on if you want the top thing or the second thing, etc.).
March 16, 2018 at 2:40 pm
makis_best - Friday, March 16, 2018 3:37 AM>> I have a query that return more than one record [sic] can any one help me? <<I think we need to do some basic education here. First, rows are nothing whatsoever like records. There is no such thing as a "link table" in RDBMS; this term comes from the old network databases and has no place in SQL at all. Trust me on this. I did do work some of the standards. You don't know the ISO 8601 date format, which is the only one allowed in ANSI/ISO standard SQL (as well as an awful lot of other ISO standards). SQL uses a period, and not the European comma, for the decimal separator.
Finally, a data element does not change its name in the schema. This means that your stupid generic GID has to be totally different from your little more accurate "D.gid" you use later.
Table names have to be collective or plural nouns because a table models a set, not an individual record. A “date†is a data type in SQL; if it were an individual column, it would be a “<something in particular>_date†to be a valid data element name. And of course in RDBMS, GUID, UUID, etc. inside the schema. They refer to universal or global things that are not part of the schema. Basically you’re trying to write something between a network database and 00 database. But you’re using SQL and the thing as a total mess.
Having so many LEFT OUTER JOINS is another bad sign. It says that the tables have no references to each other, and you have to fake it at the time the query is executed. This is why basic netiquette for the last 35 years has been to post DDL.
Then of course it's impossible to answer your question because we don't have any DDL. How did you expect us to understand the structure of your schema without it? If you're working for someone who expects you to read their mind and punishes you when you don't, it's time to update your resume.
The idiom in RDBMS for weak entity in a strong entity is something like this:
CREATE TABLE Something_Documents
(document_id CHAR(15) NOT NULL PRIMARY KEY,
document_date DATE NOT NULL,
document_price DECIMAL(12,2) NOT NULL,
…);CREATE TABLE Something_Document_Items
(document_id CHAR(15) NOT NULL
REFERENCES Something_Documents (document_id)
ON DELETE CASCADE,…);
if there are GUID references to either the items or the hold documents, then we would declare such a column. It would be external to the schema and would not be used inside the schema.
If you go back and actually followed the forum posting rules, then you and get several hundred dollars worth of free consulting from people here 🙂 but remember that you’re probably going to have to throw out most of what you’ve already got done because it’s wrong.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 19, 2018 at 2:32 am
doug.brown - Friday, March 16, 2018 12:44 PMIt returns three rows because they all match your WHERE clause. The question is by what logic do you expect to only get your single result? Do you only want the most recent item? The middle price? Your solution might involve adding something like:this to your SELECT:
ROW_NUMBER OVER(PARTITION BY LineItem.fItemGID ORDER BY <something> ) as row
And then in an outer query selecting WHERE row = x (where x depends on if you want the top thing or the second thing, etc.).
I expect to get only one result cuz the result of the second query in the join return only the last date. max()
If I run only that script then I can see the last date returned.
March 20, 2018 at 6:56 am
makis_best - Monday, March 19, 2018 2:32 AMdoug.brown - Friday, March 16, 2018 12:44 PMIt returns three rows because they all match your WHERE clause. The question is by what logic do you expect to only get your single result? Do you only want the most recent item? The middle price? Your solution might involve adding something like:this to your SELECT:
ROW_NUMBER OVER(PARTITION BY LineItem.fItemGID ORDER BY <something> ) as row
And then in an outer query selecting WHERE row = x (where x depends on if you want the top thing or the second thing, etc.).I expect to get only one result cuz the result of the second query in the join return only the last date. max()
If I run only that script then I can see the last date returned.
LEFT OUTER JOIN is not going to restrict the number of rows you get back. Try adding columns with the TB1 alias to your select, and you will probably see the problem, as those values will likely be NULL for any rows except the one you expect. Change the join to an INNER join and you are likely to see the right result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 25, 2018 at 10:11 am
sgmunson - Tuesday, March 20, 2018 6:56 AMmakis_best - Monday, March 19, 2018 2:32 AMdoug.brown - Friday, March 16, 2018 12:44 PMIt returns three rows because they all match your WHERE clause. The question is by what logic do you expect to only get your single result? Do you only want the most recent item? The middle price? Your solution might involve adding something like:this to your SELECT:
ROW_NUMBER OVER(PARTITION BY LineItem.fItemGID ORDER BY <something> ) as row
And then in an outer query selecting WHERE row = x (where x depends on if you want the top thing or the second thing, etc.).I expect to get only one result cuz the result of the second query in the join return only the last date. max()
If I run only that script then I can see the last date returned.LEFT OUTER JOIN is not going to restrict the number of rows you get back. Try adding columns with the TB1 alias to your select, and you will probably see the problem, as those values will likely be NULL for any rows except the one you expect. Change the join to an INNER join and you are likely to see the right result.
oh..... I understand now....
Thank you for your help.
I make it through.
March 27, 2018 at 11:51 am
makis_best - Sunday, March 25, 2018 10:11 AMsgmunson - Tuesday, March 20, 2018 6:56 AMmakis_best - Monday, March 19, 2018 2:32 AMdoug.brown - Friday, March 16, 2018 12:44 PMIt returns three rows because they all match your WHERE clause. The question is by what logic do you expect to only get your single result? Do you only want the most recent item? The middle price? Your solution might involve adding something like:this to your SELECT:
ROW_NUMBER OVER(PARTITION BY LineItem.fItemGID ORDER BY <something> ) as row
And then in an outer query selecting WHERE row = x (where x depends on if you want the top thing or the second thing, etc.).I expect to get only one result cuz the result of the second query in the join return only the last date. max()
If I run only that script then I can see the last date returned.LEFT OUTER JOIN is not going to restrict the number of rows you get back. Try adding columns with the TB1 alias to your select, and you will probably see the problem, as those values will likely be NULL for any rows except the one you expect. Change the join to an INNER join and you are likely to see the right result.
oh..... I understand now....
Thank you for your help.
I make it through.
Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply