Search in three table same time

[eluser]Sawariya[/eluser]
Hi friends

How to search in three tables at same time... here i think join is not possible..
any idea..??

expecting your help...

[eluser]adamp1[/eluser]
That I know of you can only search one 'table' at a time. A Join seems to be the only option. If you can't join then 3 searches.

[eluser]Sawariya[/eluser]
I will explain my table structure

Table one- content
-----------
table two - events
--------------
table three- gallery

there is no relation with all these three table..
so how can search in these three table for particular data which user looking for.

any idea...

[eluser]adamp1[/eluser]
Either search each individually OR multiply the tables together and then search that, but I would not suggest the second. Since if you had only 3 tables with 2 items in each it would create a new table with 8 items in. This amount goes up exponentially. {number of tables}^{number of items in each table}. So for 3 tables with 10 items in each that's 59049 rows to search over.

As said search each for what the user wants.

[eluser]webthink[/eluser]
If you have a search that gets run often and needs to search all of those tables at the same time you should consider a table that combines the searchable fields of all three. Also if it's a text based search look into Zend Lucene search index library.

[eluser]jcopling[/eluser]
If I'm understanding your problem correctly you are trying to search 3 different tables for the same criteria and then return the results as 1 query.

If that is correct you can achieve this quite simply by using the UNION command.

I don't believe that Code Igniter has yet implemented this into the Active Record class but it is not difficult to do on your own.

I would suggest doing something like this:

Code:

$sql  = "SELECT Field1 AS CommonFieldName FROM Table1 WHERE Condition ";
$sql .= "UNION";
$sql .= "SELECT Field2 AS CommonFieldName FROM Table2 WHERE Condition ";
$sql .= "UNION";
$sql .= "SELECT Field3 AS CommonFieldName FROM Table3 WHERE Condition ";

$this->db->query($sql);

The only thing that you must remember here is that 3 queries will be combined into 1 and therefore all field names must be the same in all 3 queries.

Hope this helps.

[eluser]Sawariya[/eluser]
Thanks for your rply jcopling

Here field names are different in all three tables...

#1222 - The used SELECT statements have a different number of columns

i am getting above error while executing this query...

[eluser]jcopling[/eluser]
I'm guessing that the error says it all. I suppose I forgot to mention that all queries included in UNION statements must have the same number of fields returned and they must all be named the same thing.

Hope that helps.

[eluser]yayot[/eluser]
If your tables are related, you can use a inner join trough a table, and then with and or OR, you can choose what field you want from the tables.

like so:

select table1.field1, table2.field2, table3.field3
from relationshiptable
innjer join
table1
on
table1.id = relactionshiptable.table1_id
innjer join
table2
on
table2.id = relactionshiptable.table2_id
innjer join
table3
on
table3.id = relactionshiptable.table3_id
WHERE
table1.field1 like '%something%'
or
table2.field2 like '%something%'
or
table3.field4 like '%something%'

Or somthing like that.

Hope it helps

[eluser]brycmills[/eluser]
The following is an example that you may find useful:

Example Tables:

Code:

TBL: BREEDS
breed_id
breed_name
active
...

TBL: FAMILY
family_id
breed_id
family_name
active
...

TBL: STYLE
style_id
breed_id (if the style reflects breed specific)
family_id (if the style reflects family specific)
style_name
active
...

QUERY EXAMPLE:

Code:

select * from (breeds left join family on breeds.breed_id = family.breed_id) left join style on style.breed_id = breeds.breed_id

Of course, you will may want to insert this with CI $this->db

Hope this helps