mcraw
Posts: 11
|
| Posted: 02/11/2009, 2:08 PM |
|
ALERT: for SQL gurus?
Let me make this simple as possible. Given the two tables below:
ClaimTable:
ID | Name | Other fields|
ClaimComments table:
ID | claimID | date | comment|
Note there are many comments per claim in the system.
I want to search on several ClaimTable fields, and with those matching records display only one claimtable record and the most recent claim comment from the ClaimComments table. The most recent comment is the one with the maximum value of ClaimComments.ID for the set of comments on any one claim.
I've had partial success selecting the MAX(ClaimComments.ID) AS MaxCCid and using GROUP BY ClaimTable.ID. My results display the correct max ClaimComments.ID. But what I really need is to display corresponding text for ClaimComments.Comments for the table index value of MaxCCid.
When I try something similar on MAX(ClaimComments.Comment) I get the wrong data since the max function uses the max numeric value of the characters in the field.
Any help is greatly appreciated, and forgive me if this is a simple SQL question! Have fought this one too long.
Thank you kindly,
Marshall
|
 |
 |
mentecky
Posts: 321
|
| Posted: 02/11/2009, 2:38 PM |
|
Marshall,
Try something like:
SELECT
ct.*,
cc.*
FROM
ClaimTable ct,
(SELECT claimID, MAX(ID) ID FROM ClaimComments GROUP BY claimID) x,
ClaimComments cc
WHERE
ct.claimID = x.claimID AND
cc.ID = x.ID;
That should be close.
Good luck!
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 3:13 PM |
|
Thanks Rick.
I knew it involved a subselect, just didn't know you could put one in the FROM part of a statement. Looks like I'll have to use sql mode in ccs, instead of the query builder for this?
PS - a small paypal gratuity is offered for the best help on this one! kinda pressing.
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 3:25 PM |
|
Marshall,
Yes. I don't know of a way to use query builder to pull this off, but in the SQL window you can add parameters and all of CCS's dropdowns will work with this method. I've used the SQL window in a lot of projects and it works well as long as your SQL statement is good.
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 3:33 PM |
|
Rick, in your code...
SELECT
ct.*,
cc.*
FROM
ClaimTable ct,
(SELECT claimID, MAX(ID) ID FROM ClaimComments GROUP BY claimID) x,
##################### ^^ --does this ID need a comma before it, or should it not be here?
ClaimComments cc
WHERE
ct.claimID = x.claimID AND
cc.ID = x.ID;
Also, not being a guru, should the subselect line above end with '...claimID) AS x,' ??
I get the following sql error:
MySQL server version for the right syntax to use near 'WHERE ClaimTable.ID = x.claimID AND ClaimComments.ID = x.ID
any help appreciated!
|
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 3:36 PM |
|
Better yet Rick, Here is the exact statement out of CCS.
SELECT Paid, payername, PayDate, AppliedAmt, notes, ClaimTable.*, Max(ClaimComments.ID) AS maxclaimcomment, Max(Comment) AS maxcomment
FROM (Payments INNER JOIN ClaimTable ON
Payments.claimID = ClaimTable.ID) INNER JOIN ClaimComments ON
ClaimComments.claimID = Payments.claimID,
(SELECT ClaimComments.claimID, MAX(ClaimComments.ID) FROM ClaimComments GROUP BY ClaimComments.claimID) AS x,
WHERE ClaimTable.ID = x.claimID
AND ClaimComments.ID = x.ID
AND Payments.AppliedAmt = {s_AppliedAmt}
AND Payments.PayDate = '{s_PayDate}'
AND Payments.payername LIKE '%{s_payername}%'
AND Payments.Inactive <> {Expr0}
AND Payments.Paid = {s_Paid}
AND ClaimTable.Status = '{s_status}'
AND ClaimTable.archived <> {Expr1}
AND ClaimTable.archived = {Expr2}
AND ClaimTable.snfPerson LIKE '%{s_snfPerson}%'
AND ClaimTable.ClientProject LIKE '%{s_ClientProject}%'
AND ClaimTable.snfclientname LIKE '%{s_snfclientname}%'
AND ClaimTable.facilityname LIKE '%{s_facilityname}%'
AND ClaimTable.first LIKE '%{s_first}%'
AND ClaimTable.last LIKE '%{s_last}%'
|
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 3:45 PM |
|
PS: ignore the two MAX(...) in the SELECT part. They were left over junk, and after removing them I get the same result as above....
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 3:49 PM |
|
Marshall,
In my initial SQL statement I did not include a few "AS" keywords but they are optional in most cases.
You probably don't need "Max(Comment) AS maxcomment" as the sub-select should only limit it to one comment returned. But it looks good to me.
Glad to help.
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 3:58 PM |
|
Thanks for your patience. here is the CCS sql query, exactly (I trimmed it down for testing):
SELECT Paid, payername, PayDate, AppliedAmt, notes, ClaimTable.*
FROM (Payments INNER JOIN ClaimTable ON
Payments.claimID = ClaimTable.ID) INNER JOIN ClaimComments ON
ClaimComments.claimID = Payments.claimID,
(SELECT ClaimComments.claimID, MAX(ClaimComments.ID) FROM ClaimComments GROUP BY ClaimComments.claimID) AS x,
WHERE ClaimTable.ID = x.claimID
AND ClaimComments.ID = x.ID
AND ClaimTable.last LIKE '%{s_last}%'
And here is the SQL error:
your MySQL server version for the right syntax to use near 'WHERE ClaimTable.ID = x.claimID AND ClaimComments.ID = x.ID AND ClaimTable.last ' at line 6
Thank you, and paypal will be your friend :)
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 4:04 PM |
|
Try,
(SELECT ClaimComments.claimID, MAX(ClaimComments.ID) AS ID FROM ClaimComments GROUP BY ClaimComments.claimID) AS x,
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 4:20 PM |
|
AARGh, we are probably within a character of success!
Trimmed the sql statement to:
SELECT ClaimTable.*, ClaimComments.*
FROM ClaimTable INNER JOIN ClaimComments ON
ClaimComments.claimID = Payments.claimID,
(SELECT ClaimComments.claimID, MAX(ClaimComments.ID) AS ID FROM ClaimComments GROUP BY ClaimComments.claimID) AS x,
WHERE ClaimTable.ID = x.claimID
AND ClaimComments.ID = x.ID
AND ClaimTable.last LIKE '%{s_last}%'
Still get this sql error:
MySQL server version for the right syntax to use near 'WHERE ClaimTable.ID = x.claimID AND ClaimComments.ID = x.ID AND ClaimTable.last ' at line 5
I have a version 5 mysql server, so I know its good on the subselect.
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 4:25 PM |
|
OK... this is tough without the actual tables but...
(SELECT ClaimComments.claimID AS claimID, MAX(ClaimComments.ID) AS ID FROM ClaimComments GROUP BY ClaimComments.claimID) AS x,
PS: PayPal donations are not required, but... you can contribute to my beer fund at:mentecky@3rc.us 
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 4:39 PM |
|
Unfortunately that change did not help, sql:
SELECT ClaimTable.*, ClaimComments.*
FROM ClaimTable INNER JOIN ClaimComments ON
ClaimComments.claimID = Payments.claimID,
(SELECT ClaimComments.claimID AS claimID, MAX(ClaimComments.ID) AS ID FROM ClaimComments GROUP BY ClaimComments.claimID) AS x,
WHERE ClaimTable.ID = x.claimID
AND ClaimComments.ID = x.ID
AND ClaimTable.last LIKE '%{s_last}%'
Same error reported.
Is there some way to get you the table structures from phpmyadmin or something to make it easier to diagnose? How about a PM on this board?
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 4:43 PM |
|
Marshall,
Sure post em here, PM me or send them to the email I posted in the previous message.
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 4:49 PM |
|
Ok, heres the structure, not the data....
--
-- Table structure for table `ClaimComments`
--
CREATE TABLE IF NOT EXISTS `ClaimComments` (
`ID` int(11) NOT NULL auto_increment,
`claimID` int(11) NOT NULL,
`username` varchar(50) NOT NULL,
`DateTime` datetime NOT NULL,
`Comment` longtext NOT NULL COMMENT 'multipe comments per claim, with user who made them',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9477 ;
--
-- Table structure for table `ClaimTable`
--
CREATE TABLE IF NOT EXISTS `ClaimTable` (
`ID` int(11) NOT NULL auto_increment COMMENT 'Main claim table with all necessary linked data',
`last` varchar(100) NOT NULL,
`first` varchar(100) NOT NULL,
`MI` varchar(100) default NULL,
`ResID` varchar(20) default NULL COMMENT 'Resident ID from Client System',
`responsibleParty` varchar(100) default NULL COMMENT 'Private Pay responsible party',
`phone` varchar(50) default NULL,
`phone2` varchar(50) default NULL,
`address` mediumtext,
`email` varchar(50) default NULL COMMENT 'Patient/Resposible party email',
`misc` mediumtext COMMENT 'misc, patient ids, etc',
`facilityname` varchar(50) NOT NULL,
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 4:51 PM |
|
Marshall,
ClaimTable is missing the end.
Rick
_________________
http://www.ccselite.com |
|
|
|
mcraw
Posts: 11
|
| Posted: 02/11/2009, 4:56 PM |
|
oops, didn't grab it all...
--
-- Table structure for table `ClaimTable`
--
CREATE TABLE IF NOT EXISTS `ClaimTable` (
`ID` int(11) NOT NULL auto_increment COMMENT 'Main claim table with all necessary linked data',
`last` varchar(100) NOT NULL,
`first` varchar(100) NOT NULL,
`MI` varchar(100) default NULL,
`ResID` varchar(20) default NULL COMMENT 'Resident ID from Client System',
`responsibleParty` varchar(100) default NULL COMMENT 'Private Pay responsible party',
`phone` varchar(50) default NULL,
`phone2` varchar(50) default NULL,
`address` mediumtext,
`email` varchar(50) default NULL COMMENT 'Patient/Resposible party email',
`misc` mediumtext COMMENT 'misc, patient ids, etc',
`facilityname` varchar(50) NOT NULL,
`snfclientname` varchar(50) NOT NULL,
`ClientProject` varchar(50) NOT NULL,
`snfPerson` varchar(50) NOT NULL,
`archived` tinyint(1) NOT NULL,
`StartDate` date NOT NULL,
`EndDate` date default NULL,
`Status` varchar(20) NOT NULL default 'new',
`nextdate` date default NULL,
`nextaction` varchar(50) default NULL,
`Balance` decimal(10,2) NOT NULL,
`TotalBilled` decimal(10,2) default NULL COMMENT 'not calculated, ref number',
`ClaimAmount` decimal(10,2) default NULL COMMENT 'Initial Claim amount',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7972 ;
|
|
|
|
mentecky
Posts: 321
|
| Posted: 02/11/2009, 5:39 PM |
|
You're right. One character off!!!
(SELECT ClaimComments.claimID AS claimID, MAX(ClaimComments.ID) AS ID FROM ClaimComments GROUP BY ClaimComments.claimID) AS x
Delete the comma before the WHERE.
Rick
_________________
http://www.ccselite.com |
|
|
|
mentecky
Posts: 321
|
| Posted: 02/12/2009, 1:59 PM |
|
Marshall,
Glad to help. Thanks for the beer! 
Rick
_________________
http://www.ccselite.com |
|
|
|
|
