CODEWITHSUNDEEP
sqloptimizationjoindatabase-cursor
Erratic
Erratic

Reputation: 459

An issue possibly related to Cursor/Join

Here is my situation:

Table one contains a set of data that uses an id for an unique identifier. This table has a one to many relationship with about 6 other tables such that.

Given Table 1 with Id of 001: Table 2 might have 3 rows with foreign key: 001 Table 3 might have 12 rows with foreign key: 001 Table 4 might have 0 rows with foreign key: 001 Table 5 might have 28 rows with foreign key: 001

I need to write a report that lists all of the rows from Table 1 for a specified time frame followed by all of the data contained in the handful of tables that reference it.

My current approach in pseudo code would look like this:

select * from table 1
foreach(result) {
  print result;
  select * from table 2 where id = result.id;
  foreach(result2) {
    print result2;
  }
  select * from table 3 where id = result.id
  foreach(result3) {
    print result3;
  }
  //continued for each table
}

This means that the single report can run in the neighbor hood of 1000 queries. I know this is excessive however my sql-fu is a little weak and I could use some help.

Upvotes: 2

Views: 2160

Answers (8)

Cervo
Cervo

Reputation: 3082

Do all the tables have the same format? If not, then if you have to have a report that can display the n different types of rows. If you are only interested in the same columns then it is easier.

Most databases have some form of dynamic SQL. In that case you can do the following:

create temporary table from
select * from table1 where rows within time frame

x integer
sql varchar(something)
x = 1
while x <= numresults {
 sql = 'SELECT * from table' + CAST(X as varchar) +  ' where id in (select id from temporary table'
 execute sql
 x = x + 1
}

But I mean basically here you are running one query on your main table to get the rows that you need, then running one query for each sub table to get rows that match your main table.

If the report requires the same 2 or 3 columns for each table you could change the select * from tablex to be an insert into and get a single result set at the end...

Upvotes: 0

evilhomer
evilhomer

Reputation: 9274

SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.resultid -- this could be a left join if the table is not guaranteed to have entries for t1.id
INNER JOIN table2 t3 ON t1.id = t3.resultid -- etc

OR if the data is all in the same format you could do.

SELECT cola,colb FROM table1 WHERE id = @id
UNION ALL
SELECT cola,colb FROM table2 WHERE resultid = @id
UNION ALL
SELECT cola,colb FROM table3 WHERE resultid = @id

It really depends on the format you require the data in for output to the report.

If you can give a sample of how you would like the output I could probably help more.

Upvotes: 1

user11318
user11318

Reputation: 9343

What I would do is open up cursors on the following queries:

SELECT * from table1 order by id
SELECT * from table1 r, table2 t where t.table1_id = r.id order by r.id
SELECT * from table1 r, table3 t where t.table1_id = r.id order by r.id

And then I would walk those cursors in parallel, printing your results. You can do this because all appear in the same order. (Note that I would suggest that while the primary ID for table1 might be named id, it won't have that name in the other tables.)

Upvotes: 0

Amy B
Amy B

Reputation: 110191

Join doesn't do it for me. I hate having to de-tangle the data on the client side. All those nulls from left-joining.

Here's a set-based solution that doesn't use Joins.

INSERT INTO @LocalCollection (theKey)
SELECT id
FROM Table1
WHERE ...


SELECT * FROM Table1 WHERE id in (SELECT theKey FROM @LocalCollection)

SELECT * FROM Table2 WHERE id in (SELECT theKey FROM @LocalCollection)

SELECT * FROM Table3 WHERE id in (SELECT theKey FROM @LocalCollection)

SELECT * FROM Table4 WHERE id in (SELECT theKey FROM @LocalCollection)

SELECT * FROM Table5 WHERE id in (SELECT theKey FROM @LocalCollection)

Upvotes: 2

Josh Bush
Josh Bush

Reputation: 2718

Join all of the tables together.

select * from table_1 left join table_2 using(id) left join table_3 using(id);

Then, you'll want to roll up the columns in code to format your report as you see fit.

Upvotes: 0

Grant Johnson
Grant Johnson

Reputation: 1244

You may be best off to use a reporting tool like Crystal or Jasper, or even XSL-FO if you are feeling bold. They have things built in to handle specifically this. This is not something the would work well in raw SQL.

If the format of all of the rows (the headers as well as all of the details) is the same, it would also be pretty easy to do it as a stored procedure.

What I would do: Do it as a join, so you will have the header data on every row, then use a reporting tool to do the grouping.

Upvotes: 1

Tom Ritter
Tom Ritter

Reputation: 101400

Ah! Procedural! My SQL would look like this, if you needed to order the results from the other tables after the results from the first table.

Insert Into #rows Select id from Table1 where date between '12/30' and '12/31'
Select * from Table1 t join #rows r on t.id = r.id
Select * from Table2 t join #rows r on t.id = r.id
--etc

If you wanted to group the results by the initial ID, use a Left Outer Join, as mentioned previously.

Upvotes: 1

Forgotten Semicolon
Forgotten Semicolon

Reputation: 14120

LEFT OUTER JOIN Tables2-N on Table1

SELECT Table1.*, Table2.*, Table3.*, Table4.*, Table5.*
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.ID = Table2.ID
LEFT OUTER JOIN Table3 ON Table1.ID = Table3.ID
LEFT OUTER JOIN Table4 ON Table1.ID = Table4.ID
LEFT OUTER JOIN Table5 ON Table1.ID = Table5.ID
WHERE (CRITERIA)

Upvotes: 3

Related Questions