Ok, I have searched the forums and the web, and I dont think this is available, but I am going to ask for any ideas...
I need to show a parameterized TOP x report with collapsible detail. For example:
+ CA
+ NY
+ GA
+ CO
+ PA
+ All Others
Again, the user is able to select how many group rows are shown.
Any suggestions or ideas?
Thanks!!
BobP
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
Hi,I'm trying to build a query that get only one row from a group ofrows, but I need the values from that row and not the results of onefunction group.I need one row for each idRef, with column2=2 and the bigger column1id |idRef | column1 | column21 1 0 12 1 1 23 1 2 14 2 0 15 2 1 26 2 2 17 2 3 2For these, I will take the rows with id=2 and id=7.Thank you, and sory for my english.
View 2 Replies View RelatedOkay, I am sure this is an easy question, but for some reason I cannot wrap my brain around it. I have a table that has data similar to the following:
IPAddress
VisitDate
URLVisited
192.154.21.554
9/18/2007
http://www.microsoft.com
192.154.21.554
9/19/2007
http://www.google.com
164.21.124.23
9/19/2007
http://www.microsoft.com
192.154.21.554
9/20/2007
http://www.yahoo.com
What I am trying to do is use a select query to find the most recent visit per IP address (but I also want the other row data like URL visited). I have something like this that is finding the most recent visit per IP address:
SELECT IPAddress, MAX(VisitDate) AS MaxDateVisited
FROM VisitorTable
GROUP BY IPAddress
How do I also pull in the URL that is associated with the MAX date visited? Thank you!
Hi,
Is it possible to keep a group in a table report on the one page if this group could be fitted into the rest of the page and start new page otherwise?
Thanks,
Igor
Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg
Row A = NoteID 1, FK_JobID 1, UnRead
Row B = NoteID 2, FK_JobID 1, UnRead
Row C = NoteID 3, FK_JobID 1, UnRead
I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.
I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.
Heres my attempt
DECLARE @UserID INT
SET @UserID = 4
SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact,
Master_Jobs.Job_Title, Master_Jobs.JobID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID
WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND
BeenRead = 'UnRead'
Thanks in advance
Hi,
I'm using SQL Server 7.0. My problem is that I'd like to count how many rows are in a group by. For example, here is my data:
Agent Branch
1 1
2 1
1 1
2 1
1 2
1 2
If I do this:
Select Agent, Branch
From Table1
Group By Agent, Branch
I get:
Agent Branch
1 1
2 1
1 2
What I need to do is count the records (3) in the group by. I've tried a few things but I can't seem to come up with the number of rows in a group by.
Can anyone help me?
Thanks in advance,
Darrin
creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below
Itemcode, Description, Period1, Period2, Period3
001--------Desc--------233,---------,------- <<ROW1
001--------Desc ------------ ,100,------------ <<ROW2
001--------Desc ------,--------,-------,300 <<ROW3
Row one contains a value in Period1
Row two contains a value in Period 2
Row three contains a value in Period 3
I need to create a query so that the end result would look like this;
Itemcode, Description, Period1, Period2, Period3
001 --------Desc -------233 ----100 ---300
creating an sql query to get the above result?
Code:
Room ID Hours StartTime EndTime
GR41908101700
GR42409101300
GR43317102000
LR111108100900
LR112109101000
LR113210101200
LR114112101300
LR115313101600
LR116216101800
LR117118101900
LR118119102000
LR119320102300
As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?
Hi Folks,
I am stuck in forming a query.
My age wise employee count sample data (department wise) is as shown below.
Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7
I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.
Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.
Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36
Thanking you in anticipation.
Jabez.
My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.
I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.
SQL Server 2005
Table (many columns left out for simplicity)
ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....
Typically there is approximately 250 records per HdrID.
I am trying to do an SQL UPDATE without using my usual solution of writing vb code!
I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.
ie;
ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00
In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.
Jake
Hello.
I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to the group field or somw where in the report.
How can I count how many rows do I have in each group?
Thanks.
Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.
hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you
Hi,
I have posted this in the Experts Forum so I have put replies to questions in here as well (Hence the long post!!). Hope someone here can help!!
I am having trouble with a T-SQL query. I have three tables in a join and I need to limit the results returned by query using a group by.
All of the fields in the result group are the same except for two fields, a date field and a varchar field. What I want to achieve is to return the row that has the latest date, and I need the varchar field as that is the information I am after. The problem is if I include the varchar field in the group by, it is returned as a separate group, if I do not included it in the group by, Server: Msg 8120 (aggregate fn / group by) error occurs!
Any ideas how to get around this?
Sample code and results below:
Query:
select distinct s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
from [coded] c join sec s on s.id = c.id
join [mkt-security] m on m.id = s.id
where ([market-id] = [scheme] or [market-id]+'CODE' = [scheme])
Group by s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
Sample results:
id : [active-from] : code : desc : [scheme] : [market-id]
--------------------------------------------------------------------------------------------------------------------------
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :1986-10-24 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
The result I want to achieve is to only return these two rows:
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
i.e. those with the latest [active-from] date, but I must have the corresponding code value.
I have tried a correlated sub query on the [active-from] field, but it is possible to have different id’s with the same [active-from] date so it did not work.
The primary key for the [coded] table is the combination of [active-from], code and [scheme]. The tables are truncated and then imported back into the SQL database from a Progress database daily, so restructuring the table(s) is not a possibility.
I have rewritten the query to "AND [ACTIVE_FROM] in (SELECT MAX([ACTIVE_FROM]) FROM CODED AND MAX([CODE]) = S.[CODE] AND MAX([SCHEME]) = S.[SCHEME]", (Which I think is the T-SQL for you query), but it returned the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
Also I think the query need to use a Group by as the results I need returned are those with the latest [ACTIVE_FROM] date WITHIN the group of [ID]and [MARKET-ID]. I can't think of any conditions where I can filter the results in the where clause.
The [ ] in the T-SQL is to identify table names as some of the table names (from the Progress db) use characters (the "-") that are unsupported for table names in T-SQL.
I am using the group by as I want to return specific groups of results ( [ID] and [MARKET-ID] ), that have the latest active-from date. I have made a change to that posted before to illustrate:
All results:
id : [active-from] : code : desc : [scheme] : [market-id] : [market-id]+’CODE’
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
10769 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
The result I want to achieve is to only return these three rows:
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
The reason the last row needs to be returned is that it has a different market id to the other row, despite its active-from date being earlier.
I have tried the self join earlier, but it would then only return 1 of the rows that have an id of 10769!!
Hope this makes sense!!
Cheers,
Guytz
I have a table, Table1 with 3 columns as follows: colItemKey, colGrouping1, colGrouping2.
colItemKey is the primary key. Say colGrouping1 has 4 different types: Grp1A, Grp1B, Grp1C and Grp1D and colGrouping2 has 5 as follows: Grp2A, Grp2B, Grp2C, Grp2D and Grp2E. How do I setup my select so that the result set is as follows:
Grp2A Grp2B Grp2C Grp2D Grp2E
Grp1A nnn nnn nnn nnn nnn
Grp1B nnn nnn nnn nnn nnn
Grp1C nnn nnn nnn nnn nnn
Grp1D nnn nnn nnn nnn nnn
I have a table which stores datewise Transactions of different items. Fields and sample data is
RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*
Now i want to select last row for each item (indicated by *). Is it possible in one single statement.
Hi
How do i group 2 table rows?
I have a report with a table and it has 58 rows, in some of the rows i am displaying the data like this
Roth contribution (Heading)
data for the roth contribution.
So depending on the options checked sometimes the heading Roth contribution is at the end of the page and the data is the next page. so how can i group 2 table rows together.
I have tried clicking on the 2 rows and grouping it, but nothing seems to appear in the report..
any help will be appreciated.
Regards,
Karen
Hi
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId
Returns
TeamMemberId MonthId
1 14
6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName
Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO
The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.
Regards,
Stefan
Sales Representative: ABC
"CustomerID"Company NameEmail AddressJun 2015Jul 2015
5033397TOPS FOR SHOESsales@topsforshoes.com$654.85$476.01
5034550Goodnightiesmarcia@goodnighties.com$538.80$295.93
5060377United States Lighting Corpjim@uslightingcorp.com$998.71$658.93
5084043mark Bettencourtbetten1@aol.com$70.97$60.07
5084382Arcana Empothecaryjames@arcanaempothecary.com$2,295.97$2,718.05
5098491Tony WisenTony.Wisen@EDMSupplies.com$40.69$94.37
5099747Crest Ridge Saddlerysaddles@crestridgesaddlery.com$733.86$518.63
5103478Ann Aurburndps@nhicwestmi.com$258.62$289.91
5110483Intimidation Clothing, LLCluke@intimidation-clothing.com$145.28$138.42
Total $5,737.75$5,250.32
I have dataset ready having sales rep ID  and other column like customer ID, company name, Email adress and transaction month as shown above how can i make a  row group by sales rep id and each tab (when we export report to excel)  I mean one tab for A sales repID , another tab for B...I did row group  parent group and group by sales repID but not working as expected.
Hi,
I have data that is grouped by a code number. One of the code numbers have over 600 rows, while other code numbers have around 10 to 20 rows within it.
When I run the report the code number that has over 600 rows gets split over 2 pages while the other code numbers each get their own page.
How do I make it so that when I run the report the code number that has over 600 rows gets all displayed on the 1 page instead of being split over 2?
Thanks
Ben.
How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.
If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.
Hello Friends,
I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but I want it at the front .
so it will be like this,
First the total should be displayed then the group members value.
Can anyone help me on this issue.
Thanks & Regards
Shivanandan Gupta
I have a table Transaction that looks something like the following :
TransactionIDÂ Â Â Â Â Â Â Â Â Â
Currency        Credit             Debit
1Â Â Â Â Â USDÂ Â Â Â Â 500Â Â Â Â Â Â Â Â Â Â Â Â Â 0
2     Afcu          6000            0
[Code] ....
I write query like this
select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source
And it came like
Balance Source         Â
1500Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â USD
6000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Afcu
6800Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INR
7000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Pfc
-200Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AUD
But I also want to add Afcu , Pfc with USD and want output like
Balance Source
14500Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â USD
6800Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INR
-200Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AUD
I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.
Example
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3
The result I want
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3
How can I add a group number to the following query?
For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):
RN          ReportDate Category            Division            TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1           2015-06-08 Field Sales           CA BDM             299743154.3912
2           2015-06-07 Field Sales           CA BDM             299765954.0354
3           2015-06-01 Field Sales           CA BDM             297902654.4172
1           2015-06-08 Key Accounts          Life Office           49954981.74
2           2015-06-07 Key Accounts          Life Office           50016989.22
3           2015-06-01 Key Accounts          Life Office           50169967.26
4           2015-05-31 Key Accounts          Life Office           50169918.01
Becomes
GN  RN          ReportDate Category            Division         TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1    1           2015-06-08 Field Sales           CA BDM           299743154.3912
1    2           2015-06-07 Field Sales           CA BDM           299765954.0354
1    3           2015-06-01 Field Sales           CA BDM           297902654.4172
2    1           2015-06-08 Key Accounts          Life Office           49954981.74
2    2           2015-06-07 Key Accounts          Life Office           50016989.22
2    3           2015-06-01 Key Accounts          Life Office           50169967.26
2    4           2015-05-31 Key Accounts          Life Office           50169918.01
i.e. each combination of Category+Division results in a new GN.
The query is:
selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN
I am using Sql Server 2008 R2.I have a existing query that basically says
Select Top 50 Subscriber_ID, Â Member_Name, Group_ID
from my_table
order by rand(checksum(newid()))
However the client now wants to have at least two from each group_id. There are 17 different groups. When I run this as is I get about six of the 17 groups in the results. How can I change this to get at least two results from each group_id?
How to get result from Query by group of (n) rows ?
- Is cursors the better way (and the only) ?
-Is there something as ROWNUM in Oracle or LIMIT in MySql ?
Example plz !
Thanks.
Alex
I need to convert the column to rows but within group. example
Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11
This need to be transposed to :
Group a b c d
p1234
q56NULL7
r891011
I have a table of attributes set up as follows:
ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
Hello
I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to
(group1) status1 10 $200,000
(detail) p1 1 $5,000
p2 1 $10,000
.
.
p10 1 $20,000
(group1) status3 5 $90,000
(detail) .
.
(group1) status4 15 $150,000
(detail) .
.
In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg
(group1) status1 10 $200,000
(detail) p1 1 $5,000
p2 1 $10,000
.
.
p10 1 $20,000
(group1)status2 0 $0
(group1) status3 5 $90,000
(detail) .
.
(group1) status4 15 $150,000
(detail) .
.
ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals.
Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset.
As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic.
Thanks
Stewart
Hi guys,
need some help here please...
The code below shows 4 rows.
The first two rows are almost identical, but the two of them exists in the same table as different rows.
Row number 1 is also related to Row number 3 and Row number 2 is also related to Row number 4
The problem is that I have to use only one of then (Rows number 1 or 2) togheter with row 3 & 4.
I thought using GROUP BY RECEIPTJURNALMATCH.JURNALTRANSID, but getting error.
Thanks in advance,
Aldo.
Code Snippet
SELECT
RECEIPTJURNALMATCH.JURNALTRANSID AS 'R.JURNALTRANSID',
RECEIPTJURNALMATCH.MATCHNUM AS 'R.MATCHNUM',
JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY',
JURNALTRANSMOVES.SUF AS 'J.TOTAL',
STOCK.REMARKS AS 'S.REMARKS'
FROM
RECEIPTJURNALMATCH
INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID
LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID
WHERE
JURNALTRANSMOVES.ACCOUNTKEY IN ('123456')
Below the results:
R.JURNALTRANSID R.MATCHNUM J.ACCOUNTKEY J.TOTAL S.REMARKS
89634 16702 123456 1155 ×¢×—: ;5752
89634 16703 123456 1155 ×¢×—: ;5752
89637 16702 123456 400 NULL
89639 16703 123456 155 NULL