Joins on many tables

  • Thread starter Thread starter Klaus Hebsgaard
  • Start date Start date
K

Klaus Hebsgaard

Hello

I have one central table in my db, with a lot of related tables (many
to many relations).

I need to do some searches, on the central table and it related tables.
Previously it has been done by pulling everything up in a dataset and
adding a rowfilter.

This could be done because previously the app worked offline by pulling
everything into a dataset, and working on that dataset and in the end
submitting it to the database.

I have now been working on making the app being "more connected"
meaning that searches are being done directly on the db, and the data
being submitted instantly instead of waiting till the app shuts down.

Now as I mentioned i have to do the search in sql somehow.

But I have to do a lot of JOINs, and I am rather new in this area.

So could someone please point me in the right direction, do I need
stored procedures, views or something else?

Thank you in advance.

Klaus Hebsgaard
 
Are you familiar with Joins? Look at SQL Books Online and look up the Join
keyword. There are plenty of examples that use the Pubs and Northwind
databases.

Just get the joins working first. Then you can decide to put them into
stored procedures, or views. We always use stored procedures.

Jeff
 
Hello

Sorry for not making myself more clear, I should have stated in my
first post that I have the following two joins:

SELECT tbl1.*
FROM tbl1 INNER JOIN
tbl2 ON tbl1.ID = tbl2.IDRef
WHERE (tbl2.x = 'some value') AND (tbl1.z= '23')



SELECT tbl1.*
FROM tbl1 INNER JOIN
tbl3 ON tbl1.ID = tbl3.IDRef
WHERE (tbl1.z = '23') AND (tbl3.m= 22)

However these are seperate and i need to be able to combine them and
more of the same in an arbitrary way.
Furthermore the selects (IE.tblDeltager_Status.Status_IDRef = 22) can
vary as well.

I have been considering using the following approach:


CREATE VIEW view1 AS
SELECT tbl1.* FROM tbl1 LEFT OUTER JOIN
tbl3 ON tbl1.ID = tbl3.IDRef
WHERE (tbl1.x = '23') AND (tbl3.m= 22)

SELECT view1.*
FROM view1 INNER JOIN
tbl2 ON view1.ID = tbl2.IDRef
WHERE (tbl2.z = 'some value') AND (view1.m= '23')

DROP VIEW view1

But have read that views can be performance problems.

So I was thinking if it would be better to use stored procedures.

The problem is that i need to do some number of joins and with some
number of selects in these joins.
And I need to make this perform.

So does anyone have some kind of input

Regards

Klaus
 
Back
Top