[RESOLVED] SQL Server - Pull Two Fields From Separate Tables Into One 'Field'

[Dom]

Hi all,

Got a question about some SQL I'm writing (for SQL Server 2005): how do I select one field from one table, and another field from another table, and have them returned as one 'virtual' field?

I know how to do that when you're talking about two fields from the same table. It would look something like this:

Code:

SELECT FirstName + ' ' + LastName AS 'FullName'
FROM person;

That would produce one 'virtual' field called "FullName" that contained the first name, then a space character, and then the last name.

My problem is that I have two fields to combine into one, but the fields are in two different tables. I've got an asset table (called 'Asset') that contains basic details of every PC, laptop, server, monitor, etc., that we have in the business, including the unique identifying number, the Asset ID. Then there's a separate table that stores details specific to the PCs and laptops, including what we call a PC ID, which is just a number that looks something like "PC123", and is used as an easy way to name all of the PCs.

What I need is to select the description from the 'Asset' table, and the PC ID from the 'AssetComputer' table, and have them returned as a single field.

The problem is that not all assets have a PC ID. Monitors, for example, will have a record in the 'Asset' table (because they're an asset, after all) but because they're not a PC, they won't have a record in 'AssetComputer', and so won't have a PC ID to return. What I need is for all assets in the 'Asset' table to be included in the results, regardless of whether they have a PC ID, but if they do have a PC ID, then that is to be returned as well, preferably in this format:

PCID <space> Description

Here's a nice ERD of the tables in question (it's slightly wrong in that it's a one-to-one relationship, not one to many as the diagram shows):



Here's the SQL that I tried:

Code:

SELECT
tblAsset.AssetID,
(tblAssetComputer.PCID + ' ' + tblAsset.AssetDescription) AS 'Description'
FROM tblAsset
LEFT OUTER JOIN tblAssetComputer
ON tblAsset.AssetID = tblAssetComputer.AssetID;

And here's what it returned:



As you can see, it returned all the assets, regardless of whether they had an associated PC ID, but if they didn't have a PC ID, then the 'Description' field is returned as NULL, rather than just the contents of the 'AssetDescription' field like I would have expected.

If there's no PC ID, I'd like just the 'AssetDescription' field to be returned, but if there is a PC ID, I'd like the 'PCID' and the 'AssetDescription' fields to be returned as one 'virtual' field, called 'Description'.

Thanks in advance for any help!

[suedenem]

Not my specialism and I may not have read through your post properly, but would something like this work?

Code:

select concat(table_2.firstname, ' ',table_2.surname) AS 'FullName'

(Might need to convert to MS SQL syntax rather than MySQL)

[Dom]

Hehe thanks for the reply, but you're right, you may not have read the post properly!

What you've written is right, if you want to join two fields together when they're in the same table (see my first code example in the OP) but the trouble is, the two fields I want to join together are in two tables, i.e. one field is in one table, and the other field is in the another table.

I could just join the tables as you would do normally, but the problem is that when they're joined, one of the columns may not have a value in it. So you end up with one column from one table (which will always have a value) and then a second column with values from the second table joined to it, but the second column may not have a value in it. Therefore when you do the CONCAT function to join the first and second columns into one, any row that has no value in the second column simply returns nothing at all, instead of just the value from the first column.

Basically, for each row in the joined table, if there's a value in the first and second column, then CONCAT the two together. But if there's a value in the first column, but not in the second, then only return the value in the first column.

It's a bit tough to explain really, but nobody else here even remotely knows what I'm talking about so I have to reach out to the intarwebs.

[JasonD]

If PCID is null when empty then concat will be null.

IF (tblAssetComputer.PCID IS NULL, tblAsset.AssetDescription, tblAssetComputer.PCID + ' ' + tblAsset.AssetDescription) AS 'Description'

Probably.

[Dom]

Ooo I didn't realise you could have an IF statement in a SELECT like that. 

Just to be clear, I'm using SQL Server, not MySQL. But I'm guessing what you said about nulls is true of SQL Server as well as MySQL.

[Dom]

This did the trick:

Code:

SELECT
tblAsset.AssetID,
CASE
WHEN tblAssetComputer.PCID IS NULL
THEN tblAsset.AssetDescription
ELSE tblAssetComputer.PCID + ' ' + tblAsset.AssetDescription
END AS 'Description'
FROM tblAsset
LEFT OUTER JOIN tblAssetComputer
ON tblAsset.AssetID = tblAssetComputer.AssetID;

You have to use a Case statement rather than If, but it works! Thanks for you help all.

[samhs]

you could go one better and use isnull(thefield,[the value if it is null]) which can include columns - so:

Code:

select tblAsset.AssetID,
isnull(tblAssetComputer.PCID, '')+' '+tblAsset.AssetDescription as 'Description'
from tblAsset
left join tblAssetComputer
on tblAsset.AssetID = tblAssetComputer.AssetID

Just a wee bit tidier.

[samhs]

Documentation on isNull(): http://msdn.microsoft.com/en-us/library/aa933210(v=sql.80).aspx

HTH

[Dom]

Awesome thanks. I think I'll be bookmarking this thread for future reference.

As is always the case, now that I've solved the problem, the Powers That Be aren't sure if this is what they want. I don't care, it's what they're getting.

#BringOnTheWeekend