sql query to take data out of multiple tables

Question

I have (several tables say) 4 tables, A, B, C and D and they are linked by some common values like:

TableA(A1,A2,A3,A4) ---> TableB(A1,B2,B3,B4) ---> TableC(C1,C2,B3,C4) ---> TableD(D1,D2,D3,C4)
                               |                             |
                         TableX(X1,B2,X3,X4)      TableY(Y1,Y2,Y3,C4)

For each unique value of A2, there is a value A1. Same Value A1 is there in TableB. Similarly in TableB, for each value of A1, there are some particular values B2,B3,B4, In the same way in TableC and TableD also, for each value of B3 and C4 respectively there are particular remaining values.

In addition to the linear flow there are multiple flows inbetween (like from TableB to TableX or TableC to TableY)

I want to extract some columns say for instance A1,A2,B2,C1,D1 and C4 if I am given a value of A2.

I am new to this type of queries and short of time to complete the task.

Thanks in advance for the help.

possible duplicate of [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) — Andy Refuerzo, Oct 03 '14 at 09:14

score 0 · Answer 1 · answered Oct 03 '14 at 09:26

0

You must use a multiple join like this:

Select TableA.A1,TableA.A2,TableB.B2,TableC.C1,TableC.C4 from
TableA join TableB on TableA.A1=TableB.A1
join TableC on TableB.B3=TableC.B3    
where A2='....'

answered Oct 03 '14 at 09:26

apomene

14,282
9
46
72

score 0 · Answer 2 · answered Oct 03 '14 at 11:53

0

Although a horrible question posted with poor sample of tables and explanations, Start first with a query on how to tie all the fields together. Once you have all your JOIN conditions established, THEN add the WHERE you are looking for specific elements from the data

select *
   from
      TableA a
         JOIN TableB b
            on a.A1 = b.A1
            JOIN TableC c
               on b.b3 = c.b3
               JOIN TableD d
                  on c.c4 = d.c4
   where
          a.field = '?'
      AND d.field = '?'

or whatever criteria you are looking for... and just use the alias references, such as (a, b, c, d)

Notice from the layout I have my SQL, you can exactly see the hierarchy of how one table connects to the other by simple visual indentation and very clear column relations between the respective tables.

answered Oct 03 '14 at 11:53

DRapp

47,638
12
72
142

thanks for bearing with such a horrible question, but working for the first time and need to finish the project today, i had no other option. I got the flow but what if Flow is not a linear. Like from TableA to TableB to TableC to TableD is existing (as in initial question) but if I have one sub-flow say from TableB to TableX and may be another subflow like Table C to TableY. How should I accomodate this? Thanks for your help. – Jivi Oct 03 '14 at 12:40
@Jivi, you must have SOME sort of connection between the tables of common elements. That is the whole point of a relational database. Just add join's to the respective other tables. If tableC can join to D, X and Z, just have each respectively joined to the C table. if there is not a guaranteed record in say.. tableX, then do it as a LEFT JOIN meaning I want the record from the left table (ex: TableC) even if there is not one in the right side table (tableX). If you have more of these subflows, update your question and I'll revise my answer. – DRapp Oct 03 '14 at 12:51

sql query to take data out of multiple tables

2 Answers2