Rickard Rickard - 1 year ago 67
SQL Question

Joining tables depending on value of cell in another table

I have a database with a few tables of which I am trying to get some data from.
but due to the layout (which I can't do anything about), I can't seem to get a normal JOIN to work.

I have three tables:

  • datorer

  • program

  • volymlicenser

  • In the table "datorer" is a number of computers (registered with AD name, room number and a cell for comments).

  • In the table "program" is different programs that my organization have purchased.

  • In the table "volymlicenser" is the few licenses owned by the organization that is volume licenses.

The cells in here is ID, RegKey and comp_name.

Most programs are OEM licenses and only installed on one computer, hence they never needed to register the program names together with the belonging computer in another table like with the volume licenses.

When the database was designed, it was only containing the two last tables, and no join queries was needed. Recently they added the table "datorer" which consists of the said cells above.

What I would like to do now, is, preferably by one single query, see if the boolean cell program.VL is set to true.

If so, I want to join progran.RegKey on volymlicenser.RegKey, and from there get the contents from volymlicenser.comp_name.

The query I tried with, is the following.. which did not work.

prog.Namn AS Program, prog.comp_name AS Datornamn,
pc.room AS Rum, pc.kommentar AS Kommentar
program AS prog
datorer AS pc ON prog.comp_name = pc.comp_name
volymlicenser AS vl ON vl.RegKey = prog.RegKey
prog.Namn = "Adobe Production Premium CS6"

Hope someone can help me. :)

Please do ask if something is not fully clear!

The following are example records and desired results:


| id | comp_name | room | kommentar|
| 1 | MB-56C5 | 1.1 | NULL |
| 2 | MB-569B | 4.1 | NULL |


| id | Namn | amount | VL | RegKey | comp_name | leveranotor | purchased | note | Suite | SuiteContents |
| 1 | Adobe Production Premium CS6 | 2 | 1 | THE-ADOBE-SERIAL | NULL | Atea | 2012-11-01 | Purchased 2012 together with new computers | 1 | The contents of this suite |

| 2 | Windows 7 PRO retail | 1 | 0 | THE-MS-SERIAL | MB-569B | Atea | 2012-11-01 | Purchased 2012 together with new computers | 0 | NULL |

| 3 | Windows 7 PRO retail | 1 | 0 | THE-MS-SERIAL | MB-56C5 | Atea | 2012-11-01 | Purchased 2012 together with new computers | 0 | NULL |


| id | RegKey | comp_name |
| 1 | THE-ADOBE-SERIAL | MB-569B |

Desired result according to the SQL select query:

| Program | Computer name | Room | Kommentar|
| Adobe Production Premium CS6 | MB-569B | 4.1 | NULL |

Desired result when querying for Windows 7 PRO retail:

| Program | Computer name | Room | Kommentar|
| Windows 7 PRO Retail | MB-569B | 4.1 | NULL |
| Windows 7 PRO Retail | MB-56C5 | 1.1 | NULL |

Desired result if the "WHERE" was changed to "Windows 7 PRO Retail"

Simply put, if program.VL is 1, the comp_name will be found in the volymlicenser.comp_name column.

If program.VL is 0, the comp_name will be found in program.comp_name column.

Uppon finding the comp_name, it needs to join comp_name from any of these tables on datorer.comp_name to get the room number.

I hope that this makes as much sense to you as it does to me.

Answer Source

Take a look at COMP_NAME in PROGRAM -- it's NULL for the Adobe product. In following, the first regular join you wrote cut the Adobe out of the results. So, after the first join, you just ended up with the the Microsoft products. And then the second join using Reg_Key would have gotten you an empty table because the remaining RegKeys refer solely to "THE-MS-SERIAL".


SELECT prog.namn, coalesce(vl.comp_name, prog.comp_name), pc.room, pc.kommentar
FROM program as prog LEFT JOIN volymlicenser as vl
ON prog.RegKey = vl.RegKey
LEFT JOIN dataorer as pc
ON coalesce(vl.comp_name, prog.comp_name) = pc.comp_name

The use of left joins will preserve the contents of the tables to the left of the join syntax. This join method is required because the join keys are not consistently filled out through all three tables. And the coalesce function acts like an ifelse function. If the first variable is null then it is replaced with the contents of the next variable. Nifty.

By the way, I haven't run this myself.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download