# Searching over multiple MySQL tables

**URL:** <https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799>\
**Category:** Rails\
**Created:** [April 26, 2006, 1:15am UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799 "2006-04-26T01:15:19Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 1:15am UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/1 "2006-04-26T01:15:19Z")

</div>

I am racking my brain over this, probably because I only know very  
simple mysql functions.

Basically I’ve got a few tables, ex:

Albums (id,name,band\_id); Bands (id,name,label\_id), and Label (id,name)

I want to search through both album.name, band.name, and label.name  
throwing all results into a variable, with no redundant info.

I think what I need to be doing is setting up some foreign key  
relationships.

I am on the right track? Or way off?

---

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 1:45am UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/2 "2006-04-26T01:45:49Z")

</div>

Dick D. wrote:

> I am racking my brain over this, probably because I only know very  
> simple mysql functions.
> 
> Basically I’ve got a few tables, ex:
> 
> Albums (id,name,band\_id); Bands (id,name,label\_id), and Label (id,name)
> 
> I want to search through both album.name, band.name, and label.name  
> throwing all results into a variable, with no redundant info.
> 
> I think what I need to be doing is setting up some foreign key  
> relationships.
> 
> I am on the right track? Or way off?

Thinking over this more… Should I be separating these tables in the  
first place? What would the advantage be over one table that looked  
like this:  
Music (id,band,album,label), than splitting the tables like I have  
above?

---

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 6:59pm UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/3 "2006-04-26T18:59:56Z")

</div>

here’s some SQL that should work:

select id, name, band\_id as subid, ‘Album’ as source  
from albums  
union all  
select id, name, label\_id, ‘Band’  
from bands  
union all  
select id, name, null, ‘Label’  
from labels

You could stick this in a view in the database, or run it from within  
Rails by one of the custom sql methods…

You could conceivably join this all into one basic table ala the  
Single Table Inheritance pattern as well… (with views to slurp out  
the individual subtypes if needed).

You do have indexes on the Name fields in those tables, right?

---

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 7:32pm UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/4 "2006-04-26T19:32:14Z")

</div>

all the solution provided so far used SQL, isnt there an elegant way to  
merge all the records using activeRecord? Or create an hash with all the  
names?

---

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 6:35am UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/5 "2006-04-26T06:35:29Z")

</div>

On 4/25/06, Dick D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > throwing all results into a variable, with no redundant info.  
> > above?  
> > no, you should keep the tables separate, to prevent redundancy. ie, if  
> > you  
> > use one table such as Music, it may look like this:

Table Music:

id | band | album | label  
1, band1, album\_a1, label1  
2, band1, album\_a2, label1  
3, band1, album\_a3, label1  
4, band2, album\_b1, label1  
5, band2, album\_b2, label2  
6, band2, album\_b2, label2

notice that band1 is repeated and label1 is repeated quite a few times.  
Also, if you wanted to change the name of label1 to “Label 1”, you may  
have  
to update thousands of rows (depending upon how large your table is).  
If  
you keep them separate, the schema would look like the following:

bands: (I would probably rename this table to ‘artists’)  
id | band\_name |  
1, band1  
2, band2

albums  
id | album\_name | band\_id  
1, album\_a1, 1  
2, album\_a2, 1  
3, album\_a3, 1  
4, album\_b1, 2  
5, album\_b2, 2

labels  
id | label\_name  
1, label1  
2, label2

bands\_labels (join table)  
band\_id | label\_id  
1,1  
2,1  
2,2

and your models would look like:

band.rb  
has\_many :albums  
has\_and\_belongs\_to\_many: labels (since a band can switch from one label  
to  
another during their lifetime)

album.rb  
belongs\_to: band

labels.rb  
has\_and\_belongs\_to\_many :bands

hopefully this will help you get started.

Mike

---

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 8:58pm UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/6 "2006-04-26T20:58:28Z")

</div>

Mike G. wrote:

> bands\_labels (join table)  
> band\_id | label\_id  
> 1,1  
> 2,1  
> 2,2
> 
> and your models would look like:
> 
> band.rb  
> has\_many :albums  
> has\_and\_belongs\_to\_many: labels (since a band can switch from one label  
> to  
> another during their lifetime)
> 
> album.rb  
> belongs\_to: band
> 
> labels.rb  
> has\_and\_belongs\_to\_many :bands

Thank you Mike. It looks like one of the primary items I was missing  
was a separate join table. When I set up inheritance in the modules  
like that, where does that functionality express itself when coding?  
Does it mean that I can liberally string together combinations of  
artist, album, label and attached it to a method for processing? I’ve  
been trying to find a resource that will explain the inheritance a  
little more in depth than the rails api.

---

<div class="post-metadata">

**Author:** ![dishkuvek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ebca7d/32.png) [@dishkuvek](https://www.ruby-forum.com/u/dishkuvek)\
**Post date:** [April 26, 2006, 9:00pm UTC](https://www.ruby-forum.com/t/searching-over-multiple-mysql-tables/58799/7 "2006-04-26T21:00:22Z")

</div>

Corey L. wrote:

> here’s some SQL that should work:
> 
> select id, name, band\_id as subid, ‘Album’ as source  
> from albums  
> union all  
> select id, name, label\_id, ‘Band’  
> from bands  
> union all  
> select id, name, null, ‘Label’  
> from labels
> 
> You could stick this in a view in the database, or run it from within  
> Rails by one of the custom sql methods…
> 
> You could conceivably join this all into one basic table ala the  
> Single Table Inheritance pattern as well… (with views to slurp out  
> the individual subtypes if needed).
> 
> You do have indexes on the Name fields in those tables, right?

No, I do not have indexes on the name fields. Clearly I need to dive  
into the documentation at [mysql.org](http://mysql.org) a little more. Additionally, I do  
not know what a view is.