0

Hey all,
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.

Thanks Everyone!

2
Contributors
1
Reply
2
Views
9 Years
Discussion Span
Last Post by dickersonka
0

The inner join on tb4 will only pull records that are in tb3 and tb4.
The query is also limiting tb2.language_code, so the query there is requiring every table to have the data regardless of the left join
I don't necessarily understand what you mean by security row, but the where clause with the inner join is the problem of limiting id's that will need to be present in all tables.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.