Post reply

Selecting rows from 2 Tables

  • January 24, 2008 at 11:50 am

    #378135

    Here's another one that's stumped me, I need to look through 2 tables, one with is Gradstudents, and the other which is Teachersassistants(TA) to find all the people who are not TAs.

    The answer is 140, but when i try to run the query below, i don't get a result.

    Select distinct g.loginid, lastname, firstname

    From gradstudents g

    join ta t

    on g.loginid = t.loginid

    Where classnumber <> ('544','444','100','142','143','326','370','378','457','467','594')

    What do i need to change in order to get 140 as a result?

    Thanks again everyone.

    Vinni

  • January 24, 2008 at 12:27 pm

    #771464

    when you get a problem where it say find data that is NOT [whatever]

    you typically need to use a LEFT OUTER JOIN, instead of an INNER JOIN

    the word "join" in your query really means inner join...it's good practice (I think) to write the whole thing out.

    so if you do the left outer join (or maybe even a FULL JOIN...try both)

    then you can try adding "WHERE GRADKEY IS NULL" or "WHERE TAKEY IS NULL" to find the exceptions to the join of the two tables.

    try taht and see if it helps.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 24, 2008 at 12:29 pm

    #771465

    You should write the Where clause as

    Where classnumber Not In ('544','444','100','142','143','326','370','378','457','467','594')

    -Roy

  • January 24, 2008 at 12:43 pm

    #771476

    I tried both LEFT OUTER JOIN and FULL JOIN, but it still yields no results.

    Maybe i should include more info about the 2 tables.

    TA (Teacher'sAssistants) Table

    loginid classnumer quarter

    rap544 Spring 1999

    rap444 Autumn 1999

    zives544 Spring 1999

    gerome100 Autumn 1999

    bonham142 Autumn 1999

    goshi142 Autumn 1999

    jwkim142 Autumn 1999

    zook142 Autumn 1999

    klee142 Autumn 1999

    karenliu142 Autumn 1999

    bart143 Autumn 1999

    sarahs143 Autumn 1999

    igor143 Autumn 1999

    zasha326 Autumn 1999

    deepak370 Autumn 1999

    cgordan378 Autumn 1999

    tjames378 Autumn 1999

    will457 Autumn 1999

    jayant467 Autumn 1999

    tzoompy594 Autumn 1999

    NULLNULL NULL

    GradStudents Table

    FName LName loginid office#

    BermanAndrewabermanC115

    CollinsMauriceacollins224

    LiuAgathaahliu223

    BernheimAliceajb431

    MichailAmiramir225

    AggarwalAmitamitC102

    GangulyAnandaanandaNULL

    DoanAnhaianhaiC106

    SabharwalAshishashish233

    ThaungAungaung233

    AuslanderJoelauslandNULL

    DavisAaronawd222

    MichalowskiBrianbamNULL

    NiswongerBartholomewbart233

    BlanchetteMathieublanchem427

    BonhamShawnbonham425

    ChamberlainBradfordbradC109

    CapellStevecapell233

    CardwellNealcardwell433

    CarlsonAdamcarlsonC109B

    CaryMatthewcaryC104

    GordonCharlescgordon431

    ChenJiamianchenjNULL

    ChenZhenguangchenzgNULL

    AndersonCorincorin425

    PrinceChristophercprince428

    KaplanCraigcsk224

    KwokChungctkwok429

    ChuangYung-Yucyy233

    WoodDanieldaniel428

    CronquistDarrendarrencC112

    AzumaDanieldazumaC110

    DionDavidddionNULL

    VermaDeepakdeepak428

    DeitzStevendeitz428

    PinnelLesliedenisep224

    WeathersbyWderrick428

    DeweyBriandeweyC109B

    FasuloDanieldfasuloC112

    LeeDennisdlee222

    LowDouglasdouglasC106

    ZongkerDouglasdougzC109C

    LewisEechrisC109C

    RockeEmilyecrocke427

    HongEdwinedhong233

    SirerEminegs429

    ElyDavidely428

    VeeErikenv429

    AndersonEriceric427

    EslerMichaelesler431

    FixJamesfixC109C

    FriedmanMarcfriedman429

    MiklauGeromegerome428

    HultenGeoffghultenC104

    BadrosGregorygjb224

    LindenGregglindenNULL

    JustinGoshigoshiC110

    GrantBriangrantC115

    BartelsGrettagrettaC110

    HartlineJasonhartline233

    HinesKennethhineskjC102

    HinshawKevinhinshaw222

    HsuDavidhsud233

    TatarinovIgorigorC112

    CockrellJakejakeC110

    MadhavanJayantjayant233

    BaerJeremyjbaer222

    BuhlerJeremyjbuhler429

    HightowerJeffreyjeffro431

    KangJongjhkang224

    AldrichJonathanjonalC109A

    SeimsJoshuajoshNULL

    PowerJoannajpower431

    KimJiwonjwkim222

    LiuChen-Yunkarenliu224

    YeungKa Yeekayee427

    KellerAndrewkeller233

    PartridgeKurtkepart223

    LeeKeunwooklee225

    LloydDaniellloyd431

    MadaniOmidmadaniC109B

    ThambiManumanu428

    PerkowitzMikemapC109

    GullicksonMariamaria425

    BerrymanMarkmarkb223

    BakerMarlamarlaNULL

    PhiliposeMatthaimatthaiC115

    RichardsonMatthewmattrC102

    ChesireMaureenmaureen223

    McSherryFrankmcsherry233

    FiuczynskiMarcmef223

    ErnstMichaelmernst222

    FigueroaMiguelmiguel222

    SwiftMichaelmikesw429

    ZimmermanMarcmkzim428

    MockMarkusmockC112

    BrownEileenmolly431

    NarasayyaViveknara225

    SharmaNitinnitin233

    NothMichaelnoth425

    NowitzJonathannowitz233

    SpringNeilnspring222

    OrtegaRossortega431

    PardyakPrzemyslawpardy433

    FranklinPaulpaul224

    CrowleyPatrickpcrowley222

    PighinFredericpighinC112

    Van VleetPeterpw428

    WangQingyueqingyue425

    PottingerRachelrap224

    AndersonRuthrea427

    RedstoneJoshuaredstoneC109A

    JakobovitsRexrex428

    GrimmRobertrgrimm429

    RuanYongshaoruan225

    SaiaJaredsaia429

    SchwarmSarahsarahsC112

    SinhaSaurabhsaurabh425

    SavageStefansavage433

    SandysSeansdsC110

    BergStefansgberg427

    ShadeJonathanshadeC110

    KogaShuichiskoga233

    SobtiSumeetsobti427

    ParekhSujaysparekhC109A

    SelbergErikspeed233

    ChoiSung-EunsungeunC109C

    SunLiangsunliang428

    SahasranamanViveksvivek225

    ParikhTapantapanC104

    NguyenThuthu425

    LimTiantian225

    TiwaryAshutoshtiwary433

    TjadenBriantjaden223

    JamesTimothytjames233

    LandrayTashanatklC110

    LauTessatlau425

    MillsteinToddtodd427

    RazmovValentinvalentin233

    LitvinovVasilyvass225

    SukharevVassilivassili225

    SazawalVibhavibha428

    VoelkerGeoffreyvoelkerC109

    WongWaynewaynew431

    ChanWoonwchanC112

    WernerDawnwerner233

    WilcoxCraigwilcoxC112

    PortnoyWillwill233

    WolfmanStevenwolf428

    WolmanAlastairwolmanC109

    SaitoYasushiyashushi428

    YasuharaKenyasuhara431

    LiYiyi428

    ZamirOrenzamirC106

    WeinbergZashazashaC115

    IvesZacharyzives224

    KunenIsaaczook433

    NULLNULLNULLNULL

  • January 24, 2008 at 6:36 pm

    #771562

    try this and look at the results:

    SELECT *

    FROM GRADSTUDENTS G

    FULL OUTER JOIN TA T ON G.LOGINID = T.LOGINID

    --WHERE G.LOGINID IS NULL

    --WHERE T.LOGINID IS NULL

    the first rows in the results will be GRADSTUDENTS columns; if any of the first rows are NULL, then there are TEACHERSASSISTANTS that are not GRADSTUDENTS

    if there are rows farther to teh right, where the TA data exists, then those are GRADSTUDENTS who are not TEACHERSASSISTANTS

    all rows that have data in both tables are obviously the people who are both.

    by doing a WHERE G.LOGINID IS NULL (for TA's who are not GRADS) or WHERE T.LOGINID IS NULL, you should be able to see in the data the items where GRADS are not TA's.

    once you are confident of your results, you can start adding more items to your WHERE clause

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 24, 2008 at 6:56 pm

    #771563

    How about:

    Select distinct g.loginid, g.fname, g.lname

    From gradstudents g

    where g.loginid NOT IN (select t.loginid from ta t)

    Based on the data you provided, I think you're excluding all the class numbers, so it looks to me like you'd never get any results the way you had it structured.

    And then again, I might be wrong ...
    David Webb

  • January 24, 2008 at 6:57 pm

    #771564

    And on a related note, I used to work with someone named Vince Caputo. Ever been to Tacoma?

    And then again, I might be wrong ...
    David Webb

  • January 24, 2008 at 9:52 pm

    #771581

    Hi,

    Try this one.

    SELECT DISTINCT g.loginid,g.LName,g.FName,g.office#

    FROM gradstudents g

    INNER JOIN ta t

    ON g.loginid = t.loginid

    WHERE classnumber NOT IN (SELECT DISTINCT classnumber FROM ta)

    -----

  • January 25, 2008 at 6:20 am

    #771707

    Thank you so much, I am not the Vince Caputo you know, I've never been to Omaha, don't know why anyone would want to go there, LOL!!!!!

    But thanks again for the help, I was trying everything but couldn't get the proper answer.

    I'm surprised that there was not a need for a join to get the answer, too.

    Vinni

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply