Professional Web Applications Themes

Return rows present in one table but not present in another - MySQL

hello, I'm kinda new at mysql and I'm trying to learn some more complex statements to help optimize some of my code. I work for a small private school and I'm currently developing their database systems. I have two tables one with student info and one with student schedules. They both contain a StudentID field that links them. My question is if there is some kind of single select statement I could use to pull all the Student ID's that are NOT in the schedules table rather then pull all the results from both tables and writing code to search ...

  1. #1

    Default Return rows present in one table but not present in another

    hello, I'm kinda new at mysql and I'm trying to learn some more
    complex statements to help optimize some of my code.

    I work for a small private school and I'm currently developing their
    database systems. I have two tables one with student info and one with
    student schedules. They both contain a StudentID field that links
    them. My question is if there is some kind of single select statement
    I could use to pull all the Student ID's that are NOT in the schedules
    table rather then pull all the results from both tables and writing
    code to search for which id's are not present?

    j-Sun Guest

  2. #2

    Default Re: Return rows present in one table but not present in another

    On Jul 12, 6:25 pm, j-Sun <org> wrote: 

    Two ways:

    select StudentID
    from students
    where StudentID not in (select StudentID
    from schedules)

    Alternatively:

    select st.StudentID
    from students st
    left outer join schedules sc
    on st.StudentID = sc.StudentID
    where sc.StudentID is null

    ZeldorBlat Guest

  3. #3

    Default Re: Return rows present in one table but not present in another

    On 12 Jul, 23:37, ZeldorBlat <com> wrote: 

    >
    > Two ways:
    >
    > select StudentID
    > from students
    > where StudentID not in (select StudentID
    > from schedules)
    >
    > Alternatively:
    >
    > select st.StudentID
    > from students st
    > left outer join schedules sc
    > on st.StudentID = sc.StudentID
    > where sc.StudentID is null[/ref]

    I'd go for the:

    select st.StudentID
    from students st
    left outer join schedules sc
    on st.StudentID = sc.StudentID
    where sc.StudentID is null


    In MySQL it is way more efficient (providing the table is correctly
    indexed)

    Captain Guest

  4. #4

    Default Re: Return rows present in one table but not present in another

    On Jul 13, 4:13 am, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]




    >
    > I'd go for the:
    >
    > select st.StudentID
    > from students st
    > left outer join schedules sc
    > on st.StudentID = sc.StudentID
    > where sc.StudentID is null
    >
    > In MySQL it is way more efficient (providing the table is correctly
    > indexed)[/ref]

    Hey thanks a bunch, that worked out really well

    j-Sun Guest

Similar Threads

  1. Portability of sinl() etc - with -lm present
    By Ilya in forum PERL Modules
    Replies: 50
    Last Post: June 25th, 03:43 AM
  2. mysql with php 4.3.4 - function not present??
    By Vorxion in forum PHP Development
    Replies: 2
    Last Post: December 13th, 08:26 AM
  3. skipping files that are not present
    By John in forum PERL Modules
    Replies: 6
    Last Post: December 3rd, 02:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139