Select a.uid
, a.name
, mewp.data As Association
from Asset a Join
wshhistory wsh 
on a.uid = wsh.assetid 
Join worksheet w 
on wsh.wshid = w.uid left join
(Select assetid, DATA From field where flddefid = '042D2105-C999-47F1-BA1B-A9BD681B8393') mewp on a.uid = mewp.assetid
WHERE a.deleted = 0
and wsh.latest = 1
and a.typeid  in('14EB9198-C477-4C04-8C2B-E11672322F4C','17F42849-3F9A-4852-9C90-9F54F35B57B6')
and wsh.wshid in('223BB9DF-26B2-4C5E-8CC8-1D9FBAEEC8F7','E1B12C92-9C95-4757-90D8-57B6AC7BE247')

I have the following query that return the correct data. 

name	        Association
M-0024	        NULL
MEWP-0001B	NULL
MK-0007	        NULL
MEWP-0003	NULL
MK-0001A	MEWP-0001A
MEWP-0004B	NULL
MK-0001B	MEWP-0001B
MK-0006	        MEWP-0006
MEWP-0006	NULL
MK-0005	        MEWP-0004B
MK-0004B	MEWP-0004A
M-0025	        NULL
MEWP-0005	NULL
M-0023	        NULL
M-0022	        NULL
MEWP-0001Aa	NULL
MK-0008	
MEWP-0004A	NULL
MEWP-0001A	NULL
MK-0004A	MEWP-0003
MEWP-0002	NULL
MK-0003	        MEWP-0003
MK-0002	        MEWP-0002

When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables.. Thanks Richard 

SELECT a.uid,
       CASE WHEN mewp.data IS NULL THEN a.name END AS name,
       mewp.data AS Association
FROM ...

Which is probably completely wrong, but it was the best interpretation I could make of your question. It could help if you posted the desired result set given what you have.

The interpretation is correct but the desired result is as follow's;

name	          Association
MK-0001A	  MEWP-0001A
MEWP-0001A       NULL

It would be

name	          Association
MEWP-0001A        MEWP-0001A

Hopefully this clearer I need to be able to associate the name (Asset) MK-0001A to (Asset)MEWP-0001A

MEWP and the MEWP Key 

(Select assetid, DATA From field where flddefid = '042D2105-C999-47F1-BA1B-A9BD681B8393') mewp on a.uid = mewp.assetid

The MEWP Key reference's the which MEWP from the above select!

• Edited by Richard I Evans 13 hours 14 minutes ago

The interpretation is correct but the desired result is as follow's;

name               Association
MK-0001A       MEWP-0001A
MEWP-0001A       NULL

It would be

name               Association
MEWP-0001A        MEWP-0001A

Hm, I can make out:

Select a.uid
, mewp.data As Name
, mewp.data As Association
from Asset a Join
wshhistory wsh
on a.uid = wsh.assetid
Join worksheet w
on wsh.wshid = w.uid
join (Select assetid, DATA From field where flddefid = '042D2105-C999-47F1-BA1B-A9BD681B8393') mewp on a.uid = mewp.assetid
WHERE a.deleted = 0
and wsh.latest = 1
and a.typeid  in('14EB9198-C477-4C04-8C2B-E11672322F4C','17F42849-3F9A-4852-9C90-9F54F35B57B6')
and wsh.wshid in('223BB9DF-26B2-4C5E-8CC8-1D9FBAEEC8F7','E1B12C92-9C95-4757-90D8-57B6AC7BE247')

That is, return the same column both Name and Association and change the left join to an inner join to remove the NULL values.

No, I don't really think this is what you are looking for, but it is all I can figure out from the information you have given.

A common advice for this type of question is that you post:

1) CREATE TABLE statements for your tables.
2) INSERT statements with sample data, enough to illustrate all important angels of the problem.
3) The desired result given the sample.
4) A short description of the business rules that explains why you want this result.
5) Which version of SQL Server you are using.

When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables.. Thanks Richard 

Seems to me that you should separate your parent records by normalizing the database. I'd also suggest that you do NOT use UNIQUEIDENTIFIER for everything. That data type bloats everything and is unnecessary for most applications.

Please take a look at this and see if it meets your needs:

DECLARE @Asset TABLE (id int, name varchar(20))
DECLARE @Mewp TABLE (id int, data varchar(20))

