SQL - Query Multiple Dissimilar Tables (not UNION)

Transact-SQL
, , ,
1

Language: T-SQL
Server: SQL Server 2008 R2 - SQL Server 2014

I have what, based on searching here an elsewhere, appears to either be a unique problem or I can't properly verbalize what I'm trying to accomplish. I'd like to query across multiple dissimilar tables that have dissimilar field structures and JOIN them to a single other table. We have a table of ASSETS tb_assets and a table of LICENSES tb_licenses. I'd like to query across both of these and JOIN them to the table of VENDORS tb_vendors. I can't join tb_assets and tb_licenses together as they have no commonality. They do, however, reference the vendor GUID from tb_vendors.

Like this:

--------------------------                       ---------------------------
|        TB_ASSETS       |                       |    TB_LICENSES          |
--------------------------                       ---------------------------
| f_assetvendorGUID      | <~~~             ~~~> | f_licensevendorGUID     |
| f_assettag             |    |             |    | f_licensename           |
| f_assetname            |    |             |    | f_licenseexpirationdate |
|                        |    |             |    | f_licensequantity       |
--------------------------    |             |    ---------------------------
                              |             |
                       ~~~~~~~~             ~~~~~~~~~~
                       |                             | 
                       |    ----------------------   |
                       |    |     TB_VENDORS     |   | 
                       |    ----------------------   | 
                       ~~>  | f_vendorGUID       | <~~
                            | f_vendorname       |
                            ----------------------

For a short example, I want to search for "Dell" across a few fields. I'd like to query against tb_assets as well as against tb_licenses for their vendor as well as those table's name columns. Note that I am aware that the example below is the outdated JOIN style - I'm only showing this here as an illustration of what I'm trying to accomplish rather than something that actually works to try to better illustrate the problem.

SELECT 
    f_assetvendor AS 'AssetVendor', f_assettag AS 'AssetTag', f_assetname AS 'AssetName'
    f_licensevendor as 'LicenseVendor', f_licensename AS 'LicenseName', f_licenseexpirationdate AS 'LicenseExpirationDate', f_licensequantity AS 'LicenseQuantity'
FROM 
    tb_assets, tb_licenses
LEFT JOIN 
    tb_vendors ON f_vendorGUID = f_assetvendor
LEFT JOIN 
    tb_vendors ON f_vendorGUID = f_licensevendor
WHERE 
    f_vendorname LIKE '%Dell%' or f_assetname LIKE '%Dell%' or f_licensename LIKE '%Dell'

I'd prefer the results to appear like the following so that I can differentiate the items in code to show the results to the users in a favorable way. Note below that I have a result from each of the three WHERE statements above - hence the searching across all three tables.

AssetVendor          AssetTag           AssetName           LicenseVendor            LicenseName             LicenseExpirationDate          LicenseQuantity
=================================================================================================================================================================
Dell                 1001               OptiPlex 790        
Amazon               1002               Dell Printer
                                                            Dell                     Microsoft Office 2013   12/31/2016                     15  
                                                            Compellent               Dell Storage Manager    02/31/2017                     1

Worst case I have to run two distinct queries to accomplish this, but I'm hoping someone here could highlight a solution that lets me use a single query so I can learn something today. Forgive me if I didn't explain this very well, I've spend the better part of a day searching the Internet and can't seem to find exactly what I'm describing so my fear is that I'm describing it poorly because I'm surely not the first to want to accomplish something like this.

Thanks,
Matt

**Regarding my title stating "not UNION", I can't use a UNION here because with UNION column names for the final result set are taken from the first query, the columns must have the same data types, and both tables must have the same number of columns - none of which work for me here.

2

This might get you a step closer:

select a.f_assetvendor as AssetVendor
      ,a.f_assettag as AssetTag
      ,a.f_assetname as AssetName
      ,l.f_licensevendor as LicenseVendor
      ,l.f_licensename as LicenseName
      ,l.f_licenseexpirationdate as LicenseExpirationDate
      ,l.f_licensequantity as LicenseQuantity
  from tb_vendors as v
       left outer join tb_assets as a
                    on a.f_assetvendorguid=v.f_vendorguid
       left outer join tb_licenses as l
                    on l.f_licensevendorguid=v.f_vendorguid
 where v.f_vendorname like '%Dell%'
    or a.f_assetname like '%Dell%'
    or l.f_licensename like '%Dell%'
