SQL Statement to retrieve information from multiple tables

Question

I need some help constructing a SQL Statement. I just have basic knowledge concerning SQL (insert, delete, alter, where, select) but never really had to deal with aggregate functions or joins.

So here is the setup:

TABLE A

cust_id
prod_id
statusCode
...

TABLE B

cust_id
land_id
...

TABLE C

country_id
...

TABLE D

country_id
country_code

TABLE E

product_id
country_code1

What the SQL Statement should output is: All rows where the statusCode from Table A is 1, or 2, or 3 and where the country_code == country_code1.

Where country_code can obtained via Table B,C,D and country_code1 via Table E.

Please do not post answers concerning the database structure itself since i have no rights to change them.

My approach was this but it is clear that it is horribly wrong since I am a SQL beginner:

SELECT * FROM TableA
INNER JOIN TableB ON TableA.cust_id = TableB.cust_id
INNER JOIN TableC ON TableB.landId = TableC.country_id
INNER JOIN TableE ON TableA.prod_id = TableE.product_id
INNER JOIN TableD ON TableE.country_code1 = TableD.country_code
WHERE statusCode IN (1,2,3)

terary · Accepted Answer · 2015-01-03 16:34:39Z

1

Off the top of my head.

Join the two groups of tables with FK Join those groups, Restrict that super set more to come

SELECT * 
FROM (tableA A INNER JOIN tableB B ON A.cust_id=B.cust_id)

INNER JOIN  tableE E ON E.product_id=A.prod_id
INNER JOIN (tableC C INNER JOIN tabldeD D ON D.country_id)
ON D.country_code = E.country_code1
WHERE A.statusCode IN(1,2,3)

We don't have to worry about the country code bit because it is in the 'join'.

edited Jan 3, 2015 at 16:34

answered Jan 3, 2015 at 16:26

terary

1,13815 silver badges31 bronze badges

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

Comments

Karel Kubat · Accepted Answer · 2015-01-03 16:18:27Z

0

Off the top of my head (untested, and I'm not 100% sure that this is what your requirement is):

select a.cust_id, a.prod_id, a.statusCode,
       b.land_id,
       c.country_id,
       d.country_code,
       e.product_id
from   TableA a, TableB b, TableC c, TableD d, TableE e
where  a.cust_id = b.cust_id            -- basic joins on common fields
  and  b.land_id = c.country_id
  and  b.land_id = d.country_id
  and  d.country_code = e.country_code1
  and  a.statusCode in (1, 2, 3)        -- finally the statuscode selection

answered Jan 3, 2015 at 16:18

Karel Kubat

1,67511 silver badges12 bronze badges

2 Comments

user3185894 Over a year ago

Thanks but won't this create a cross product ? TableA,B,C,D and E are really large tables. Considering this is this still a possible solution ?

Karel Kubat Over a year ago

I can't really comment on the performance if your tables are really big. I usually use this approach because, well, I'm kinda used to it. I haven't yet encountered bad performance (if the indices are right).

Collectives™ on Stack Overflow

SQL Statement to retrieve information from multiple tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related