0

I have two tables and have used a simple query:

SELECT * FROM innerb where not exists 
   (select * from wms where barcode = innerb.barcode) and pcode like '%102';

My results come out like this:

enter image description here

I need to add in a comma for all values so it looks like this:

enter image description here

But I don't want to update the table nor create a new table, just to add it in for few seconds.

Any ideas?

2 Answers2

2

You could create a view:

CREATE VIEW innerb_comma AS
SELECT CONCAT(Pcode, ',') AS Pcode,
       CONCAT(Brand, ',') AS Brand,
       CONCAT(Pdescription, ',') AS Pdescription,
       CONCAT(Size, ',') AS Size,
       CONCAT(Barcode, ',') AS Barcode
FROM innerb
WHERE NOT EXISTS (SELECT * FROM wms WHERE barcode = innerb.barcode) 
  AND Pcode like '%102';

Then select from that instead:

SELECT * FROM innerb_comma

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • What is a view? I can see I have a new view inside my SCHEMAS inside the navigator on the left hand side of the screen. And how would I drop it? –  Apr 01 '19 at 07:41
  • 1
    A view just allows you to create a different way of looking at the data from a table. You might for example sum some columns in it, or multiply one by another. It has no effect on your actual tables. You can remove it with `DROP VIEW innerb_comma`. See the [manual](https://dev.mysql.com/doc/refman/8.0/en/create-view.html) – Nick Apr 01 '19 at 07:44
  • 1
    @Eduards I've corrected the view definition. Because you are trying to compare the `barcode` with the value in the `wms` table (which doesn't have a comma appended), the entire query needs to be in the view. I've also added a demo – Nick Apr 01 '19 at 08:01
  • He doesn't need the view, he can just execute the embedded SQL in that view definition, and it would return what he needs. – Lasse V. Karlsen Apr 01 '19 at 10:11
  • 1
    @LasseVågsætherKarlsen agreed. If OP needs to execute the query more than once the view makes it easier. – Nick Apr 01 '19 at 11:22
0
SELECT pcode || ',' as pcode
     , brand || ',' as brand
  FROM table

EDIT: Only works on some DBs. Apparently not MYSQL(unless you configure it to work). What does SQL Select symbol || mean? has a list of what works on which DB

TineO
  • 1,023
  • 8
  • 24
  • 1
    `||` is an `OR` [operator](https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_or), how is this supposed to add comma? – barbsan Apr 01 '19 at 08:12
  • Not in SQL. || is basically the same thing as CONCAT just shorter and in my mind, more readable. Might be different in different DBs, but I think its quite common. – TineO Apr 01 '19 at 09:37
  • I linked docs for MySQL in my comment, also tried in dbfiddle and it behaved like OR. – barbsan Apr 01 '19 at 09:41
  • Seems that Oracle and PostgreSQL [support](https://www.oreilly.com/library/view/sql-in-a/9780596155322/re92.html) `||` as concatenation operator but OP uses MySQL – barbsan Apr 01 '19 at 09:48
  • It would appear so yeah. Good list here: https://stackoverflow.com/questions/23372550/what-does-sql-select-symbol-mean – TineO Apr 01 '19 at 10:00
  • 1
    Accepted answer there states *mysql: concat ( vararg function ). caution: || means 'logical or'*. There's a note that you can configure MySQL to use `||` as a concat operator but by default it's an `OR` operator – barbsan Apr 01 '19 at 10:06