www.webdeveloper.com
Results 1 to 3 of 3

Thread: What's the most concise way in sql to select from 5 different tables..

  1. #1
    Join Date
    Jun 2011
    Location
    California
    Posts
    383

    Lightbulb What's the most concise way in sql to select from 5 different tables..

    I want some data from each.. but mainly the telephone numbers found in each of these tables..

    The ultimate goal is to create a type of phone book, so when someone searches in the phone number input box it returns all the phones numbers that match from those five different categories (tables).....

    any help would be greatly appreciated.

    Here's what i have now...

    Code:
    select 
    
    code,
    first,
    last,
    phoneday,
    phonenight,
    dfcode
    
    from event.dbo.patient_dg()
    
    select 
    
    id,
    doctor_id,
    phone,
    fax
    
    from event.dbo.doctor_alt_address
    
    select 
    
    CODE_,
    NAME_,
    PHONE,
    FAX
    
    from ARE.APS.RFDME
    
    select 
    
    CODE_,
    FIRST_,
    LAST_,
    PHONE,
    FAX
    
    from ARE.APS.DFDME
    
    Select *
    
    from pharmacy.dbo.chain
    
    Select *
    
    from crm.dbo.rf_contacts
    Last edited by Supplement; 12-10-2012 at 07:17 PM.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,145
    I might let the application code do some of the repetitive work, e.g. in PHP:
    PHP Code:

    <?php

    $tables 
    = array(
        array(
            
    'table'  => 'event.dbo.patient_dg',
            
    'fields' => array(
                
    'code'       => 'Code',
                
    'first'      => 'First Name',
                
    'last'       => 'Last Name',
                
    'phoneday'   => 'Daytime Phone',
                
    'phonenight' => 'Nighttime phone',
                
    'dfcode'     => 'DF Code'
            
    )
        ),
        array(
            
    // repeat for each DB table
        
    )
    );

    $pdo = new PDO($dsn$user$pass);

    foreach(
    $tables as $tbl) {
        
    $sql "SELECT ".implode(', 'array_keys($tbl['fields']))." FROM ".$tbl['table'];
        
    $stmt $pdo->query($sql);
        while((
    $row $stmt->fetch(PDO::FETCH_ASSOC)) != false) {
            echo 
    "<div class='phone_listing'>\n<ul>\n";
            foreach(
    $row as $key => $value) {
                echo 
    "<li><b>".$tbl['fields'][$key].":</b> $value</li>\n";
                echo 
    "</ul>\n";
            }
            echo 
    "</div>\n";
        }
    }
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jun 2011
    Location
    California
    Posts
    383

    Thanks Nogdog, I'm still working on it... i see what you've done there...

    Here's how I've done it:

    PHP Code:
    <?php
    $module 
    'Phone Book';
    $table event_table('patient_dg()');
    $alias = array(
        
    0=>array('code'=>'ID'),
        
    1=>array('first'=>'First'),
        
    2=>array('last'=>'Last'),
        
    3=>array('phoneday'=>'Phone'),
        
    4=>array('phonenight'=>'Phone_2')
        );
    $where_list = array();
    $search = array(3);
    $show = array(0,1,2,3,4);
    $order = array(1);
    $newlink matry::base_to('phone_book/new');
    $rowlink matry::base_to('phone_book/lookup');

    I'm only showing the phoneday input box which is what i wanted... but i still can't figure out how to get the other select (data) statements from the other tables to appear in my queries.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles