SQL Server query to retrieve data from two tables with multiple conditions

Question

I have two tables, Table1 and Table2.

Table1 has the following columns

date
u_id
t_id
col_t1_a
col_t1_b
col_t1_c

and Table2 has the following columns

date
ux_id
tx_id
col_t2_a
col_t2_b
col_t2_c

Table1.u_id is a sub-string of Table2.ux_id and Table1.t_id = Table2.tx_uid.

I am trying to get all the columns (col_t2_c) from Table2 when all the below conditions are true

Table1.t_id = Table2.tc_uid and
substring(Table2.ux_id,7,6) = Table1.u_id and
Table1.date = Table2.date = 'yymmdd' and
Table1.col_t1_a = 'A' and
Table1.col_t1_b = 'B' and
Table1.col_t1_c = 'C'

I need some help in writing a SQL Server 2008 query to achieve the above.

Any help will be grateful.

The Shooter · Accepted Answer · 2016-08-05 06:57:07Z

1

Try this query:

SELECT * 
FROM Table2 T2
INNER JOIN
    Table1 T1 ON T1.t_id = T2.tx_uid
    AND T1.u_id = substring(t2.ux_id, 7, 6)
    AND T1.[Date] = T2.[Date]
WHERE
    T1.[date] = 'date'
    AND T1.col_t1_a = 'A'
    AND T1.col_t1_b = 'B'
    AND t1.col_t1_c = 'C'

answered Aug 5, 2016 at 6:57

The Shooter

7333 silver badges10 bronze badges

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

Comments

gofr1 · Accepted Answer · 2016-08-05 07:01:06Z

0

All your conditions are already written. Just put them into query:

SELECT t2.col_t2_c
FROM Table2 t2
INNER JOIN Table1 t1
    ON t1.t_id = t2.tc_uid and 
        substring(t2.ux_id,7,6) = t1.u_id and
        t1.date = t2.date
WHERE   
        t2.date = 'yymmdd' and 
        t1.col_t1_a = 'A' and
        t1.col_t1_b = 'B' and
        t1.col_t1_c = 'C'

answered Aug 5, 2016 at 7:01

gofr1

16k11 gold badges47 silver badges56 bronze badges

Comments

Mono · Accepted Answer · 2016-08-05 07:03:51Z

0

SELECT * 
FROM Table1 t1, Table2 t2
WHERE t1.t_id = t2.tx_uid 
AND t1.u_id = substring(t2.ux_id,7,6)
AND t1.date = t2.date
AND t2.date = 'yymmdd'
AND t1.col_t1_a = 'A'
AND t1.col_t1_b = 'B'
AND t1.col_t1_c = 'C'

answered Aug 5, 2016 at 7:03

Mono

3306 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL Server query to retrieve data from two tables with multiple conditions

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related