INSERT INTO @Asset (id, name) VALUES (1,'M-0024'), 
	(2,'MEWP-0001B'), (3,'MK-0007'), (4,'MEWP-0003'), (5,'MK-0001A'), 
	(6,'MEWP-0004B'), (7,'MK-0001B'), (8,'MK-0006'), (9,'MK-0005'), 
	(10,'MK-0004B'), (11,'M-0025'), (12,'MEWP-0005'), (13,'M-0023'),
	(14,'M-0022'), (15,'MEWP-0001Aa'), (16,'MK-0008'), (17,'MEWP-0004A'),
	(18,'MEWP-0001A'), (19,'MK-0004A'), (20,'MEWP-0002'), (21,'MK-0003'),
	(22,'MK-0002')
INSERT INTO @Mewp (id, data)  VALUES (5,'MEWP-0001A'), 
(7,'MEWP-0001B'), (8,'MEWP-0006'), (9,'MEWP-0004B'), (10,'MEWP-0004A'),
(17,'MEWP-0003'), (21,'MEWP-0003'), (22,'MEWP-0002')

SELECT CASE WHEN a.name like 'mk%' then mewp.data else a.name end as Name, 
	mewp.data as Association
FROM @Asset a
	LEFT JOIN @Mewp mewp on (a.id = mewp.id)
WHERE (mewp.data not in (SELECT name FROM @Asset))
or (a.name like 'mewp%') or (a.name like 'm-%')

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your datA. You should follow ISO-11179 rules for naming data elements. What you posted is garbage. You should follow ISO-8601 rules for displaying temporal datA. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL.  

The purposes of a GUID is to locate entities GLOBAL to the schema; they are never used inside a schema. Noobs who grew up with pointer chains and OO will often use a GUID to replace a pointer, but this is so awful, I fire people for this. They have no understanding of RDBMS, SQL or modern tiered architectures.  

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells- Your code has an awful smell to it. 

Tables and columns change names in your code. Look at your magical u_id and see how it changes to a WSH.asset_id and then to a Woe WSH.wsh_id!  Wow! Can it turn into a squid or an automobile, too? The answer to that bit of sarcasm is yes; it is a pointer and does not follow the Law of Identity (remember that from Freshman Logic -- To be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all. This is fundamental law of math and logic is wrongly attributed to Aristotle. 

Your write SQL with assembly language bit flags! But you also put commas at the start of line; that is an old punch card programmer's trick to make the deck easier to re-arrange. The ards are gone, but the mindset lives like a ghost in your code. 

Why do you think that name, data and all your other vague generic  column names are clear and precise? That they follow iso-11179 rules? 

How can field ever be a table name? A table is a set, so the name has to be a collective or plural word. 

>> When I return the data how do I eliminate the name's that are in bold but keep the association record [sic: rows are not records] as this is a relationship between joining tables. <<

You got a basic term wrong. But then network database and file systems do have records. 

Having worked with crap SQL code for decades and helped with the SQL standards, I can predict this disaster will fill with orphan GUIDs and have to be cleaned out on a regular basis. Back in the 1970's when we used pointers in the early network databases, the system had garbage collection built in it. Of course, we had no data integrity when we mimicked them in SQL with GUIDs like you are doing. The disks filled up. Typing lists of GUIDs was a royalty pain. Etc. 

Can you start over? Will your boss pay for a year of training and re-writes? If not, update your resume; things are looking bad. 

To be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all. This is fundamental law of math and logic is wrongly attributed to Aristotle. 

"The wise are not perplexed, the humane do not worry, and the courageous do not feel fear". Analects - Confucius.

So I as come back to this, from your collection of literature Joe which do you feel I should start with ? So I can and will make that system better !

Thanks. 

• Edited by Richard I Evans 9 hours 4 minutes ago

So I as come back to this, from your collection of literature Joe which do you feel I should start with ? So I can and will make that system better !

My wife is Soto Zen, so when some allocates NVARCHAR(255) column, I load the Heart Sutra in Unicode Chinese. If I cannot teach them SQL, they can learn Zen :)

Start with the basics of Codd (warning! heavy math), then Date and my stuff. The real trick in your case is UN-learning 1970's pointer chains and network databases. PROGRAMMING STYLE would be a good place.