manager, hours, policies, rent, and properties (which ties them all together). Now let's say I need to find out all manager, hours, policies, and rent information for property #1...
What is the advantage of doing this with one query as opposed to doing it with many?
I have it set up to just do an individual query for the information I need from each table. Is this the wrong way to do this?
SELECT * FROM properties
which provides me with the necessary ids to perform the following queries...
SELECT * FROM manager WHERE id = 2
SELECT * FROM hours WHERE id = 4
SELECT * FROM policies WHERE id = 2
SELECT * FROM rent WHERE id = 7
performing a join with all those tables may cause serious load on your database server. You can, and should test it however to find out how your server will react.
I would expect that one query is executed faster than n queries... Anyway, if you want to know the answer you should run a couple of tests... And then share those results with us
I'm curious to see the test results. I find myself jumping from one method to the other, and I'd like a justified reason to remove some chaos from my code
well the only problem is that my database is TINY... I mean like 5 records in each table. The only reason I chose to use a database at all is because of the relational use. I can more easily allow for my customer to administer a database than a text file. 5 records will produce some pretty quick results no matter what... will a test really show a difference between the two? I guess I will have to find out.
Furthermore, I do not know where to begin as far as turning that into one query. Oh well... this will be a learning experience.
left join on `photos`.`location` = `locations`.`id` and `photos`.`default` = 1
Can examine redundantly, meaning if you have 20 locations to select default photos for it is joining the entire photos table onto the locations table for each location, multiple queries for each photo would be faster in this scenario. In the case of one row being returned the JOIN almost always wins (again there are exceptions). I discovered this on accident, so it could possibly be a bug or flaw in my table structure but I doubt it.