Did the simple example I posted earlier work for you? Do you have to specify the database to use somewhere, or are the tables owned by somebody other than you? What database system are you using and what sort of software do you use to connect to it?
There might be a potential typo on the identifiers you're using on the "ON" statement. Can you check if "SPRIDENT_CURRENT_PERS_LAST_NAME" should actually be "SPRIDEN_CURRENT.PERS_LAST_NAME"? The identifier naming convention should be "[Table Name].[Column Name]"
OK, after looking further into it it looks like the tables are wrong. I think there is just 1 SPRIDEN table. So how would I compare SPRIDEN_LAST_NAME where SPRIDEN_NTYP_CODE = LEGAL and SPRIDEN_LAST_NAME where SPRIDEN_NTYP_CODE = TRAN and they do not match. Is that doable? That PERS_LAST_NAME is an actual field, I just don't know what table it is on.
You can join a table to itself. Code: Select DISTINCT (legal.SPRIDEN_CURRENT_ID) From SPRIDEN legal Inner Join SPRIDEN trans On legal.userID = trans.userID And legal.SPRIDEN_LAST_NAME <> trans.SPRIDEN_LAST_NAME And legal.SPRIDEN_NTYP_CODE = 'LEGAL' And trans.SPRIDEN_NTYP_CODE = 'TRANS'
Since you are joining a table to itself you have to assign it different names, otherwise it wouldn't be able to figure out what SPRIDEN.SPRIDEN_LAST_NAME (or any other columns) was referring to.
Is USERID an actual column? I just put that there as a placeholder. You'll have to substitute whatever the column name is that stores the user's ID.
I have written SQL to pull from a specific table but I want the code to pull only those IDs with one record in the table. Here is my code: Code: select asu_f_get_id(sftregs_pidm) id, sftregs.* from sftregs where sftregs_term_code='201160' AND ; Is there a piece of code where I can limit the IDs pulled (sftregs_pidm) to those that have just one record (sftregs_crn) with a specific status code (sftregs_rsts_code). I've googled this and looked through my SQL books with no luck at all.
If you're looking to obtain IDs with just one record, you will probably need to use an aggregate function. I'm guessing it'll look something like this: Code: SELECT sftregs_pidm, COUNT(sftregs_crn) FROM sftregs GROUP BY sftregs_pidm HAVING COUNT(sftregs_crn) = 1
Thanks for the help! I have combined it but I cannot get it to work where all or specific field in the sftregs table are displayed, it is just the ID. In my previous code the sftregs.* line gave me all the fields in my output but I cannot get that line to work now. If I could just get sftregs_crn to show that would be good. Code: SELECT asu_f_get_id(sftregs_pidm) id, COUNT(sftregs_crn) FROM sftregs WHERE sftregs_term_code = 201160 AND sftregs_rsts_code IN ('RE','RW') GROUP BY sftregs_pidm HAVING COUNT(sftregs_crn) = 1 ;
The aggregate function combines multiple records to display on one line so it will not know how to interpret *. It is not smart enough to know which data to use out of the multiple records. You will likely need to list out every single column. In addition, you will also likely need to add an aggregate function to each of those columns in order for it to run, e.g. MAX([column name]). I hope that makes sense.
You can use the aggregate function as part of the where clause to only select records returned by that function. I think this would work. Code: SELECT asu_f_get_id(sftregs_pidm) id, * FROM sftregs WHERE sftregs_term_code = 201160 AND sftregs_rsts_code IN ('RE','RW') AND sftregs_pidm in ( SELECT sftregs_pidm FROM sftregs GROUP BY sftregs_pidm, sftregs_crn HAVING COUNT(sftregs_crn) = 1 )
Try the following. Not sure if you need a '' around 201160 too. SELECT asu_f_get_id(sftregs_pidm) id, sftregs.* FROM sftregs WHERE sftregs_term_code = 201160 AND sftregs_rsts_code IN ('RE','RW') AND sftregs_pidm in ( SELECT sftregs_pidm FROM sftregs GROUP BY sftregs_pidm, sftregs_crn HAVING COUNT(sftregs_crn) = 1 )
