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 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Only where a record exists

    Is it possible to create a query where the results only match the records in another database?

    Here is my example:

    I have 2 tables:
    1. Partners
    2. Dispatches


    Sometimes there are spurious records in the dispatches database as new records are imported but it is important that I only pull the dispatches for my contracted partners (for a dashboard and trending).

    So, I need to pull records from the 'dispatches' table ONLY where it is a valid partner in the partner table with doing multiple select clauses.

    Any help would be appreciated...

    Thanks in advance

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you would do an INNER JOIN on the tables. you'd have to determine your columns to join on since you've not provided your table layouts.

  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I'm so close here...

    any clue what is wrong with my code?

    Code:
    include '../config.php'; //connect to the database
     
    
    
    $sql = "SELECT partner_name FROM partners";//contains only the contracted partners
    
    $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
    $partners = mysql_fetch_array($result);//assign to an array
    
    $sql = "SELECT * FROM dispatches WHERE Dispatched_TPM IN('".implode("', '", $partners)."')"; //fetch only dispatches where partner is contracted
    
    $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
    while( $row = mysql_fetch_assoc( $result ) ) { 
       echo $row['Dispatched_TPM'];
       echo "<br>";
    }
    If I type out the $partners array instead of pulling from MySQl it works. When I add the SELECT clause to create the partners array all I get is the first partner... must be close.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Follow what guelphdad suggests with a simple join:
    Code:
    SELECT * FROM dispatches INNER JOIN partners ON partner_name = Dispatched_TPM

  • #5
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    How did I miss that post? So sorry... thanks for the advice... works great


  •  

    Posting Permissions

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