Use concatenated name to filter results but don't display name in table

Question

I need to produce the following:

Comp No    Year Purchased
=========================

11111       2008
22222       2007
33333       2008
44444       2006

But I need to exclude some results based on a concatenated name. And I can't have the name listed in the result. This is my code:

SELECT 
Comp_Num,
YEAR (Comp_PurchaseDate) AS 'Year Purchased',
CONCAT(Emp_First, ' ', Emp_Last) as 'Name'
FROM Computer
JOIN Hire using (Comp_Num)
JOIN employee using (Emp_Num)
ORDER BY Comp_Num;

It produces:

Comp No    Year Purchased   Name
================================

11111       2008            AAA
22222       2007            BBB
33333       2008            CCC
44444       2006            DDD

The concatenated name is used to filter out results, eg:

WHERE ('Name' <> 'AAA' AND
        Name' <> 'DDD')

How do I create the concatenated name to filter out results without displaying the column? The concatenate doesn't work without "as 'name' ".

And how do I use the concatenated name to filter? Can I still use Where? Or is there another clause?

score 2 · Accepted Answer · answered Dec 12 '18 at 09:12

2

You can simply define the expression in the WHERE clause instead:

SELECT 
Comp_Num,
YEAR (Comp_PurchaseDate) AS `Year Purchased`
FROM Computer
JOIN Hire using (Comp_Num)
JOIN employee using (Emp_Num)
WHERE (CONCAT(Emp_First, ' ', Emp_Last) <> 'AAA' AND
       CONCAT(Emp_First, ' ', Emp_Last) <> 'DDD')
ORDER BY Comp_Num;

Also Read: When to use single quotes, double quotes, and back ticks in MySQL

answered Dec 12 '18 at 09:12

Madhur Bhaiya

28,155
10
49
57

Thank you @Madhur Bhaiya. I also need to filter Comp_PurchaseDate <= 2008. – ppines Dec 12 '18 at 09:56
I thought it would go in the where clause but this just produces an empty table. – ppines Dec 12 '18 at 09:57
@ppines use `where YEAR (Comp_PurchaseDate) <= 2008`. – Madhur Bhaiya Dec 12 '18 at 09:58
Fabulous! Thanks again @Madhur Bhaiya – ppines Dec 12 '18 at 10:10

score 0 · Answer 2 · answered Feb 13 '21 at 04:49

A simpler alternative to Madhur's two CONCAT() calls is to use NOT IN () and only concatenate once.

SELECT Comp_Num,
    YEAR (Comp_PurchaseDate) AS `Year Purchased`
FROM Computer
JOIN Hire USING Comp_Num
JOIN employee USING Emp_Num
WHERE CONCAT(Emp_First, ' ', Emp_Last) NOT IN ('AAA','DDD')
ORDER BY Comp_Num

Use concatenated name to filter results but don't display name in table

2 Answers2