Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts

    In one table but NOT in the other ?

    Hi,

    I have two tables: PLATFORMS and MAIN

    I want to select the platform id numbers that exist in PLATFORMS
    but that have not yet been used in MAIN


    In PLATFORMS the platform id number field is platform_id

    and in MAIN, if the platform has been used, its id is in platform_no

    This gives me ALL the platform names and ids:

    PHP Code:
    $sql_plat "SELECT platform_id,plat_name FROM PLATFORMS ";
    $result_plat mysql_query($sql_plat) or die("could not find platforms"mysql_error()); 
    but how do I exclude all those already used in MAIN ???


    Thanks for advice.



    .
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Code:
    SELECT p.platform_id, p.plat_name
    FROM PLATFORMS p
    LEFT JOIN MAIN m ON m.platform_no = p.platform_id
    WHERE m.platform_no IS NULL
    Try that.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Thanks

    I tried these combinations:

    PHP Code:
    $sql_plat "SELECT platforms.platform_id, platforms.plat_name FROM platforms 
            LEFT JOIN main ON platforms.platform_id = main.platform_no WHERE main.platform_no IS NULL"

    and

    PHP Code:
    $sql_plat "SELECT platforms.platform_id, platforms.plat_name FROM platforms 
            LEFT JOIN main ON main.platform_no = platforms.platform_id WHERE main.platform_no IS NULL"

    But the result is the same ...

    I get a list of four of the platforms, the same list each time regardless of
    whether the platform is on MAIN or not

    Just to clarify ...

    The complete list is held by platforms, the partial "used" list is on main.

    I am trying to get the ones that are on platforms but not on main.


    .
    Last edited by jeddi; 05-22-2013 at 06:05 PM.
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Which is what that should do. I can't write an identical query, but I can do one off of a table I have. I use a set of keywords that are applied in many to many to servers, so if I want to find unused keywords than I write as:
    Code:
    SELECT k.Keyword
    FROM Keywords k
    LEFT JOIN ServerKeyword s ON k.Keyword = s.Keyword
    WHERE s.Keyword Is Null
    Which gives me a list results of:
    Code:
    NLB
    SERVER 2000
    EPICORE
    REVERSE PROXY
    Indicating that none of the above are in use by any associated server. I have a total of about 35 keywords in use by various machines, and checking on a WHERE for server 2000 as well as for EPICORE I get no results since I haven't tagged any machines with those.
    Granted this isn't on a MySQL machine, but that shouldn't be an issue with the is null check.
    If that's not working, you'll have to wait for pedant to come around and fix it up :P
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    FouLu's query should have worked.

    Try it in a MySQL query tool instead of PHP.

    But another way to write it is:
    Code:
    SELECT platform_id, plat_name FROM platforms 
    WHERE platform_id NOT IN ( 
         SELECT platform_no FROM main )
    Usually not as efficient as the version FouLu gave you, but it should work.

    If it doesn't, then you have a problem in your data.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Senior Coder
    Join Date
    May 2006
    Posts
    1,683
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Thanks for your help.

    OK - I SEE I HAVE FORGOTTEN TO INCLUDE THE PAGE_NO.

    SO I NEED TO ALSO EXCLUDE ROWS THAT HAVE PAGE_NO = $Db_page_no in the MAIN table.

    So I have added it below ...

    This is the code I am using in my script:

    PHP Code:
    <b><u>Assigning Page No: <?php echo $Db_page_no ?></u> -- <?php echo "$Db_page_name This_type:$this_type" ?> </b><br>
    <div class="art_title">
            <span>Select Platform:&nbsp;</span> 
            <select name="x_platform_no">
            <?php
            $sql_plat 
    "SELECT platform_id, plat_name FROM platforms, main 
            WHERE platform_id=platform_no AND page_no != '$Db_page_no' )"
    ;
            
            
    $result_plat mysql_query($sql_plat) or die("could not find platform"mysql_error());
            
    $num_plat mysql_num_rows($result_plat);

            if (
    $num_plat == ) {
                echo 
    "ERROR in locating Platform for Client: $this_client.";
                exit;
                }  
    // endif    
            
    else {
                while(
    $row_plat mysql_fetch_assoc($result_plat)) {
                    echo 
    "<option value='{$row_plat['platform_id']}'>{$row_plat['plat_name']}</option>\n";
                    } 
    // end while             
                
    // end else
            
    ?>
        </select>
        </div>
    And this is the data in the PLATFORMS table:



    And this is the data in the MAIN table:




    The result I get when trying to use page_id 3 is:



    As you see, I now get zero results:

    Can you see what I have done wrong ?

    My listing shows that Page 3 is allocated in platforms:

    no. 1 - Meaty Monster Bikes
    no. 3 - Hovercraft Kits
    no. 4 - Robot Kits

    So my expected result from the above query is:

    no. 2 - Big Boat Kits.

    ( because page 3 is not allocated to platform 2 in MAIN )

    But what I need to do is make the query ALSO output the
    rows that are not in MAIN but are in PLATFORMS

    So my final result SHOULD BE:

    no. 2 - Big Boat Kits.
    no. 5 - Sail Boat Kits.
    no. 6 - Jet Boat Plans
    no. 7 - Build Boat Kits
    no. 8 - Hovercraft DIY


    I hope you see what I am trying to do and how I can do it

    Thanks.


    .
    Last edited by jeddi; 05-23-2013 at 08:41 AM.
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    Like this?
    Code:
    SELECT platform_id, plat_name FROM platforms 
    WHERE platform_id NOT IN ( 
         SELECT platform_no FROM main WHERE page_id = 3)
    Which could also be done starting from FouLu's code as:
    Code:
    SELECT p.platform_id, p.plat_name
    FROM PLATFORMS p
    LEFT JOIN MAIN m ON ( m.platform_no = p.platform_id AND M.page_id = 3 )
    WHERE m.platform_no IS NULL
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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