CODEWITHSUNDEEP
sqlone-to-many
steveo225
steveo225

Reputation: 11882

Many SQL queries vs 1 complex query

I have a database with two tables that are similar to:

table1
------
Id : long, auto increment
Title : string(50)
ParentId : long

table2
------
Id : long, auto increment
FirstName : string(20)
LastName : string(30)
Zip : string(5)

table2 has a one-to-many relationship with table1 where many includes zero.

I also have the following query (that works correctly, so ignore typos an the like, it is an example):

SELECT t1.Id AS tid, t1.Title, t2.Id AS oid, t2.FirstName, t2.LastName
    FROM table t1
    INNER JOIN table2 t2 ON t1.ParentId = t2.Id
    WHERE t2.Id IN
        (SELECT Id FROM table2
         WHERE Zip IN ('zip1', 'zip2', 'etc'))
    ORDER BY t2.Id DESC

The query finds all items in table1 that belong to a person in table2, where the person is in one of the listed zip codes.

The problem I have now is: I want to show all the users (with their items if available) in the listed zip codes, not just the ones with items.

So, I am wondering, should I just do something simple with a lot more queries, like:

SELECT Id AS oid, FirstName, LastName FROM table2 WHERE Zip in ('zip1', 'zip2', 'etc')
foreach(result) {
    SELECT Id AS tid, Title FROM table2 WHERE ParentId = oid
}

Or should I come up with a more elaborate single SQL statement? And if so, can I get a little help? Thanks!

Upvotes: 0

Views: 1763

Answers (7)

cordsen
cordsen

Reputation: 1701

If I understand correctly, I think you are looking for something like this

SELECT t1.Id AS tid, t1.Title, t2.Id AS oid, t2.FirstName, t2.LastName
    FROM table t1
    RIGHT OUTER JOIN table2 t2 ON t1.ParentId = t2.Id AND Zip IN ('zip1', 'zip2', 'etc'))
    ORDER BY t2.Id DESC

You can have multiple conditions on your JOIN and RIGHT OUTER will give you all the rows in table2 even if they don't match in table1

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

What you've described is called an N + 1 query. You have 1 initial query that returns N results, then 1 query for each of your N results. If N is small, the performance difference may not be noticeable - but there will be a larger and larger performance hit as N grows.

Upvotes: 0

Doc Brown
Doc Brown

Reputation: 20044

In general, you should prefer the "many queries" approach if (and only if)

  • it gets you simpler code in total
  • is fast enough (which you should find out by testing)

In this case, I suspect, both conditions may not apply.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40319

I agree with (and have upvoted) @Lee D and @Bueller. However, I generally advocate LEFT OUTER JOINS, because I find it easier to conceptualized what's going on with them, particularly when you are joining three or more tables. Consider it like so:

Start with what you know you want in the final result set:

FROM table2 t2

and then add in the "optional" data.

FROM table2 t2
 left outer join table1 t1
  on t1.ParentId = t2.Id

Whether or not matches are found, whatever gets selected from table2 will always appears.

Upvotes: 1

Bueller
Bueller

Reputation: 2344

Look into Right Joins and Group By. That will most likely get you the query you are after.

Upvotes: 2

Lee D
Lee D

Reputation: 12951

If I understand correctly, changing your INNER JOIN to a RIGHT JOIN should return all users regardless of whether they have an item or not, the item columns will just be null for those that don't.

Upvotes: 2

patapizza
patapizza

Reputation: 2398

You should come up with a more elaborate single SQL statement and then process the results with your favorite programming language.

Upvotes: 0

Related Questions