;

If you don't like the null values, wrap selected fields with ifnull function:

select isnull(a.f_assetvendor,'') as AssetVendor
      ,isnull(a.f_assettag,'') as AssetTag
      ,isnull(a.f_assetname,'') as AssetName
      ,isnull(l.f_licensevendor,'') as LicenseVendor
      ,isnull(l.f_licensename,'') as LicenseName
      ,isnull(l.f_licenseexpirationdate,'') as LicenseExpirationDate
      ,isnull(l.f_licensequantity,'') as LicenseQuantity
  ...
1 Like
3

Thanks BitsMed, you got it mighty close. Is there any way to have the results from the two tables appear in separate rows? Here's the result from the query:

AssetVendor	                        AssetTag	AssetName	LicenseVendor	                        LicenseName	          LicenseExpirationDate	        LicenseQuantity
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1DEBDECC-8F76-4AD8-8395-8E19FEE26578	1001	        OptiPlex 790	1DEBDECC-8F76-4AD8-8395-8E19FEE26578	Microsoft Office 2013     2016-12-31 00:00:00.000	15
22E38398-0227-409A-A6AD-B61953133D3A	1002	        Dell Printer	22E38398-0227-409A-A6AD-B61953133D3A	Dell Storage Manager	  2017-02-28 00:00:00.000	1
4

How about:

select a.f_assetvendor as AssetVendor
      ,a.f_assettag as AssetTag
      ,a.f_assetname as AssetName
      ,null as LicenseVendor
      ,null as LicenseName
      ,null as LicenseExpirationDate
      ,null as LicenseQuantity
  from tb_vendors as v
       left outer join tb_assets as a
                    on a.f_assetvendorguid=v.f_vendorguid
 where v.f_vendorname like '%Dell%'
    or a.f_assetname like '%Dell%'
union all
select null as AssetVendor
      ,null as AssetTag
      ,null as AssetName
      ,l.f_licensevendor as LicenseVendor
      ,l.f_licensename as LicenseName
      ,l.f_licenseexpirationdate as LicenseExpirationDate
      ,l.f_licensequantity as LicenseQuantity
  from tb_vendors as v
       left outer join tb_licenses as l
                    on l.f_licensevendorguid=v.f_vendorguid
 where v.f_vendorname like '%Dell%'
    or l.f_licensename like '%Dell%'
;
1 Like
5

You can solve that (as Bitsmed has shown) by having additional, NULL, columns in the first SELECT which you then use in the second SELECT (which will have NULL for the columns in the first SELECT that are not relevant).

However, I would have seen this as more of a presentation issue. Your example shows the first two rows displaying Assets (on the "left" of the report) and the last two rows showing Licenses (on the "right" of the report), but the two are on separate rows. I would build this so that the two were side-by-side, albeit that Assets and Licenses, on the same row, were not related to each other. Probably easiest to do this in the presentation layer, but it could be done in SQL, for example:

Query Assets to Temp Table-1 with a Row Number BY Vendor
Query Licenses to Temp Table-2 with a Row Number BY Vendor

Combine based on Vendor and Row Number (could probably use a FULL Outer Join to achieve that, or just based on the MAX Row Number, for each Vendor, from both Temp Tables)

[I've said "Temp Tables" but with a bit of thought I imagine it can all be combined into a single SQL statement)

1 Like
6

Thank you to both bitsmed and Kristen! I've learned a lot here today. The usage of the NULL "values" (if you will) makes complete sense now that I see it.

Kristen, the splitting of the results into entirely separate rows is merely for ease of display of data. I'm using classic VBScript to present the content on a web page and having it on separate rows merely means less code complexity there.

Again, thank you to you both for taking the time to help and explain. This forum has been my go-to place for the better part of a decade and I've learned an astounding amount here from the more-than-helpful members.

7

Seems to me to be a bit disjointed,for the user, having the Assets on several rows, with nothing on the right, and then having several rows of Licenses - with nothing on the Left. But if the users are happy then that's fine :slight_smile: