Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    laredo
    Posts
    303

    MySql relation in data

    Hello,

    How can I display records that have the same data. Similar to finding duplicates, but not exactly. EXAMPLE:

    NAME | COURSE |
    ----------------------
    JOE | COURSE1 |
    JOE | COURSE2 |
    MATT | COURSE1 |
    MATT | COURSE2 |
    MATT | COURSE3 |
    JUAN | COURSE1 |
    JUAN | COURSE2 |
    JUAN | COURSE4 |

    The pattern here is that students that take COURSE1 will likely take COURSE2.

    Thank you.

  2. #2
    Join Date
    Jun 2004
    Location
    San Diego, CA
    Posts
    136
    Well, this works but I'm not sure if it is what you want:

    select course, count(course) as courses, (select count(distinct name) from table) as students from table group by course having courses = students;

    This grabs the courses that are common amongst all students in the table. You could probably wrap that within another query to find all courses common amongst students in a particular set.
    Matt Bloom
    AngryHosting - Load balanced/redundant shared hosting solutions

  3. #3
    Join Date
    Jul 2003
    Location
    laredo
    Posts
    303
    Thanks Optikalus,

    I tried to implement your example in my code but I might have placed something wrongly. I get Results = 0.

    Can you take a look at it an tell me what you see wrong.

    The "ClassSection_id" is the name of the column for the courses.
    The "Campus_id" is the name of the column for the student id.
    The name of the table is "students".

    Thank you.

    <?php
    echo "<h2>Most common combinations:</h2>";

    // the connections script

    $query = "select ClassSection_id, count(ClassSection_id) as courses, (select count(distinct Campus_id) from students) as students from students group by ClassSection_id having courses = students";

    $result = mysql_query($query) or die(mysql_error());
    // Print out result

    $i=0;
    while($row = mysql_fetch_array($result)){
    $i++;

    $ClassSection_id = $row['ClassSection_id'];
    $Campus_id = $row['Campus_id'];

    echo $Campus_id;
    }

    echo "Results = " . $i;

    // closing connection script
    ?>
    Last edited by raulgonzalez; 04-15-2008 at 02:58 PM.

  4. #4
    Join Date
    Jun 2004
    Location
    San Diego, CA
    Posts
    136
    The problem with the query is that it doesn't work unless all students in the set share at least one course, otherwise #courses will not equal #students.

    This is a pretty tricky question which may be best done outside of MySQL.

    I'll give it another try and see if I can't come up with anything.
    Matt Bloom
    AngryHosting - Load balanced/redundant shared hosting solutions

  5. #5
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    665
    This is a DTI problem, I don't believe that you could efficiently (or at all) do this with a single SQL query.

    You'll need to construct a classifier, pull all your rows, and pit them against your classifier constructs.

    Here are some notes on DTI:
    http://www.cse.unsw.edu.au/~billw/cs...p/id3/id3.html

    This type of deductive classifier can using entropy, discover patterns in categorical data.

    I'm assuming that you want to draw relationships between students, and their course subscription patterns?

    All the best.
    Alex
    circlical - hosting software development
    forums * blog

  6. #6
    Join Date
    Jul 2003
    Location
    laredo
    Posts
    303
    seaven,

    Yes, I asked a previous instructor of mine and he told me that I should search for something like "Market - basket" analysis.

    What if I try to pull out those records that appear more than once using the HAVING clouse first and then take it from there. Would that be an approach?

  7. #7
    can you give me more info on this?because im not good in mysql. thanks

Posting Permissions

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