CODEWITHSUNDEEP
sqlsql-server-2008
shiva baral
shiva baral

Reputation: 69

SQL query to extract the part of matching SQL string

Let's consider a CLASS and BILL table.

CLASS table:

Class Description
F Factory
H Headoffice
C001 Electrical
C002 Mechanical
C003 Civil

BILL table:

BILL_NO DOCCLASS
1 FC001
2 FC002
3 FC003

Description: Class is a primary key. Let's say we created invoices by tagging different class from the CLASS table.

For example invoice 1 is created by tagging FCOO1 (ie. Factory and Electrical)

Problem: how to write a SQL query to get only COO1 part from FC001 of Invoice 1?

This SQL code is not working:

Select
    BILLDET.BILL_NO,
    BILLDET.CLASS,
    BILLDET.GLCODE,
    CLASS.DESCRIPT
From
    BILLDET 
Full Join 
    CLASS On BILLDET.CLASS = CLASS.CLASS

Output:

Bill NO DESCRIPT
1 FactoryElectrical

Thank you

Upvotes: 1

Views: 76

Answers (2)

TimLer
TimLer

Reputation: 1360

select bill_no, Description
from
(
select c2.class + c1.class as keys, c2.description+c1.description as Description
from class c1, class c2
where c1.class LIKE 'c%' AND 
      c2.class NOT LIKE 'c%'
)as tab, bill
where keys = DOCCLASS;

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74710

Ugh, what a way to store data

WITH classx AS(
  SELECT
    c.class+n.class as class,
    c.descript+n.descript as descript 
  FROM
    class c 
    JOIN class n 
    ON 
      c.class LIKE 'c%' AND 
      n.class NOT LIKE 'c%'
)

SELECT * FROM bill JOIN classx ON bill.docclass = classx.class

Upvotes: 1

Related Questions