Joins on many tables

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

Jeff
Fri May 05 10:46:21 CDT 2006

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

"Klaus Hebsgaard" <khebbie@gmail.com> wrote in message
news:1146840131.061710.182230@i40g2000cwc.googlegroups.com...
> 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
>

Klaus
Sat May 06 00:44:02 CDT 2006

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

Jeff
Sun May 07 11:13:28 CDT 2006

SQL UNION

"Klaus Hebsgaard" <khebbie@gmail.com> wrote in message
news:1146894242.715396.73890@j73g2000cwa.googlegroups.com...
> 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
>