ciHnoN Posted January 30, 2013 Share Posted January 30, 2013 Sorry, I wasn't sure where to post an SQL specific question. Please move this thread to the correct place. I think I'm getting stupid or something, not sure why I cannot solve this! *lol*I got two tables TABLE1 and TABLE2. TABLE1 has some item ids which can have one or more linked values from TABLE2.Example:TABLE1.IDTABLE1.ITEMIDTABLE1.table2_IDTABLE2.IDTABLE2.DESCRIPTIONData sample:TABLE1:ID ---- ITEMID ---- table2_ID1 ---- SOMEITEMa ---- 22 ---- SOMEITEMa ---- 33 ---- SOMEITEMa ---- 4 4 ---- SOMEITEMb ---- 25 ---- SOMEITEMc ---- 2TABLE2:ID ---- DESCRIPTION1 ---- somedescription0012 ---- somedescription0023 ---- somedescription0034 ---- somedescriptoin004As you see, in TABLE1, I got 3 items "SOMEITEMa","SOMEITEMb","SOMEITEMc" which all share "description002" connected by TABLE2.ID to them.My question is: How do I select from TABLE1 only those ITEMIDs which have TABLE2.ID='2' and NO OTHER IDs connected to them? I basically want the result to show two rows from TABLE1, which is "SOMEITEMb" and "SOMEITEMc", and not "SOMEITEMa" because that item has other values connected to it. Eventhough is has TABLE2.ID='2' connected to it, I don't want to select it because it has other IDs from TABLE2 as well.Thanks! Link to comment Share on other sites More sharing options...
Tripredacus Posted January 30, 2013 Share Posted January 30, 2013 Which SQL are you using? MSSQL, MySQL or something else?Should be close enough for web development. When in doubt, if there is no section we always have the Hardware and Software sections. Link to comment Share on other sites More sharing options...
ciHnoN Posted January 30, 2013 Author Share Posted January 30, 2013 I'm using MSSQL. (Thanks) Link to comment Share on other sites More sharing options...
submix8c Posted January 30, 2013 Share Posted January 30, 2013 Been a LOOOONG time using SQL (MainFrame via Assembly/COBOL) but it seems that somewhere you will have to allow for a "count" or another "secondary" Read to see if there's another "ITEMID" existing (thus indicating more than one Table2_ID and/or building the Report in an independent temporary Table?How are the Key Fields set up - just the ID field in both tables? On a Mainframe, you could use a Secondary Key as necessary (depending on your needs).(that's an oddball you're asking for)Probably no help, but logically that seems to be what you may need to do. Link to comment Share on other sites More sharing options...
ciHnoN Posted January 30, 2013 Author Share Posted January 30, 2013 Thanks for the help submix8c but I really need an example of a SQL query. Kind'a urgent, need it for something at my work. hehe Link to comment Share on other sites More sharing options...
submix8c Posted January 30, 2013 Share Posted January 30, 2013 Sorry I can't help beyond "logically". Only have used basic functions in MS-Access so far (very similar). Now if this were being coded in said Mainframe languages (and access to said Mainframe), I could have give you exactly what you need post-haste. Have no full-blown SQL (MS-Access isn't AFAICT) installed. "For Each..."/ "End-For" Link to comment Share on other sites More sharing options...
Tripredacus Posted January 30, 2013 Share Posted January 30, 2013 Sounds like you need to do a JOIN. Read about the types here (generally speaking)http://www.w3schools.com/sql/sql_join.aspRemember MSSQL and MySQL has slightly different syntax. Link to comment Share on other sites More sharing options...
ciHnoN Posted January 30, 2013 Author Share Posted January 30, 2013 Thanks for the link. I know about JOIN. I been coding a long while now. But this specific scenario doesn't cover it. A bit tricky. :e Link to comment Share on other sites More sharing options...
ciHnoN Posted January 31, 2013 Author Share Posted January 31, 2013 *solved it* Turns out I had the IDs from second table so I only used one table.Something like:SELECT id FROM tableLEFT JOIN(SELECT id FROM table WHERE <some condition>[mine was id<10 or id>20]) AS AON table.id=A.idWHERE A.id IS NULL <---- the important part. <---- the important part. Cheers! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now