I need to write a SQL statement with a sub-query and multiple inner joins

Question

I need to join multiple tables for the data I want. However, two fields exists on separate tables that, when the query is executed, returns a cartesian product, grossly inflating the actual count of my data. I need a different column from two different tables. I can write two separate queries and then extract the data to Excel and manipulate it there (which I've done) but there's got to be a more efficient way.

Can I write a sub-query to get around this? If so, where would I write the sub-query?

    SELECT a.field
          ,b.field
          ,c.field
          ,d.field
    FROM table a
    INNER JOIN table b
    ON...
    INNER JOIN table c 
    ON...
    INNER join table d
    ON... 
    WHERE...

The above query results in a cartesian product. I am using SQUIRREL.

Thanks!

Table structure, sample data and expected results would be helpful. — sgeddes, Aug 10 '16 at 15:14
you can subquery in this way: SELECT a.field, b = (SELECT b.field from b where b.JOINFIELD = a.JOINFIELD) FROM a — AlexT82, Aug 10 '16 at 15:21
@sgeddes [Here's the best I can do super quick](http://imgur.com/AHjvPof) — firstinten, Aug 10 '16 at 15:21
Squirrel is only a SQL client application that can connect to many different DBMS. Which database are you using? Postgres? Oracle? — , Apr 19 '17 at 07:42

score 0 · Answer 1 · answered Aug 10 '16 at 15:30

Missing criteria?

You are joining related a, b, c and d records and show them. There seems nothing wrong about that. If you get records multiplied, this may be because you forgot criteria in your ON clauses. Let's say table d is related to tables a and b, then you need ON d.id_a = a.id and d.id_b = b.id. Forgetting one of the two criteria leads to data multiplication.

Unrelated data?

Another possiblity is that two tables are not related. Say table c and d are related to table a and hold several records with detail data, but they are not related to each other. Then your query would list all combinations of c and d per a (e.g. a product bought from several suppliers and sold in several stores; your query would combine all suppliers that sell product X with all stores that sell product X). In that case you would have to think about what your query shall really show - or whether you want two separate queries instead maybe.

I need to write a SQL statement with a sub-query and multiple inner joins

1 Answers1