I need to join 4 table and perform select

[bobo420]

Hi!
I have 4 tables (table1, table2, table3, table 4)
I need to do select * from all four table and get them sorted

all the tables have field named id, so I figured that's the field I should join all the tables with

let's say table1 has 5 records in it, and table2 has 3
when I do select I want that id from table2 starts from 6 and goes to 8

so...

when i do select the id would go from 1-8

and is there a way that i could get an id from table2(or i have to subtract id from first table(table1) from all tables....)

how do I select * from 4 tables? they all have field name id and I want to join tables using that field

i use php, so I have a variable named $t and I want to use similar query to this
$t="something";
SELECT * FROM table1 WHERE field1 LIKE '%$t%' OR field 2 LIKE '%$t%'

it works when I use four queries but I want to be able to join queries and get one result... and if possible I want the id field not to have duplicate values (as described, i want them to go from 1-number_of_id_from_table1 and then just add that number to id from table2 and so on...)

and if there's a way to get the table name, or id(i.e.from table2).... I don't know how to descibe it... but if my table1 has 3 records and table2 has 2 record, then the first id from table2 would have id=5


thx

[pbmods]

Heya, bobo. Welcome to TSDN!

To join four tables:

SELECT
        *
    FROM
    (
            `Table1`
        LEFT JOIN
            `Table2`
                USING (`id`)
        LEFT JOIN
            `Table3`
                USING (`id`)
        LEFT JOIN
            `Table4`
                USING (`id`)
    )
 

http://dev.mysql.com/doc/refman/5.0/en/join.html

[bobo420]

i've tried that... and I think it works because I don't get nothing returned from mysql_error in php... but can I do mysql_fetch_array on such query or?

I've tried but I get :
supplied argument is not a valid MySQL result resource

[pbmods]

Heya, Bobo.

Let's see the code that runs the query.

[bobo420]

SELECT * FROM table1 LEFT JOIN table2 USING ('id') LEFT JOIN table3 USING ('id') LEFT JOIN table4 USING ('id')

[pbmods]

Heya, Bobo.

Right. I meant the PHP code that calls mysql_query() and mysql_fetch_assoc() :)

PS. Thanks for using CODE tags!

[bobo420]

$do=("SELECT * FROM table1 LEFT JOIN table2 USING ('id') LEFT JOIN table3 USING ('id') LEFT JOIN table 4 USING ('id')")

$t=mysql_fetch_array($do)

[pbmods]

Heya, Bobo.

In between those two lines, insert this code:

$result = mysql_query($do);
 

Then change your last line to read:

$t = mysql_fetch_assoc($result);
 

[bobo420]

sorry...
i missed to add mysql_query

here's my entire code for it, it displays only data from table 1

$do=mysql_query("SELECT * FROM table1 LEFT JOIN table2 USING (id) LEFT JOIN table3 USING (id) LEFT JOIN table4 USING (id)") or die(mysql_error());
if ($tred=mysql_fetch_array($do)){
do {
$mid=$tred['id'];
$lok=$tred['lokacija'];
$opis=$tred['opis'];
echo "$mid - $lok - $opis <br />";
} while ($tred=mysql_fetch_array($do));
}

all fields (id, lokacija,opis) are in all 4 tables
but all I get is ids from table1 and not the rest of the fields from table1 and no data from any other table

[bobo420]

after joining all four tables I need to get results matching keyword.
my query for one table is like this

$key="something";
$do=mysql_query("SELECT * FROM table1 WHERE lokacija LIKE '%$key%'  OR opis LIKE '%$key%'");

and it works, but for just one table... can I do UNION of four queries? Would that be easier?

If so... how?

thx

[pbmods]

Heya, Bobo.

If your tables have columns with the same names, then it sounds like your best bet is to use the UNION keyword. Either that or change your database structure so that related data is organized together. But that is not always an option.

To combine queries:

    SELECT
            *
        FROM
            `Table1`
UNION
    SELECT
            *
        FROM
            `Table2`
 

And so on.

[bobo420]

hi, thx for the responses...

can I use LIMIT on that query or how could I write that query

ie.

$do1=mysql_query("SELECT * FROM table1 WHERE opis LIKE '%$key%' OR lokacija LIKE '%$key%'");
$do2=mysql_query("SELECT * FROM table2 WHERE opis LIKE '%$key%' OR lokacija LIKE '%$key%'");
$do3=mysql_query("SELECT * FROM table3 WHERE opis LIKE '%$key%' OR lokacija LIKE '%$key%'");
$do4=mysql_query("SELECT * FROM table4 WHERE opis LIKE '%$key%' OR lokacija LIKE '%$key%'");
 

how could I make UNION using those queries and use i.e. LIMIT 0,5 or something?

and if not... could I just write

$do1="SELECT * FROM table1 WHERE opis LIKE '%$key%' OR lokacija LIKE '%$key%'";
 

so, that it isn't an actual query, but when I put it all together then run mysql_query?

how?

thanks

[bobo420]

$trag is $key, I've changed the name of the var

$do=mysql_query("SELECT * FROM table1 WHERE opis LIKE '%$trag%' OR lokacija LIKE '%$trag%' UNION SELECT * FROM table2 WHERE opis LIKE '%$trag%' OR lokacija LIKE '%$trag%' UNION SELECT * FROM table3 WHERE opis LIKE '%$trag%' OR lokacija LIKE '%$trag%' SELECT * FROM table4 WHERE opis LIKE '%$trag%' OR lokacija LIKE '%$trag%'")or die(mysql_error());
 

I get an error in the syntax...

[pbmods]

Heya, Bobo.

You can limit the results of your UNION by making the whole thing a subquery:

SELECT
        *
    FROM
    (
        (
            SELECT
                    *
                FROM
                    `Table1`
        )
        UNION
        (
            SELECT
                    *
                FROM
                    `Table2`
        )
    )
    LIMIT 0,5;
 

Note that this will start cutting results from the `Table2` query before cutting results from `Table1`.

And if you're getting an error, you have to tell us WHAT error you're getting before we can help you :)

[bobo420]

like I said, I get an error in the syntax, it doesn't say where... but here's the code I get from my browser

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM table4 WHERE opis LIKE '%ivana%' OR lokacija LIKE '%ivana%'' at line 1

[pbmods]

Heya, Bobo.

You're missing a UNION in between two of your queries.

[bobo420]

i get ->Every derived table must have its own alias

when i limit everything, if I don't do that it works...


btw. if you ever come to Croatia, you have as much beer as you can drink. on me. thx, very much :)

and...

i use the same if/do/whil loop as posted above...
how could I get the name of the table specific id is in?
I've tried with mysql_tablename and mysql_field_table but I didn't get right results...
thx

[pbmods]

Heya, Bobo.

For a solution to the 'Every derived table must have its own alias' issue, check out this thread.

In terms of determining the name of the table for each query, try this:

(
    SELECT
            'Table1'
                AS `TableName`,
            *
        FROM
            `Table1`
)
UNION
(
    SELECT
            'Table2'
                AS `TableName`,
            *
        FROM
            `Table2`
)