[adubb]

I have a database with 3 tables:

band_users: A User Table
fan_users: A User Table
band_fans: Relationship table.

Now the relationship table joins the 2 together by ID and username. so they are related.

What would the best way be to query information based on the relationship.

Lets say you a "Band" and your querying information from the Fan_users table based on fans that are related to you in the band_fans table.

Now i know theres a simple logic to it, but what if you have 1000+ fans information to query based on the them having a relationship with you

Would querying the ID's from the relationship table then looping through them each with php to query the info not KILL the server? Is there a method I could program to store this easier... by the way, the info your querying is for graphs and statistical purposes

Is there a way to join the whole thing into 1 query???

Any ideas? I know this is a SQL question but I was wondering if there is a PHP solution

Thanks

[odi]

You know what a join in SQL is?

This is a very classic INNER JOIN and is normally handled by one query:

Code:

select 
	fu.username 
from 
	fan_users fu,
	band_users bu,
	band_fans bf 
where 
	bf.band_username = bu.username and 
	bf.fan_username = fu.username and 
	bu.band_name = 'My Band';

I don't know exaclty the naming of your table, but with a little editing this should to it.

And of course you have to watch your queries if they're too big. A possibility is, only to query the subset you currently need, if you can't determine this with a given value like the date (so you can select values on a daily basis) you can do this with the LIMIT statement of MySQL (if you're using MySQL, other system have other ways to to this).