I have a weird query and I'll try to explain it simply.
I am building an application inventory for my work. The database includes a single master table (tb1) and many slave tables. Some of the slave tables have a language code on them (so it can store english, french, spanish, whatever).
Here's a simple view of my database:
tb1 -app_id -app_name tb2 -app_id -language_code -security_doc1 -security_doc2 -security_doc3 tb3 -app_id -server_id tb4 -server_id -language_code -server_name
Okay, that's a pretty simple example. Here's my problem:
When a record is inserted, there may not be any security information or server information entered. So I do a left join and everything works out, except that if there is a server set for this application, there will be multiple records. So, I say that tb4.language_code = 1 it will work for all records that have a server set, but the ones that don't will pull down 0 records.
This works and pulls out atleast one record in all situations:
SELECT * FROM tb1 LEFT JOIN tb2 ON tb1.app_id = tb2.app_id LEFT JOIN tb3 ON tb1.app_id = tb3.app_id INNER JOIN tb4 ON tb3.server_id = tb4.server_id
This will give me the result that I want, but if there is no security or server row, it will not return anything.
SELECT * FROM tb1 LEFT JOIN tb2 ON tb1.app_id = tb2.app_id LEFT JOIN tb3 ON tb1.app_id = tb3.app_id INNER JOIN tb4 ON tb3.server_id = tb4.server_id WHERE tb2.language_code = 1 AND tb4.language_code = 1
I thought about doing a bunch of inner joins, but I have 24 tables to work with, and each of those could have multiple columns, so it'll be a mess.