SQL Joining two tables and relabeling column

Question

I am using an SQL Database, which is a DBMS. Its the Lyric Database. (I'll be happy to clarify if my description is insufficient).

My salespeople table (with the salesids) is as follows:

SalesPeople:

SalesID | FirstName | LastName
------- | --------- | --------
        |           |

and my Studios table is as follows:

Studios:

StudioID | Name | City | Contact | SalesID
-------- | ---- | ---- | ------- | -------
         |      |      |         |

I have started the Query in the following way:

select st.salesid, coalesce(sp.salesid, 'Does Not Work')
--Does This Match Harry Lee?
from studios st
inner join salespeople sp on sp.salesid = st.salesid;

and as well:

select st.*
from studios st
inner join salespeople sp
    on sp.salesid = st.salesid
where st.contact = "Harry Lee";

But I am unsure as to relabel the column. I know to relable the column (as I sort of hint in the query above with the coalesce function).

However, how to I include that in the prior query whilst still adhering to the specifics of question? And again, I'll be happy to clarify the question if my description is insufficient.

BellevueBob · Answer 1 · 2013-11-03T03:04:22.750

It's really hard to tell what you want, but perhaps this will do:

select *
from SalesPeople s
where exists (
   select 1
   from   Studios
   where  Studios.SalesID = s.SalesID
      and Contact = 'Harry Lee'
   )

This is based on the requirement stated in the second sentence of your question and not the code example you show (which was a bit confusing).

EDIT: Reading over your revised question, try this:

select distinct a.SalesID
    . case when b.SalesID is null
           then 'doesn't work with Harry'
           else 'works with Harry'
           end as "Harry?"
from SalesPeople a
left outer join Studios b 
on   b.SalesID = a.SalesID
 and b.Contact = 'Harry Lee'

score 0 · Accepted Answer · edited May 23 '17 at 12:29

The coalesce(sp.salesid, 'Does Not Work With Harry Lee') will always return sp.salesid in your case. The reason for that is that you are using an INNER JOIN which by definition will only return a row when a match exist. It will not return null values.

Your statement is correct with a slight change: use an OUTER JOIN. You can read more about it and about the difference between both here.

Try to use the following. This will return all the available salesids that have Harry Lee as contact plus all those who don't where Does Not Work With Harry Lee is returned:

SELECT st.salesid, COALESCE(sp.salesid, 'Does Not Work With Harry Lee')
FROM studios st
LEFT JOIN salespeople sp ON sp.salesid = st.salesid;
WHERE st.contact = "Harry Lee";

If you are only interested in the salesids that have Harry Lee as contact, than just use the following:

SELECT st.salesid
FROM studios st
INNER JOIN salespeople sp ON sp.salesid = st.salesid;
WHERE st.contact = "Harry Lee";

EDIT :

Following your last edit, in order to get that result you should use the following. You can control the result of the query using the WHERE clause:

SELECT st.salesid, 'works with Harry' AS [Harry?]
FROM studios st
INNER JOIN salespeople sp ON sp.salesid = st.salesid;
WHERE st.contact = "Harry Lee";

UNION ALL

SELECT st.salesid, 'doesn't work with Harry' AS [Harry?]
FROM studios st
INNER JOIN salespeople sp ON sp.salesid = st.salesid;
WHERE st.contact <> "Harry Lee";

SQL Joining two tables and relabeling column

2 Answers2

EDIT :