Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Junior Member
    Join Date
    Feb 2005
    Posts
    5
    Member #
    9143
    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.  

  3. #2
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Quote Originally Posted by window2
    ...OR name does not exist in Table2 yet.
    ????

    I'm not sure what you mean.

    If your table presentation is an accurate reproduction of your actual tables, then you don't need to involve Table1 at all.

    For your 'no' extraction, you could write a simple "SELECT * FROM Table2 where answer='no'" (assuming the table are related with regard to 'name').

    This is probably not what you want to achieve (as that would be blindingly simple).

    Soooo...

    I suggest you write a short example of what you have in the two tables, how you would like to combine them, for what purpose.

    Right now, it's fairly cryptic.
    S. Rosland

  4. #3
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Is this what you're trying to do?

    pseudo code:
    select all names from table1 that has equals in (table2 where answer is 'no') or name does not exist in table2.

    In MySQL 4.1.x you could write that as a sub-query:

    SELECT name FROM Table1 WHERE name NOT IN (SELECT name FROM Table2 where answer='no')
    S. Rosland

  5. #4
    Junior Member
    Join Date
    Feb 2005
    Posts
    5
    Member #
    9143
    Rosland,

    Close on the second one...

    I want the records from the first table that
    EITHER...
    do not have a record in the second table YET,
    OR have a record but it's Table2.answer value is 'no'

    Your second answer is close? but missing some key things.
    I need to first look to see if there is an entry in table2 ...
    that is where the table1.name=table2.name comes in
    If there is no matching entry for table1.name, select that record...
    If there is, select it only if the table2.answer value is 'no'.

  6. #5
    Junior Member
    Join Date
    Feb 2005
    Posts
    5
    Member #
    9143
    Third party had answer...

    Code:
    select t1.ID
         , t1.name 
      from table1 as t1
    left outer
      join table2 as t2
        on t1.name = t2.name
       and t2.answer = 'yes'
     where t2.name is null

  7. #6
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    I see now I missed that.

    Nice solution!

    The apropriate sub-query accomplishing the same would be:
    Code:
    SELECT name FROM Table1 WHERE name NOT IN (
    SELECT t1.name FROM table1 as t1 inner join table2 as t2 where t1.name=t2.name and t2.answer='yes' 
    )
    S. Rosland


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:15 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com