mysql create a hardcoded value within select statement

Question

I want to know if I can hardcode a value within my select statement. I have the following mysql query that I use to generate a list.

I use concat to build this string as part of creating the list. However, now I need to generate an 'Unknown' record as part of the list.

For example:

20(ABC Object #20)
24(DEF Object #24)

I want to add a value of 'UKNOWN' to the top of the list:

--(UNKNOWN -- )
20(ABC Object #20)
24(DEF Object #24)

Here is what I have so far:

SELECT CAST(p.Serial AS UNSIGNED INTEGER) as Serial, 
p.Object_ID, 
p.Part_ID, 
st.Description AS ObjectDesc, 
s.Object_Num, 
concat(Serial,' (',st.Desc,' #',s.Object_Num,')') as DropList 
FROM Parts p LEFT JOIN Objects s ON p.Object_ID = s.Object_ID 
LEFT JOIN ObjectTypes st ON s.ObjectType_ID = st.ObjectType_ID

Can I hardcode that string in my select statement? If so, how do I do that?

select '--(unknown -- )' union all select ...your query here ...? — Marc B
– Marc B, Commented Feb 2, 2015 at 16:33

Robbert · Accepted Answer · 2015-02-02 16:35:27Z

1

If I understand correctly, you want a new row. You can use UNION

SELECT "" as Serial,
   "" as Object_ID,
   "" as ObjectDEsc,
   "" as Object_Num,
   "--(UNKNOWN -- )" as DropList
UNION
 SELECT CAST(p.Serial AS UNSIGNED INTEGER) as Serial, 
  p.Object_ID, 
  p.Part_ID, 
  st.Description AS ObjectDesc, 
  s.Object_Num, 
  concat(Serial,' (',st.Desc,' #',s.Object_Num,')') as DropList 
 FROM Parts p LEFT JOIN Objects s ON p.Object_ID = s.Object_ID 
 LEFT JOIN ObjectTypes st ON s.ObjectType_ID = st.ObjectType_ID

In a union query each part must select the same columns. Since your original query included Serial, Object_ID, Part_ID and ObjectDesc, the first part must include it as well.

answered Feb 2, 2015 at 16:35

Robbert

6,6226 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

noober Over a year ago

since I had a LEFT JOIN condition in my from clause with my original query, do I also need to add that to the top query?

Robbert Over a year ago

No. They are independent queries.

Collectives™ on Stack Overflow

mysql create a hardcoded value within select statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related