www.webdeveloper.com
Results 1 to 9 of 9

Thread: Help with mysql select data from two tables

  1. #1
    Join Date
    Mar 2009
    Posts
    46

    Help with mysql select data from two tables

    Hi, I need help selecting data from to mysql tables using php.

    table 1 and table 2 both contain columns with the same name, but not matching data. They also contain some unique fields to the specific table.

    I have tried using UNION, which works but not fully because I have to null some fields since the column numbers do not match.

    I am trying to setup a JOIN query, but cannot find a way to do so without getting the "ambiguous" field problem

    How can I combine the two tables and query data with the possibility of using a where and order clause?
    Last edited by yahooman123; 07-23-2010 at 04:38 PM.

  2. #2
    Join Date
    Mar 2004
    Posts
    369
    to get all the data from two different tables, you use a join. An INNER join only gets records where both tables have records that matchup in your join clause:
    Code:
    SELECT *
    FROM table1
    INNER JOIN table2
    ON table2.id=table1.id
    A LEFT or RIGHT join will also fetch other records that have no match.

    If both tables have field names that are the same but which contain different data, you need to use an alias. You can alias both tables and fields.

    Here's an example of table aliasing:
    Code:
    SELECT t1.*, t2.*
    FROM table1 AS t1
    INNER JOIN table2 AS t2
    ON t2.id=t1.id
    You don't even need the "as" keyword. Just enter a space after table name and then the alias. This is very helpful for shortening otherwise lengthy queries.
    Code:
    SELECT t1.*, t2.*
    FROM table1 t1
    INNER JOIN table2 t2
    ON t2.id=t1.id
    To solve your problem where the fields have the same name, you can also alias your fieldnames
    Code:
    SELECT t1.col_x AS t1_col_x, t2.col_x AS t2_col_x
    FROM table1 AS t1
    INNER JOIN table2 AS t2
    ON t2.id=t1.id
    Write multiplayer games with FlashMOG 0.3.1
    Try the MyPlan Salary Calculator

  3. #3
    Join Date
    Mar 2010
    Posts
    2,803
    If you post your table structure and a sample of desired query output it should be easier to find a solution for your specific need

  4. #4
    Join Date
    Mar 2009
    Posts
    46
    Quote Originally Posted by sneakyimp View Post
    Code:
    SELECT t1.col_x AS t1_col_x, t2.col_x AS t2_col_x
    FROM table1 AS t1
    INNER JOIN table2 AS t2
    ON t2.id=t1.id

    Doesn't stating "ON t2.id=t1.id" declare that the data from the id field is the same for both tables? In my situation I do not have a field in table 1 that resembles a row in table 2. All the rows are completely unique, including the ID numbers.

  5. #5
    Join Date
    Mar 2010
    Posts
    2,803
    There is a solution but unless you post your table structures and say 2-3 rows of sample data from the relevant columns in each table along with a sample of your required query output, I will be only guessing as to which solutuon to post.

  6. #6
    Join Date
    Mar 2004
    Posts
    369
    Quote Originally Posted by yahooman123 View Post
    Doesn't stating "ON t2.id=t1.id" declare that the data from the id field is the same for both tables? In my situation I do not have a field in table 1 that resembles a row in table 2. All the rows are completely unique, including the ID numbers.
    It does. I'm having a hard time picturing why you'd want to fetch two different tables at once if there was no column match between them. In that case, I'd probably run two queries.

    On the other hand, you could also use a UNION query with column aliasing:
    Code:
    SELECT col_x as my_col FROM table1
    UNION
    SELECT col_r as my_col FROM table2
    Write multiplayer games with FlashMOG 0.3.1
    Try the MyPlan Salary Calculator

  7. #7
    Join Date
    Jul 2010
    Posts
    4
    A join is specifically designed to combine two tables that have a relationship, ie: a column. Different types of joins give different results.

    A Union can combine two similar tables that are unrelated. The union requires that the two select statements select the same number of columns with the same datatypes and in the same order.

    If you post your table info (column names, data types, etc) and the end result you are going for and I may be able to advise.

  8. #8
    Join Date
    Mar 2009
    Posts
    46
    Table 1:

    id | color | brand | date | motor | tire |
    ===================================
    1 | varchar| varchar| datetime| varchar| varchar|
    2 | varchar| varchar| datetime| varchar| varchar|
    3 | varchar| varchar| datetime| varchar| varchar|

    Table 2:

    id | color | brand | date | material | finish | style | quality |
    ===================================
    8 | varchar| varchar| datetime| varchar| varchar| varchar| varchar|
    9 | varchar| varchar| datetime| varchar| varchar| varchar| varchar|
    10 | varchar| varchar| datetime| varchar| varchar| varchar| varchar|

    There are no fields that are related. The ID field is auto increment and each is unique to the other.

    I need to query both tables and then display the data through a while loop with a where clause, (ex where : color = blue), order by datetime DESC and limit 3. So it would display rows from both tables that have blue in the color field, and if the data was fetched from table 2 then the other columns that are not in table 1 would also display (material, finish, style, quality)...

    The only way I can use UNION is by setting the extra columns to 'blank' in the query, but then the second union will have null data and it would be useless.

    I can add null columns to the other tables of where they are missing, but this is inconvenient when there are many different fields.

    If I run two queries and grab for example 3 rows from each table with the where and order clauses, how would I display only 3 rows out of the 6 I grabbed from the two tables in a while loop?

  9. #9
    Join Date
    Mar 2004
    Posts
    369
    Quote Originally Posted by yahooman123 View Post
    There are no fields that are related. The ID field is auto increment and each is unique to the other.
    What happens when a product in table 1 has the same exact id as one in table 2? How do you know which product you are talking about?

    Quote Originally Posted by yahooman123 View Post
    I need to query both tables and then display the data through a while loop with a where clause, (ex where : color = blue), order by datetime DESC and limit 3. So it would display rows from both tables that have blue in the color field, and if the data was fetched from table 2 then the other columns that are not in table 1 would also display (material, finish, style, quality)...
    Something like this would fetch the data:
    Code:
    SELECT id, color, bran, `date`, motor, tire, NULL, NULL FROM table_1
    UNION
    SELECT * FROM table_2
    However, you wouldn't know if field 5 was a motor a material unless you selected something extra to let you know:
    Code:
    SELECT 'T1', id, color, bran, `date`, motor, tire, NULL, NULL FROM table_1
    UNION
    SELECT 'T2', * FROM table_2
    That first field would let you know for a given record whether it came from table 1 or table 2. However, you still have a problem that if you have 10 items in table 1 returned by this query then you probably won't see any items from table 2 if you are just pulling 3 or 6 items.

    Another problem with this is that you probably wouldn't be able to use associative indexes when looking at your data. Instead of $row['motor'], you'd probably have to use $row[4] instead -- if mysql doesn't complain about different data types or something.

    Quote Originally Posted by yahooman123 View Post
    The only way I can use UNION is by setting the extra columns to 'blank' in the query, but then the second union will have null data and it would be useless.
    The data would not be useless -- finish/style/quality values would not be fetched for table 1 because they don't make sense for cars (or bicycles or whatever).

    Quote Originally Posted by yahooman123 View Post
    I can add null columns to the other tables of where they are missing, but this is inconvenient when there are many different fields.
    The reason you are having problems is because you either need to redesign your database structure to support what you are trying to do or you need to abandon this query thing you are doing. It is quite awkward.

    Quote Originally Posted by yahooman123 View Post
    If I run two queries and grab for example 3 rows from each table with the where and order clauses, how would I display only 3 rows out of the 6 I grabbed from the two tables in a while loop?
    You're going to have to figure that one out yourself. What criteria do you want to use to pick 3 out of the 6? Randomly? Ordered by price?

    You just need to think more about what you are trying to accomplish here.
    Write multiplayer games with FlashMOG 0.3.1
    Try the MyPlan Salary Calculator

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