SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi can anyone help.

    I am trying to do a Join on an sql statement bou can not get it to work.

    The tables i have are

    Table#1
    FieldA
    FieldB

    Table#2
    FieldA
    FieldB
    FieldC

    What I want is a query that will bring back all rows in table A where FieldB (in TableA) is not in TableB.

    Can anyone help ?.

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Select * From Table_1, Table_2 where Table_1.fieldb <> Table_2.fieldb


    I think that should do what you are looking for.
    Marty H.

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Marty.

    Thank's but that does not fix it !!.

    In this scenario Table#1 has 87 records and Table#2 has 24 records.

    I tried the query and it returned over 2000 rows !!.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah yes, just one thing missing from Marty H's SQL.

    SELECT *
    FROM Table1, Table2
    WHERE Table1.FieldA = Table2.FieldA
    AND Table1.FieldB <> Table2.FieldB

    You need to filter the result set which is what the first part of the WHERE clause does above. I'm assuming here that you want to match those records where FieldA is the same in both tables. You need to do this because what the FROM statement (a.k.a inner join) in the SQL does is create a set which is the cartesian product of Table1 X Table2. As an example, here is something I've cut and paste from a post I made in this forums a couple of months ago.
    Code:
    Table X 
    ------- 
    X1 
    X2 
    X3 
     
    and  
     
    Table Y 
    ------- 
    Y1 
    Y2 
    Y3 
     
    and we join these two tables we get 
     
    Table X . Table Y 
    --------------- 
    X1   Y1 
    X1   Y2 
    X1   Y3 
    X2   Y1 
    X2   Y2 
    X2   Y3 
    X3   Y1 
    X3   Y2 
    X3   Y3
    Last edited by freakysid; May 8, 2001 at 04:09.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freakysid - sorry that does not work.

    FieldA in Table#1 and Table#2 are not the same format therefore can not be compared.

    To clarify here is an example

    Table#1 : FieldB has the fololwing values

    Apple
    Orange
    Peach
    Banana
    Mango

    Table#2 : FieldB has the following values

    Apple
    Grape
    Orange
    Papaya
    Banana
    Mango

    What I want is a query that will return "Peach" from Table#1 as "Peach" does not exist in Table#2

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good news and bad news. Good news is that in SQL you would write this as:

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good news and bad news. Good news is that in SQL you would write this as:
    Code:
    SELECT *
    FROM Table1
    WHERE FieldB NOT IN
       (SELECT FieldB FROM Table2)
    The bad news is the MySQL does not support subqueries. So you might have to do this in your PHP. I will post again if I can come up with a fix.

    [added]

    Well, this is internesting. I cannot think of how you would achieve a work around using a join because as you say, there is nothing to join on. So the only thing I can think of is to produce some very hackish code in PHP (off the top of my head so very untested):
    PHP Code:
    // query Table2 to create a list of the values of of fieldB.
    $sql "SELECT fieldB FROM Table2";
    $result mysql_query($sql);
    while ( 
    $row mysql_fetch_row($result) {
       
    $filter[] = $row[0];
    }

    // create an SQL string that will include a clause to filter out
    // all the values in array $filter
    $sql "SELECT * FROM Table1";

    $filterSize sizeof($filter);
    if ( 
    $filterSize ) {
       
    $sql .= " WHERE fieldB != $filter[0]";
    }
    if (
    $filterSize ) {
       for( 
    $i 1$i $filterSize$i++) {
          
    $sql .= " AND fieldB != $filter[$i]";
       }
    }

    // execute the query
    $result mysql_query($sql); 
    Wow! That's a lot of code to hack what could be done in one simple SQL with a subquery. Someone may be able to think up a more elegant solution.
    Last edited by freakysid; May 8, 2001 at 06:17.

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont know if this is more elegant but it should work

    PHP Code:
    $sql "Select fieldb from Table2";
    $result mysql_query($sql);

    $i=1;

    while ( 
    $row=mysql_fetch_array($result) ) {
        
    $row[$i] = $row[fieldb];
        
    $i++;
    }

    $where "fieldb != $row[1]";

    $i=2;

    while ( isset(
    $row[$i] ) {

        
    $where .= " and fieldb != $row[$i]";
        
    $i++;
    }

    $sql "Select * from Table1 where $where";
    $result mysql_query($sql);

    while ( 
    $row mysql_fetch_array($result) ) {

        echo 
    "$row[fieldb]";

    Using the above example, it should echo "peach" to the browser.
    Last edited by Marty H.; May 8, 2001 at 10:52.
    Marty H.


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
  •