SQL query for two values of one row based off same table column

Question

I have two columns of one row of a report that I would like to be based off the same one column in a SQL table.

For example, in the report it should be something like:

ID | Reason       | SubReason
1  | Did not like | Appearance

In the SQL table it is something like:

ID | ReturnReason
1  | Did not like
1  | XX*SR*Appearance
1  | XX - TestData
1  | XX - TestData2

The SubReason column is being newly added and the current SQL query is something like:

SELECT ID, ReturnReason AS 'Reason'
FROM table
WHERE LEFT(ReturnReason,2) NOT IN ('XX')

And now I'd like to add a column in the SELECT statement for SubReason, which should be the value if *SR* is in the value. This however won't work because it also has 'XX' in the value, which is omitted by the current WHERE clause.

I hate databases designed like this!

user153923
– user153923

2013-10-16 13:36:26 +00:00
Commented Oct 16, 2013 at 13:36 — user153923
– user153923, Commented Oct 16, 2013 at 13:36

valex · Accepted Answer · 2013-10-16 13:45:53Z

2

SELECT t.ID, t.ReturnReason AS 'Reason',
SUBSTRING(t1.ReturnReason,7,10000) as 'SubReason '
FROM t
LEFT JOIN t as t1 on t.id=t1.id and t1.ReturnReason LIKE 'XX*SR*%'
WHERE t.ReturnReason NOT LIKE 'XX%'

SQLFiddle demo

answered Oct 16, 2013 at 13:45

valex

24.2k7 gold badges47 silver badges62 bronze badges

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

3 Comments

kyle_13 Over a year ago

Hi @valex, this did produce the result I wanted, however, once adding the JOIN, the query took a very long time to run (went from really quick to about 5 minutes). Any thoughts as to why this is happening? Could it be because the table is approximately 18GB and the query is joining it?

valex Over a year ago

If you can't change table design try to add indexes on ID and ReturnReAson fields

kyle_13 Over a year ago

I'm not looking to change table design or add an index if possible, is there any alternative ways to query or methods that might work? There is an index on the ID but not ReturnReason field.

Collectives™ on Stack Overflow

SQL query for two values of one row based off same table column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related