CODEWITHSUNDEEP
mysqlsqlselectjoin
user3185894
user3185894

Reputation: 37

SQL Statement to retrieve information from multiple tables

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

TABLE B

TABLE C

TABLE D

TABLE E

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)

Upvotes: 1

Views: 190

Answers (2)

terary
terary

Reputation: 1098

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'.

Upvotes: 1

Karel Kubat
Karel Kubat

Reputation: 1675

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

Upvotes: 0

Related Questions