SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    instead of intersect?

    ok, i'm stumped...
    i have two queries that i want to intersect, but since intersect isn't supported and my host runs mysql 4.0.21 (so i can't use nested queries) i don't really know how to work around it...

    i have two queries stored in variables, like:

    PHP Code:
      $sql_know "select distinct student_id from knowledge_specification where area_id='$area_id'";
        
        
    $sql_edu "select distinct student_id from education where kind='$kind' and name_id='$program_id'"
    no i want to intersect them, like this:

    PHP Code:
    $sql $sql_edu " INTERSECT " $sql_know
    but how? any help greatly appriciated...
    /Måns

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    why is DISTINCT used in each query? are students not unique in each table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, the tables consist of different educations, each education has a unique id but each student can have multiple educations. simplified:
    id|kind|...blablabla...|name_id|student_id (where student_id is foreign key to a table with students)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    this might not work --
    Code:
    (
    select 'K' as source
         , student_id 
      from knowledge_specification 
     where area_id = $area_id
    )
    union all
    ( 
    select 'E' as source
         , student_id 
      from education 
     where kind = $kind 
       and name_id = $program_id 
    )
    group
        by student_id
    having count(*) > 1
    but this definitely should work --
    Code:
    select distinct
           K.student_id 
      from knowledge_specification as K
    inner
      join education as E
        on K.student_id
         = E.student_id
     where K.area_id = $area_id
       and E.kind = $kind 
       and E.name_id = $program_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mkay, thanks, i'll try that right away. just another question that came up with join: sometimes i have three queries (like those above), how do i join them?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well, since you don't have 4.1 and can't use subqueries, you cannot actually join queries, although you can join tables

    just keep going in the same pattern --
    Code:
    select distinct
           K.student_id 
      from knowledge_specification as K
    inner
      join education as E
        on K.student_id
         = E.student_id
    inner
      join foo as F
        on K.student_id
         = F.student_id 
     where K.area_id = $area_id
       and E.kind = $kind 
       and E.name_id = $program_id 
       and F.bar = $bar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe, damn, it's a shame intersect isn't available... oh, well, i'll try to work it out with join and let you know how it goes, will probably take some time though

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    take some time? i gave you a query that will work, what else do you have to do in order to test it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, sorry, didn't mean to sound ungrateful, just that i've got this ugly if-statement where i build up a search-query and thought that it would take some time to implement join in that. didn't want to bother you with the details when i started the thread...

    PHP Code:
     if ($search_edu=='yes' and $search_know=='yes' and $search_bus=='yes') {
     
    $sql $sql_edu " INTERSECT " $sql_know " INTERSECT " $sql_bus;
     } elseif (
    $search_edu=='yes' and $search_know=='yes' and $search_bus=='no') {
     
    $sql $sql_edu " INTERSECT " $sql_know;
     } elseif (
    $search_edu=='yes' and $search_know=='no' and $search_bus=='no') {
     
    $sql $sql_edu;
     } elseif (
    $search_edu=='yes' and $search_know=='no' and $search_bus=='yes') {
     
    $sql $sql_edu " INTERSECT " $sql_bus;
     } elseif (
    $search_edu=='no' and $search_know=='yes' and $search_bus=='yes') {
     
    $sql $sql_know " INTERSECT " $sql_bus;
     } elseif (
    $search_edu=='no' and $search_know=='no' and $search_bus=='yes') {
     
    $sql $sql_bus;
     } elseif (
    $search_edu=='no' and $search_know=='yes' and $search_bus=='no') {
     
    $sql $sql_know;
     } else {
     
    $sql "";
     } 

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yoiks, what a mess, there's gotta be easier ways to query three tables

    are you really going to execute an empty string if all three options are "no"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, i know, it's not pretty... but the user can build three different queries on three different tables, and then there's checkboxes where he/she selects which query to perform. couldn't come up with a better solution...

    and yes, at the time i execute an empty string and echo a pretty error message

  12. #12
    SitePoint Enthusiast HARS's Avatar
    Join Date
    Jun 2001
    Location
    Stockholm, Sweden
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    group by didn't work but i got it working with join, many thanks for your help!

    Code:
    $sql = $sql_edu . " INNER JOIN knowledge_specification on education.student_id = knowledge_specification.student_id WHERE " . $sql_know_where . " AND " . $sql_edu_where;


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
  •