rhexis07 Posted December 6, 2007 Share Posted December 6, 2007 Hi all. I'm having a problem getting the row I desire if I have to compare 2 values of the same column. I have looked around, tried AND, OR and IN methods but they fail to return what I want, so I'm seeking help here.Here's an example table, and a few sample inputs.EXAMPLETABLEprimaryKey | userid | groupid | 1 | 01 | A | 2 | 01 | B | 3 | 02 | A |The thing I need to do is retrieve the userid that is in both group A AND B. This means that I should not get userid 02, and should only have 01 as my final results.Anyone have any idea how this should be done?I've triedSelect userid from EXAMPLETABLEwhere groupid = 'A' and groupid = 'B' (This returns me nothing.)Select userid from EXAMPLETABLEwhere groupid = 'A' or groupid = 'B' (This returns me 01 and 02.)Select userid from EXAMPLETABLEwhere groupid IN ('A', 'B') (This returns me 01 and 02.)Any kind of help would be much appreciated. Thanks . Link to comment Share on other sites More sharing options...
justsomeguy Posted December 6, 2007 Share Posted December 6, 2007 You can either use a subquery or a join, a join will be more efficient.subquery: SELECT userid FROM exampletable WHERE userid IN (SELECT userid FROM exampletable WHERE groupid='A') AND userid IN (SELECT userid FROM exampletable WHERE groupid='B') join: SELECT a.userid FROM exampletable AS a, exampletable AS b WHERE a.userid = b.userid AND a.groupid='A' AND b.groupid='B' Link to comment Share on other sites More sharing options...
rhexis07 Posted December 6, 2007 Author Share Posted December 6, 2007 Thank you so very much. :)I don't think I can use the 2nd example provided as I have I'm doing a few inner joins, and my SQL statements are being generated by passing some values into variables, and manually coded. (It's a very complicating issue.). I've yet to test the 1st code with my java, but I've tested it in SQL and it works. Thank you again. Link to comment Share on other sites More sharing options...
rhexis07 Posted January 20, 2008 Author Share Posted January 20, 2008 Hello.Sorry for bumping an old topic, but I'm encountering the same problem, and I think creating a new topic just for it is a little in appropriate.I have a table called mbs_audittrail_detail, and it has the following columns,Audittrailno | SessionID | Amount | Trans_Code01 | 01 | 1000 | SBMN05RQ02 | 01 | 2000 | ACWDXXRQ03 | 01 | 0000 | RDXXXXRQ04 | 02 | 1500 | SBMN05RQ05 | 02 | 1500 | ACWDXXRQThe problem I have is, I'd like to retrieve the distinct count of sessions, sum of amount, and distinct count of auditno from this table where trans_code is SBMN05RQ, ACWDXXRQ and RDXXXXRQ and where sessionID is the same for all 3. SELECT COUNT(DISTINCT MAT.sessionID) as sessions, Sum(amount) as turnover, count(distinct audittrailno) as transactions FROM MBS_AuditTrail_Detail MATD LEFT JOIN MBS_AuditTrail MAT ON MATD.AUDITTRAILNO = MAT.auditNo WHERE MATD.trans_code LIKE 'SB%' and MATD.sessionid in (select sessionid from mbs_audittrail_detail where trans_code like 'AC%') and MAT.sessionid in (select sessionid from mbs_audittrail_detail where trans_code like 'RD%') group by MAT.accountno The above is my current query. This query retrieves me the correct count of distinct sessionid, which in this case is 1. However, for my Sum(amount) as turnover and count(distinct audittrailno), it only returns me 1000 and 1 respectively. I understand the problem. It's because of my WHERE MATD.trans_code LIKE 'SB%' , which only retrieves me the information of the first row. I know we can't AND the same column, so is there any suggestions? Link to comment Share on other sites More sharing options...
rhexis07 Posted January 22, 2008 Author Share Posted January 22, 2008 Hi guys. I'd just like you all to know that I've solved my problem on my own. Thank you. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.