www.webdeveloper.com
Results 1 to 5 of 5

Thread: Selecting from multiple tables without conditions

  1. #1
    Join Date
    Nov 2010
    Posts
    49

    Selecting from multiple tables without conditions

    Hey guys,

    I'm trying to select and print data from two different tables.
    The reason the data is in different tables is for use in other parts of my site (makes life way easier). But on one particular page I just want to get the data from both tables and print it all out.
    Once I can do that I want to order it by a date column (so it doesn't matter which table it's from the newer one will come first).

    I have tried something like:
    Code:
    "SELECT id.table1, id.table2 FROM table1, table2"
    but it seems to print out repetitions randomly :S

    I am calling it all through php, I normally use something like
    Code:
    while($row = mysql_fetch_array($sql_query))
    {
        echo "the data from the row 'id' is: " . $row['id'];
    }
    Any way I can do this? The simpler the better as I really like to understand what I'm doing

    Thanks guys!,

    lilfellabob

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Code:
    "SELECT id.table1, id.table2 FROM table1, table2"
    This is looking for columns 'table1' and 'table2' in an aliased table (id) that hasn't been defined.
    Code:
    "SELECT table1.id, table2.id FROM table1, table2"
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

  3. #3
    Join Date
    Nov 2010
    Posts
    49
    thanks for the reply.
    It fixed the error but it still loops through some row's many many times (no idea why..)

    :S

  4. #4
    Join Date
    Jan 2005
    Location
    Los Angeles, CA
    Posts
    4,887
    Try:
    PHP Code:
    $sql="SELECT table1.id AS `id`,'table1' AS `table` FROM table1 UNION ALL SELECT table2.id,'table2' AS `table` FROM table2";
    $query=mysql_query($sql) or die(mysql_error());
    $results=array();
    while(
    $row=mysql_fetch_array($query)){
        
    $results[]=array('id'=>$row['id'],'table'=>$row['table']);
    }
    echo 
    '<pre>'.htmlentities(print_r($results,true)).'</pre>'
    You might also be interested in something complex to simulate a horizontal or column concentration:
    Code:
    SET @cnt1 := 0,@cnt2 := 0,@cnt3 := 0,@cnt4 := 0;
    SELECT t1.id AS `id1`,t2.id AS `id2`FROM (SELECT *,++@cnt1 := @cnt1 + 1 AS `` FROM table1) AS t1 LEFT JOIN (SELECT *,@cnt2 := @cnt2 + 1 AS `` FROM table2) AS t2 ON t1.``=t2.`` UNION
    SELECT t1.id AS `id1`,t2.id AS `id2`FROM (SELECT *,++@cnt3 := @cnt3 + 1 AS `` FROM table1) AS t1 RIGHT JOIN (SELECT *,@cnt4 := @cnt4 + 1 AS `` FROM table2) AS t2 ON t1.``=t2.``
    But it would make more sense to just preform a second query in most cases.

    Depending on the data in your tables, if the two tables have a field in common such as an id, you would probably be better off doing:
    PHP Code:
    $sql="SELECT * FROM `table1` t1 LEFT JOIN `table2` t2 ON t1.id=t2.id"
    Or if the two tables have different amount of rows, like in most cases, you can be safe using:
    PHP Code:
    $sql="SELECT * FROM `table1` t1 LEFT JOIN `table2` t2 ON t1.id=t2.id UNION SELECT * FROM `table1` t1 RIGHT JOIN `table2` t2 ON t1.id=t2.id"
    But that is usually unnecessary if the first table is the more important one. If not, simply switch the table1 and table2 references or swap the LEFT JOIN for a RIGHT JOIN.
    Last edited by Ultimater; 05-10-2011 at 05:40 AM.

  5. #5
    Join Date
    Nov 2010
    Posts
    49
    Hmm yeah I thinking that first lot is what I'd use. But I decided that I'd be better off putting it in one table then adding a field to split it up later.

    Thanks for the help guys, really appreciate it!

    lilfellabob

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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