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
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts

    joining multiple tables

    how would i go about joining more then 1 table that have nothing in common? I am wanting to get a total count from several different tables then sort the results by month and amount per month

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The whole point of a join is to bring together data with something in common. Without that common something, a join is not the right tool for the job.

    You probably want to use a UNION instead.

  • #3
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I tried UNION and UNION all and it does put out the data from all 3 tables but does not bring them together as a total count or separate the result by amount per month

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You can get amount by month with "GROUP BY month". You may have to run a separate query to get a count of multiple tables:

    Code:
    SELECT (SELECT COUNT(*) FROM T_TABLE1)
    + (SELECT COUNT(*) FROM T_TABLE2)
    + (SELECT COUNT(*) FROM T_TABLE3)

  • #5
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    heres what i am trying to accomplish:

    http://classify.kellywebserv.com/3tables.php

    Code:
    <?php
    
    include ('.global.php');
    
    
    ?>                 
    
                     <?php
                     
    $result7 = mysql_query("SELECT ad_id FROM ads
     UNION ALL 
     SELECT ad_id FROM ads_announce 
     UNION ALL 
     SELECT ad_id FROM ads_jobs") or die("Sql error : " . mysql_error());
    
    $num_rows4 = mysql_num_rows($result7);
    
    $result2 = mysql_query("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads
    UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_announce
    UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_jobs group by month(ad_date)") or die("Sql error : " . mysql_error());
                    $data1 = array();
                     $approved = array();
                     $i = 0;
    while ($row = mysql_fetch_array($result2)) {
    $data1[$i] = (int) $row['num1'];
    $approved[$i] = (int) $row['approved'];
              $cats1[$i] = $row['admonth'];
              $i++;
                             
    }
    
    $result2a = mysql_query("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved1 from ads where approved='1'
    UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved1 from ads_announce where approved='1'
    UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved1 from ads_jobs where approved='1' group by month(ad_date)") or die("Sql error : " . mysql_error());
    
                     $approved1 = array();
                     $i = 0;
    while ($row = mysql_fetch_array($result2a)) {
    $approved1[$i] = (int) $row['approved1'];
              $cats1[$i] = $row['admonth'];
              $i++;
                             
    }
    
                     $query3 = ("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved from ads where approved='0'
    UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved  from ads_announce where approved='0'
    UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved from ads_jobs where approved='0' group by month(ad_date)") or die("Sql error : " . mysql_error());
    
    
                                      $result3 = mysql_query($query3);
                     $unapproved = array();
                     $i = 0;
    while ($row = mysql_fetch_array($result3)) {
    $unapproved[$i] = (int) $row['unapproved'];
              $i++;
                             
    }
    
    
    $data1 = json_encode($data1);
    $cats1 = json_encode($cats1);
    $approved = json_encode($approved);
    $approved1 = json_encode($approved1);
    $unapproved = json_encode($unapproved);
    
                     ?>
    
                                      <?php
    echo "Total number of ads=" .$data1. "Should equal the TOTAL Ads in the system which is " .$num_rows4. "<br><br>";
    echo "Months=" .$cats1. " Should combine the months which in this case should be April and May<br><br>";
    echo "Total number of ads approved=" .$approved1. " Should combine the total number of ads in all 3 tables that are approved and seperated by month.<br><br>";
    echo "Total number of ads unapproved=" .$unapproved. " Should combine the total number of ads in all 3 tables that are not approved and seperated by month.<br><br>";
    
    
    /* from this point down has been hand counted and not generated from the code*/
    
    echo 'The actual numbers are as follows:<br>
    <table border="1">
    <tr>
    <th>Table</th><th>Total Ads</th><th>Month(s)</th><th>Approved</th><th>Not Approved</th></tr>
    <tr>
    <td>ads</td><td>29</td><td>April, May</td><td>4</td><td>25</td></tr>
    
    <tr><td>ads_announce</td><td>11</td><td>April</td><td>4</td><td>7</td></tr>
    
    <tr><td>ads_jobs</td><td>21</td><td>May</td><td>1</td><td>20</td></tr>
    </table>';
    
    echo 'Final output should read:<br>
    Total Number of ads=61<br>
    Months = April, May<br>
    Total number of ads approved=[7,2] (April=7, May=2)<br>
    Total number of ads unapproved=[20,32] (April=20, May=32)';
    ?>

  • #6
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    so from what i`m gathering is theres no way to join several different tables unless theres a common field?

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Oh there's a way, it's called a Cartesian join. But there is typically no point in doing so and it usually doesn't give the results you're after. It's easy to do; just join two tables with no qualifier on the join.

  • Users who have thanked Fumigator for this post:

    Mike1963 (05-30-2011)


  •  

    Posting Permissions

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