Issue with using Substring within SQL

Question

I am facing a problem coming up with a SQL query for a certain issue:

Table 1

Client ID|Name of Client|Customer Type|Account Type|

01| A |    AB        |      30      |
02| B |    AB        |      30      |
03| C |    CD        |      30      |
04| D |    CF        |      40      |

Table 2

Argument|Switch|

AB30     |  Y  |
CD30     |  N  |
CF40     |  Y  |

Table 3

ClientID|Name Of Client|External_Information|

01|A    | External A
02|B    | External B
03|C    | External C

Currently, I am trying to extract the information based on 3 different tables. I am suppose to extract the External Information from Table 3 based on 3 conditions

The Client ID must exist in Table 3 and Table 1
If the Client ID exist in Table 3 and Table 1, then we must check in Table 2 if the combination of Customer Type and Account Type (eg : For Client ID 01 , the combination would be AB30) in Table 1 exist in Table 2 and if the Switch in Table 2 = "Y".

My current SQL would be :

select External_Information 
from Table_3 a 
where a.ClientID = (select ClientID 
                    from Table_1 
                    where ClientID = a.ClientID)

This would solve Condition 1 , but for condition 2 I am not sure how we are able to do it within the same SQL query.

The expected result should be :

External_Information
-----------------------
External A
External B

your table 2 doesn't have foreign key in either table 1 or table3 add table1_id or table3_id first on your table 2 — CodeSlayer
– CodeSlayer, Commented Oct 23, 2014 at 2:34

Barmar · Accepted Answer · 2014-10-23 02:39:57Z

1

Use JOINs, and use CONCAT() to combine the two columns in Table 1.

SELECT a.`External_Information`
FROM Table_3 AS a
JOIN Table_1 AS b ON a.`ClientID` = b.`Client ID`
JOIN Table_2 AS c ON CONCAT(b.`Customer Type`, b.`Account Type`) = c.Argument
WHERE c.Switch = 'Y'

DEMO

edited Oct 23, 2014 at 2:39

answered Oct 23, 2014 at 2:34

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

Fatin Lutfi Over a year ago

Thanks for helping me out. The CONCAT statements works for me.

Collectives™ on Stack Overflow

Issue with using Substring within SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related