Jump to content

Getting a row after comparing 2 values in the same column.


rhexis07

Recommended Posts

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

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

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

  • 1 month later...

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...