SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL special query

    Here is a simple example of the set up:

    Table1 (table name)
    ID (field name in Table1)
    name (field name in Table1)

    Table2 (second table name)
    name (field name in Table2)
    answer (field name in Table2)

    content of 2 tables:

    Table1
    ID: 1 name: Bob
    ID: 2 name: Sally
    ID: 3 name: Jane

    Table2
    name: Bob answer: yes
    name: Sally answer: no

    Here is the needed query:
    I need to get all info on the people in Table1 based on the following...
    WHERE Table1.name=Table2.name AND Table2.answer='no'...
    that part is easy but now here is the part I can't seem to figure out...
    OR name does not exist in Table2 yet.

    I have it set up to create an entry in Table2 when updating Table1, but I need to be able to access the records from Table1 (as described above) in a list BEFORE that update and creation is performed.

    I do not want any records from Table1 that have a matching record in Table2 where answer='yes'.

    Anyone got any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select t1.ID
         , t1.name 
      from Table1 as t1
    inner
      join Table2 as t2
        on t1.name = t2.name
       and t2.answer= 'no'
    union
    select t1.ID
         , t1.name 
      from Table1 as t1
    left outer
      join Table2 as t2
        on t1.name = t2.name
       and t2.name is null
    Last edited by r937; Feb 25, 2005 at 21:31.
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the effort, but it does the same thing all the other ways I've tried. It prints out all records, and even a duplicate of the first record, and for some reason doesn't give a value for the name on every record except the first. The name returns on the first record, but not on any other record after that. All other field values for each record return fine.

    Is there a way to select everything BUT one criteria?
    IE: can I query: SELECT all entries from Table1 that DO NOT have the following..
    WHERE Table2.answer='yes'

    I've tried writing that several times with no luck.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by window2
    Is there a way to select everything BUT one criteria?
    IE: can I query: SELECT all entries from Table1 that DO NOT have the following..
    WHERE Table2.answer='yes'
    Code:
    select t1.ID
         , t1.name 
      from Table1 as t1
    inner
      join Table2 as t2
        on t1.name = t2.name
       and t2.answer = 'yes'
     where t2.name is null
    i realize now i had a typo in my pervious answer

    i've edited it, and made the change in red
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No results at all now.
    Looking at what you wrote...

    and t2.answer = 'yes'
    where t2.name is null
    If t2.answer = 'yes', then that means there is an entry in t2...which means that the name can't be NULL.
    And..
    If t2.name is NULL, then that means there is no entry in t2

    ..as for the typo in the first one, I already noticed that and changed it in my test.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by window2
    If t2.answer = 'yes', then that means there is an entry in t2...which means that the name can't be NULL.
    And..
    If t2.name is NULL, then that means there is no entry in t2
    nope, i am certain this time i have it right

    t2.answer='yes' is part of the JOIN condition, as it's in the ON clause

    that's what the join is attempting to find -- t2 rows with the same name where the answer is 'yes'

    if it can't find one, then t2.name will be null

    that's how left outer joins work

    based on all the problems you've encountered, i now suspect bad data

    i would now ask you to provide a few sample rows of each table that illustrate the various conditions (with answer='yes', with answer='no', without either) for a few names, so that i can test the queries on the same data as you
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use exactly what I gave as the example in the beginning...
    fields are ID, name, in table1
    fields are name, answer in table2
    values/records as mentioned..

    table1
    ID: 1 name: Bob
    ID: 2 name: Sally
    ID: 3 name: Jane

    table2
    name: Bob answer: yes
    name: Sally answer: no
    The results are as follows using your script..
    2, Sally, no

    1, Bob, yes

    3, Jane,
    But the second record should not be showing.
    This is a 'yes' record and I only want 'no' records, or records with no table2 counterpart.

  8. #8
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a sample of my example page...
    Code:
    <?php 
    
    
    ?>
    
    <b>Search Results:</b><br /><br /><br />
    <?php
    require_once ('database connection file here');
    
    $query = "select t1.ID, t1.name from Table1 as t1 inner join Table2 as t2 on t1.name = t2.name and t2.answer= 'no' union select t1.ID , t1.name from Table1 as t1 left outer join Table2 as t2 on t1.name = t2.name and t2.name is null";
    
    ////////////////// RESULTS
    
    echo '<table border="0" width="750" cellspacing="0" cellpadding="5">
    </tr>';
    
    
    $result = mysql_query ($query);
    $num = mysql_num_rows ($result); 
    
    if ($num > 0) { 
    
    
    while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
    
    $query2 = "SELECT * FROM table2 WHERE name = '{$row['name']}'";
    $result2 = mysql_query ($query2);
    $row2 = mysql_fetch_array ($result2, MYSQL_BOTH);
    	
    
    	echo "<td>{$row['ID']}, {$row['name']}, {$row2['answer']}";
    
    echo "</b></a></td>
    	</tr>\n";
    }
    	mysql_free_result ($result); 	
    
    } else { 
    
    	echo '<tr>
    		<td align="center"><p><font color="red" size="+1">There is no match for your entry.<br /><br /><a href="javascript:window.history.go(-1);">Click HERE to return to Search page.</a></font></p></td>
    	</tr>';
    }
    
    echo '</table>';
    mysql_close();
     ?>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your php does me no good, i don't do php

    however, i do make mistakes, and often in bunches

    i said "that's how left outer joins work" but i wrote INNER (in post #4)

    doh!!

    i have tested this --
    Code:
    select t1.ID
         , t1.name 
      from window2_1 as t1
    left outer
      join window2_2 as t2
        on t1.name = t2.name
       and t2.answer = 'yes'
     where t2.name is null
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    YAHOO!!

    I do believe we have lift off!
    Thank you , thank you, thank you.

    I have been trying joins and unions all day with no luck. I don't know what I was doing wrong before, don't care, it works now.
    Sleep Now.


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
  •