Help with returning a certain # of records from a view.

I have a view that will return say 5000 records when I do a simple
select query on that view like.

select *
from vw_test_view

How can I set up my query to only return a certain # of records, say
the first 300?

Here is what is going on, we have a large amount of data that returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. So my thoughts were as follows:

1. To run a query to return X amount of the total data for us to work
with.
2. Update these records with a flag in a table that the vw_test_view
filters out.
3. The next time I run the query to pull data from the view it will
skip the records that I have already looked at (because of step 2) and
pull the next X amount of records.

Thanks in advance,
MikeOn 24 Jun 2004 08:43:30 -0700, Mike wrote:

>I have a view that will return say 5000 records when I do a simple
>select query on that view like.
>select *
>from vw_test_view
>
>How can I set up my query to only return a certain # of records, say
>the first 300?
>
>Here is what is going on, we have a large amount of data that returns
>in a view and we need to work with all of it eventually, However we
>want to do it in chunks. So my thoughts were as follows:
>1. To run a query to return X amount of the total data for us to work
>with.
>2. Update these records with a flag in a table that the vw_test_view
>filters out.
>3. The next time I run the query to pull data from the view it will
>skip the records that I have already looked at (because of step 2) and
>pull the next X amount of records.
>Thanks in advance,
>Mike

Hi Mike,

You could use the TOP clause of the SELECT statement:

SELECT TOP 300 Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....

Without the order by, you'll still get maximum 300 rows, but there's no
way predicting which 300 out of the total number of matching rows will be
selected. With the ORDER BY, you'll get the first 300 according to the
specified sort order.

An alternative is to use SET ROWCOUNT:

SET ROWCOUNT 300
SELECT Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....
SET ROWCOUNT 0-- restored default behaviour

The SET ROWCOUNT gives the maximum number of rows to affect for all future
commands from the same connection. Note that this applies to UPDATE and
DELETE as well!! To return to the default behaviour of affecting all rows,
use SET ROWCOUNT 0 or close and re-open the connection.

Note that both methods use proprietary Transact-SQL syntax. An ANSI
standard version can only be done with a specified order (you'll have to
specify by which order you want the 300 "first" rows) and requires a
correlated subquery. It will be much slower.

SELECT Column1, Column2
FROM MyView AS a
WHERE ....-- if necessary
AND (SELECT COUNT(*)
FROM MyView AS b
WHERE ....-- same as in outer join
AND b.OrderingColumn < a.OrderingColumn)
< 300
ORDER BY OrderingColumn-- may be omitted

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I set up my query to only return a certain # of records
[sic], say
the first 300? <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

You will have to get out the RDBMS world and use a cursor of some
kind.

>> Here is what is going on, we have a large amount of data that
returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. <<

1) A mere 5000 rows is not a lot of data.

2) The idea of "doing it in chunks" is dangerous; do you know anything
about transactions, isolation levels and shared data?|||
Sometimes it benefits programmers to get out of in front of their
screens for a while and see how what they do affects end users.
Unfortunately too many of them do not take the time to do this or to try
and understand things from an end users point of view. No 5000 rows is
not a lot of data from a programmers point of view, but from a user who
has to go through this and verify certain information this can seem like
a daunting task, if you can break it down either feed it to them slowly
or split it amongst several people it becomes much more manageable for
them. This by the way is not what I am trying to accomplish, nor is
5000 the # of rows that I have of total data or 300 how many that I want
to pull out at a time. All that this is are made-up scenarios to
illustrate the type of things that I am trying to accomplish.

If you want to crucify me with semantics go ahead. It doesn't matter,
all that does is that people understand my question and through their
generosity point me in the right direction.

Hugo, thanks again for the help this will give me what I need to get the
job done.
And I already have the view using an order by clause on the data and it
returns exactly what I need, so if I add in the top clause it should
give me exactly what I need.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Help with Query Strings

I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @.PROPERITY_ID)"><SelectParameters><asp:QueryStringParameterName="PROPERITY_ID"QueryStringField="key"Type="Int32"/></SelectParameters></asp:SqlDataSource>

Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string

Dimy as string ="'" // " ' "

key = Request.QueryString("key").trim(y.tochararray)

But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?

string

y ="'";

key = Request.QueryString[

"key"].trim(y.tochararray());

If I understand you correctly, you have 5 in database but while you select it, you get '5' correct?

SqlDataSource doesn't put any thing while it gets data. And binding itto Gridview should not be a problem

|||

I am codeing the value of my querystring in depending on a key value in another forms gridview so my code for a query string is

dim key as string 'holds the key value of the selected row in a dataview

response.redirect("Info.aspx?id=' " & key & " ' ")

when i do that, if key is 5 it places a single quote infort and behind five, '5'

So when i have my select statment in my sqldatasource and the paramater is equal to query string id it has the single quote in the select command sent to the database and that produces an erro. How can i take out the " ' " in the query sting in my aspx file ?

|||

Hi draskc03

This is my example. You can try this

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="NewsID" Width ="100%"
DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None" HorizontalAlign="Center" PageSize="20">
<Columns>
<asp:TemplateField SortExpression="ImageUrl" HeaderText="Edit"><ItemTemplate>
<a href ="AddEditNews.aspx?ID=<%#Eval("NewsID") %>"> <img src ="../images/Edit.gif" border ="0"/></a>

</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ReadOnly="True" DataField="NewsID" InsertVisible="False" Visible="False" SortExpression="NewsID" HeaderText="ID"></asp:BoundField>
<asp:BoundField DataField="AddedDate" SortExpression="AddedDate" HeaderText="AddedDate"></asp:BoundField>
<asp:TemplateField SortExpression="ImageUrl" HeaderText="Title"><ItemTemplate>
<a href ="AddEditNews.aspx?ID=<%#Eval("NewsID") %>"> <%#Eval("Title") %></a>

</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Description" Visible="False" SortExpression="Description" HeaderText="Description"></asp:BoundField>
<asp:BoundField DataField="Body" Visible="False" SortExpression="Body" HeaderText="Body"></asp:BoundField>
<asp:BoundField DataField="ImageUrl" Visible="False" SortExpression="ImageUrl" HeaderText="ImageUrl"></asp:BoundField>
<asp:BoundField DataField="language" SortExpression="language" HeaderText="Language"></asp:BoundField>
<asp:CommandField ShowDeleteButton="True" DeleteText="Xóa" DeleteImageUrl="~/images/Delete.gif" ButtonType="Image" HeaderText="Xoá"></asp:CommandField>
</Columns>

</asp:GridView>

Help with query -SQL Express and ASP.net

I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.

@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.

My tables look similar to this:

Company TblComodity TblRegion

PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc

What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear. My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display - like its only filltering on commodity name. Can anyone help ? I can provide more info if needed

Here is my query:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)More info needed: Are the ticks mapped to the appropiate parameters like @.sw ? What do you pass if the ticks are not selected ? Probably Null ? because of doing an OR, you will get all values which habe in one of the filtered columns the value null then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with query NOT IN

I have a view containing column X and column Y and a foreign key F. I
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:

> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com

Help with query

I am looking for some suggestions as I am a little stuck trying a create a
view that will give me the correct results. Basically I need to produce a
view where document amounts do not balance to 0 and to do this I am using
the ApplyTo column. It also needs to calculate the remaining balance of a
document
CREATE TABLE [dbo].[Invoices_Payments] (
[type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocNo] [int] NULL ,
[ApplyTo] [int] NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,50)
Using the above example, the query should return the following results
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Any help would be much appreciated.
ThanksIt seems that you want to display only one document for each set that
does not balance to zero. That's not difficult but I'm not clear just
which document of the set you want to display. For example, the
following will return the highest numbered doc in each case:
SELECT type, docno, applyto, amount
FROM dbo.Invoices_Payments AS P
WHERE docno = (SELECT MAX(docno)
FROM dbo.Invoices_Payments
WHERE applyto = P.applyto
HAVING SUM(amount)<>0)
Also, what is the primary key? Apparently you don't have one. Shouldn't
ApplyTo be declared as a foreign key?
--
David Portas
SQL Server MVP
--|||I think this may be closer to what you wanted, although perhaps there
was a typo in your data: Credit: 50 instead of Credit: -50.
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
David Portas
SQL Server MVP
--|||David
Thanks for the suggestion, I am going to run it to see if it works with the
data I have here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David this has really helped. I just have another question for you..
is it possible to exclude the grouping on the apply to number when it equals
0. For example
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,-50)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1018,0,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Payment',1019,0,-65)
Returns the following records
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Credit, 1018, 0 -60
Payment, 1019,0 -65
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>Thanks David this has really helped. I just have another question for you..
>is it possible to exclude the grouping on the apply to number when it equals
>0. For example
(snip)
Hi Sarah,
You could adapt David's code, like this:
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
Thanks.. can I get this working in a view? Apparently you can't use UNION
in a view?
Is there anyway around this?
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lp1j619m1rf6grkapt32uj177d1lqeetju@.4ax.com...
> On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>>Thanks David this has really helped. I just have another question for
>>you..
>>is it possible to exclude the grouping on the apply to number when it
>>equals
>>0. For example
> (snip)
> Hi Sarah,
> You could adapt David's code, like this:
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes, you can use UNION in a view. Maybe it's just that the GUI you are
using prevents you doing this? Try creating the view in Query Analyzer:
CREATE VIEW foo
AS
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
If you mean that you can't create an INDEXED view then you are correct.
I don't think it will be possible to create an indexed view for this
because you can't avoid a self-join or subquery.
--
David Portas
SQL Server MVP
--|||Thank a million.. I never thought to try creating the view in the analyzer.
It works a treat!
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114420218.812239.309580@.z14g2000cwz.googlegroups.com...
> Yes, you can use UNION in a view. Maybe it's just that the GUI you are
> using prevents you doing this? Try creating the view in Query Analyzer:
> CREATE VIEW foo
> AS
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
> If you mean that you can't create an INDEXED view then you are correct.
> I don't think it will be possible to create an indexed view for this
> because you can't avoid a self-join or subquery.
> --
> David Portas
> SQL Server MVP
> --
>

Help with most efficient column sorting technique

I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.
Thanks,
MattMJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
Huh'
Have u tried the ORDER BY clause?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||MJB skrev:

> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
> Thanks,
> Matt
I don't think there is a silver bullet for this, if the table is
updated also I guess you have to prioritize between the columns and add
indexes on the ones that must be sorted fast.
Hopefully someone else knows better.
/impslayer, aka Birger Johansson|||Have you ever tried an ORDER BY on a non-index column that has 4 million
plus rows... I can tell you it ain't fast...
MGFoster wrote:
> MJB wrote:
> Huh'
> Have u tried the ORDER BY clause?|||Just so I understand, you're exposing 4 million + rows of data in a
paged data grid, and your users want t obe able to sort this data?
My first question is: do they really need to see all 4 million rows of
data? How in the hell is that practical?
Stu|||Well, it's a database that stores Ethernet traffic information. Kinda
what you might get out of Snort or something similar. In most cases the
data will be filtered a bit, but in some cases they may want to "see all
the data" (in the sense that it's paged and globally sortable). The
problem is they want to be able to sort on the ip col, the port col,
date time stamps etc (like i said there are 35+ cols in this table).
Currently only the primary key col is indexed, but it doesn't make sense
to index all of the others. Was wondering if anyone had dealt with this
before - doesn't sound like it.
Stu wrote:
> Just so I understand, you're exposing 4 million + rows of data in a
> paged data grid, and your users want t obe able to sort this data?
> My first question is: do they really need to see all 4 million rows of
> data? How in the hell is that practical?
> Stu
>
MJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.|||Actually, the company I work for manages network appliances for our
customers; the way we address the issue is bring the data off the
server onto the client pc, and let them sort it on their own pc (using
ADO.NET dataasets). We warn them if the data is going to be larger
than a few thousand rows, so they can decline, and only sample the
appropriate amount.
The largest collection of related events processed so far has been
about 200,000; it took about 3 minutes to load to the client's pc, and
then they had to deal with sorting issues. At least that way, the
entire server was not bogged down for one incident.
HTH,
Stu|||Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;

Help with most efficient column sorting technique

I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.
Thanks,
Matt
MJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
Huh?
Have u tried the ORDER BY clause?
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
|||MJB skrev:

> I have a SQL 2005 database with 4 million+ rows. One table in
> particular has about 35 columns. I have implemented a paged data grid
> results view for them. They want to be able to sort on the majority of
> the columns. When they sort they want all the results sorted not just
> the visible result set, but it's not practical for me to index every
> column either. There has to be a way to achieve my sorting goals. Has
> anyone dealt with this issue and solved it reasonably well.
> Thanks,
> Matt
I don't think there is a silver bullet for this, if the table is
updated also I guess you have to prioritize between the columns and add
indexes on the ones that must be sorted fast.
Hopefully someone else knows better.
/impslayer, aka Birger Johansson
|||Have you ever tried an ORDER BY on a non-index column that has 4 million
plus rows... I can tell you it ain't fast...
MGFoster wrote:
> MJB wrote:
> Huh?
> Have u tried the ORDER BY clause?
|||Just so I understand, you're exposing 4 million + rows of data in a
paged data grid, and your users want t obe able to sort this data?
My first question is: do they really need to see all 4 million rows of
data? How in the hell is that practical?
Stu
|||Well, it's a database that stores Ethernet traffic information. Kinda
what you might get out of Snort or something similar. In most cases the
data will be filtered a bit, but in some cases they may want to "see all
the data" (in the sense that it's paged and globally sortable). The
problem is they want to be able to sort on the ip col, the port col,
date time stamps etc (like i said there are 35+ cols in this table).
Currently only the primary key col is indexed, but it doesn't make sense
to index all of the others. Was wondering if anyone had dealt with this
before - doesn't sound like it.
Stu wrote:
> Just so I understand, you're exposing 4 million + rows of data in a
> paged data grid, and your users want t obe able to sort this data?
> My first question is: do they really need to see all 4 million rows of
> data? How in the hell is that practical?
> Stu
>
MJB wrote:
> I have a SQL 2005 database with 4 million+ rows. One table in
particular has about 35 columns. I have implemented a paged data grid
results view for them. They want to be able to sort on the majority of
the columns. When they sort they want all the results sorted not just
the visible result set, but it's not practical for me to index every
column either. There has to be a way to achieve my sorting goals. Has
anyone dealt with this issue and solved it reasonably well.
|||Actually, the company I work for manages network appliances for our
customers; the way we address the issue is bring the data off the
server onto the client pc, and let them sort it on their own pc (using
ADO.NET dataasets). We warn them if the data is going to be larger
than a few thousand rows, so they can decline, and only sample the
appropriate amount.
The largest collection of related events processed so far has been
about 200,000; it took about 3 minutes to load to the client's pc, and
then they had to deal with sorting issues. At least that way, the
entire server was not bogged down for one incident.
HTH,
Stu
|||Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
...
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
.. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;

Help with missing data in query

Hello and thanks for your efforts,
I have a table with Part, MonthSold, ItemsSold
i need to generate a view comparing this years sales to lastyears sales and
their differences by month.
this was my first shot at it:
SELECT DATENAME(month, inv_Monthly_Sales.MonthSold) AS Month,
SUM(inv_Monthly_Sales.ItemsSold) AS ThisYear, SUM(yr2.ItemsSold) AS LastYear,
SUM(inv_Monthly_Sales.ItemsSold - yr2.ItemsSold) AS
Comparison
FROM inv_Monthly_Sales INNER JOIN
inv_Monthly_Sales AS yr2 ON inv_Monthly_Sales.Part
= yr2.Part AND MONTH(inv_Monthly_Sales.MonthSold) = MONTH(yr2.MonthSold)
WHERE (YEAR(inv_Monthly_Sales.MonthSold) = @.Yr) AND
(inv_Monthly_Sales.Part = @.Part) AND (YEAR(yr2.MonthSold) = @.Yr - 1) AND
(yr2.Part = @.Part)
GROUP BY DATENAME(month, inv_Monthly_Sales.MonthSold),
MONTH(inv_Monthly_Sales.MonthSold), MONTH(yr2.MonthSold)
ORDER BY MONTH(inv_Monthly_Sales.MonthSold)
this works great if there exists data for all 12 months of both years.
if any month is missing on any year i get back nothing.
how can i make it generate the missing columns if there is no data for that
month
i.e. during march and april no gizmos were sold so there won't be a record
any sale for that month. i need a 0 placed in that column if it didn't exist.
i tried using isnull on the sum but it didn't help
please enlighten me if you can.
On Tue, 14 Aug 2007 20:08:01 -0700, SLIMSHIM wrote:

>Hello and thanks for your efforts,
>I have a table with Part, MonthSold, ItemsSold
>i need to generate a view comparing this years sales to lastyears sales and
>their differences by month.
>this was my first shot at it:
(snip)
>this works great if there exists data for all 12 months of both years.
>if any month is missing on any year i get back nothing.
>how can i make it generate the missing columns if there is no data for that
>month
Hi slimshim,
You'll have to use a seperate table with all 12 months in it. You can
either create it on the fly as a derived table, or create a permanent
table in your DB as a one-time operation. In the query below, I presume
the latter; the query expects a table dbo.Months, with at least the two
columns MonthNo and MonthName.
SELECT m.MonthName AS MONTH,
SUM(yr.ItemsSold) AS ThisYear,
SUM(yr2.ItemsSold) AS LastYear,
SUM(yr.ItemsSold - yr2.ItemsSold) AS Comparison
FROM dbo.Months AS m
LEFT JOIN inv_Monthly_Sales AS yr
ON yr.Part = @.Part
AND YEAR(yr.MonthSold) = @.Yr
AND MONTH(yr.MonthSold) = m.MonthNo
LEFT JOIN inv_Monthly_Sales AS yr2
ON yr2.Part = @.Part
AND YEAR(yr2.MonthSold) = @.Yr - 1
AND MONTH(yr2.MonthSold) = m.MonthNo
GROUP BY m.MonthNo, m.MonthName
ORDER BY m.MonthNo;
Note: If your inv_Month_Sales table is indexed on the MonthSold column,
you should rewrite the date selection to the form MonthSold >= (first
day of month) AND MonthSold < (first day of next month). Let me know if
you need help with that.
Yet another note - the query is untested. Please see www.aspfaq.com/5006
if you prefer a tested reply, or if you want to post followup questions.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" wrote:
[vbcol=seagreen]
> On Tue, 14 Aug 2007 20:08:01 -0700, SLIMSHIM wrote:
SELECT COALESCE (m.MonthName, 'Total') AS MONTH,
ISNULL(SUM(yr.ItemsSold), 0) AS ThisYear, ISNULL(SUM(yr2.ItemsSold), 0) AS
LastYear,
SUM(ISNULL(yr.ItemsSold, 0) - ISNULL(yr2.ItemsSold,
0)) AS Comparison
FROM (SELECT 1 AS monthId, 'Jan' AS MonthName
UNION ALL
SELECT 2 AS Expr1, 'Feb' AS Expr2
UNION ALL
SELECT 3 AS Expr1, 'Mar' AS Expr2
UNION ALL
SELECT 4 AS Expr1, 'Apr' AS Expr2
UNION ALL
SELECT 5 AS Expr1, 'May' AS Expr2
UNION ALL
SELECT 6 AS Expr1, 'Jun' AS Expr2
UNION ALL
SELECT 7 AS Expr1, 'Jul' AS Expr2
UNION ALL
SELECT 8 AS Expr1, 'Aug' AS Expr2
UNION ALL
SELECT 9 AS Expr1, 'Sep' AS Expr2
UNION ALL
SELECT 10 AS Expr1, 'Oct' AS Expr2
UNION ALL
SELECT 11 AS Expr1, 'Nov' AS Expr2
UNION ALL
SELECT 12 AS Expr1, 'Dec' AS Expr2) AS m
LEFT OUTER JOIN
inv_Monthly_Sales AS yr ON yr.Part = @.Part AND
YEAR(yr.MonthSold) = @.Yr AND MONTH(yr.MonthSold) = m.monthId LEFT OUTER JOIN
inv_Monthly_Sales AS yr2 ON yr2.Part = @.Part AND
YEAR(yr2.MonthSold) = @.Yr - 1 AND MONTH(yr2.MonthSold) = m.monthId
GROUP BY m.MonthName WITH ROLLUP
ORDER BY Month
thanx in advance
> (snip)
> Hi slimshim,
> You'll have to use a seperate table with all 12 months in it. You can
> either create it on the fly as a derived table, or create a permanent
> table in your DB as a one-time operation. In the query below, I presume
> the latter; the query expects a table dbo.Months, with at least the two
> columns MonthNo and MonthName.
> SELECT m.MonthName AS MONTH,
> SUM(yr.ItemsSold) AS ThisYear,
> SUM(yr2.ItemsSold) AS LastYear,
> SUM(yr.ItemsSold - yr2.ItemsSold) AS Comparison
> FROM dbo.Months AS m
> LEFT JOIN inv_Monthly_Sales AS yr
> ON yr.Part = @.Part
> AND YEAR(yr.MonthSold) = @.Yr
> AND MONTH(yr.MonthSold) = m.MonthNo
> LEFT JOIN inv_Monthly_Sales AS yr2
> ON yr2.Part = @.Part
> AND YEAR(yr2.MonthSold) = @.Yr - 1
> AND MONTH(yr2.MonthSold) = m.MonthNo
> GROUP BY m.MonthNo, m.MonthName
> ORDER BY m.MonthNo;
> Note: If your inv_Month_Sales table is indexed on the MonthSold column,
> you should rewrite the date selection to the form MonthSold >= (first
> day of month) AND MonthSold < (first day of next month). Let me know if
> you need help with that.
> Yet another note - the query is untested. Please see www.aspfaq.com/5006
> if you prefer a tested reply, or if you want to post followup questions.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
> You did a great job . thank you.
i"m including my final code it has one bug i can't get the sort order
straight
i.e. jan feb mar ......Total
it comes in alphabeticaly
|||On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:

>i"m including my final code it has one bug i can't get the sort order
>straight
>i.e. jan feb mar ......Total
>it comes in alphabeticaly
Hi slimshim,
That's because you asked it to order on the month name column :-)
Change the last par tof the query to
GROUP BY m.MonthName WITH ROLLUP
ORDER BY MIN(m.MonthId)
I was first about to suggest to include MonthId in the GROUP BY, but I'm
not sure if the WITH ROLLUP option likes that. The workaround I chose is
to use an aggregate function for the ORDER BY.
If that doesn't work (you didn't follow the instructions I linked to
that would have enabled me to test before posting), then change the end
to
GROUP BY m.MonthID WITH ROLLUP
ORDER BY m.MonthID
and change the first line to read
SELECT MAX(COALESCE(m.MonthName, 'Total')) AS MONTH,
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" wrote:

> On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:
>
> Hi slimshim,
> That's because you asked it to order on the month name column :-)
> Change the last par tof the query to
> GROUP BY m.MonthName WITH ROLLUP
> ORDER BY MIN(m.MonthId)
> I was first about to suggest to include MonthId in the GROUP BY, but I'm
> not sure if the WITH ROLLUP option likes that. The workaround I chose is
> to use an aggregate function for the ORDER BY.
>
> If that doesn't work (you didn't follow the instructions I linked to
> that would have enabled me to test before posting), then change the end
> to
> GROUP BY m.MonthID WITH ROLLUP
> ORDER BY m.MonthID
> and change the first line to read
> SELECT MAX(COALESCE(m.MonthName, 'Total')) AS MONTH,
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
THanx for the help
I did go to that site But I couldn't figure out how to upload anything.
after i tried your suggested changed this is what my result looks like
Jan000
Total12012
Feb000
Mar000
Apr000
May000
Jun000
Jul12012
Aug000
Sep000
Oct000
Nov000
Dec000
Total falls under jan not dec as it should.
I appreciate all your help.
I was wondering if I could use COMPUTE intead of coalese to get the total
heading to the bottom ?
THanx again
SLIMSHIM
|||On Thu, 16 Aug 2007 19:38:04 -0700, SLIMSHIM wrote:

>THanx for the help
>I did go to that site But I couldn't figure out how to upload anything.
Hi slimshim,
You don't need to upload anything. The site describes the information
you need to supply to give people the best chance to help you. You just
read that site, assemble the information, then post that information in
your next question.

>after i tried your suggested changed this is what my result looks like
>Jan000
>Total12012
>Feb000
>Mar000
>Apr000
>May000
>Jun000
>Jul12012
>Aug000
>Sep000
>Oct000
>Nov000
>Dec000
I'm surpried - for a quick test on some scratch data, I got the total as
the very first line.
Try changing the ORDER BY clause to read either
GROUP BY m.MonthName WITH ROLLUP
ORDER BY GROUPING(m.MonthName), MIN(m.MonthId)
or
GROUP BY m.MonthID WITH ROLLUP
ORDER BY GROUPING(m.MonthID), m.MonthID
depending on which version of the query you are now using.

>I was wondering if I could use COMPUTE intead of coalese to get the total
>heading to the bottom ?
COMPUTE is a deprecated feature and will be removed in a future version
of SQL Server. Don't use it for new work, and replace it if you have it
in existing code.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Help with Many-to-Many-to-Many Problem

I am having a problem creating a many-to-many-to-many type relationship. It
works fine, but when I create a view to query it and test it, it does not
generate the results I expected.
Below if the DDL for the tables and the SQL for the view.
Any help would be most appreciated.
Many thanks in advance.
Regards
Keith
DIAGRAM 5: SYS_Relationship_Individuals_Courses
(http://www.step-online.org.uk/diagram5.png)
This is the relationship I am having a problem with. Each individual can
attend many courses. I have tried to model this by creating this diagram.
It has the following tables in it. SYS_Individual (to show individuals).
To show courses (which already have a many-to-many relationship
(http://www.step-online.org.uk/diagram2.png), I added all the same tables as
in diagram 2 - SYS_Courses, SYS_Courses_Venues, SYS_Courses_TimeTable,
SYS_Courses_Tutors (joined using SYS_Xref_Join_Courses). Now as each
individual could attend many courses, I assumed that the correct way to
model this would be by creating another many-to-many between the
SYS_Individual table and the SYS_Xref_Join_Courses by using a new join table
(SYS_Xref_Join_Ind_Courses).
I tried to check this works using a view (SYS_Individual_Courses_List) -
VIEW 5 below.
The problem is that no matter how many entries I put in the
SYS_Xref_Join_Ind_Courses, the test view (VIEW 5 below), only ever shows one
record. While playing around with the view, I got it to show all the
entries, but they were duplicated 4 times each! I can't remember how I did
this now either.
Now for the DDL for the tables:
CREATE TABLE [do].[SYS_Individual] (
[IND_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[IND_Date_Entered] [datetime] NOT NULL ,
[IND_Date_on_Project] [datetime] NULL ,
[IND_First_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Surname] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Address] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Post_Code] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Telephone_Home] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Telephone_Mobile] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Telephone_Other] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Email_Address] [varchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Date_Started] [datetime] NULL ,
[IND_Trading_Name] [varchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Description_Proposed] [text] COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_Profile] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_DD_Economic_Activity_ID] [numeric](18, 0) NULL ,
[IND_DD_Referal_Source_Code] [numeric](18, 0) NULL ,
[IND_DD_Training_Status_ID] [numeric](18, 0) NULL ,
[IND_DD_Age_ID] [numeric](18, 0) NULL ,
[IND_DD_Potential_Business_Type_ID] [numeric](18, 0) NULL ,
[IND_DD_Exit_ID] [numeric](18, 0) NULL ,
[IND_DD_Disadvantage_ID] [numeric](18, 0) NULL ,
[IND_DD_Bank_ID] [numeric](18, 0) NULL ,
[IND_DD_Start_Up_Confirmation_ID] [numeric](18, 0) NULL ,
[IND_DD_Growth_Potential_ID] [numeric](18, 0) NULL ,
[IND_DD_Ethnicity_ID] [numeric](18, 0) NULL ,
[IND_DD_Marital_Status_ID] [numeric](18, 0) NULL ,
[IND_DD_Gender_ID] [numeric](18, 0) NULL ,
[IND_DD_Status_ID] [numeric](18, 0) NULL ,
[IND_Related_To_Another] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_DD_Business_Status_ID] [numeric](18, 0) NULL ,
[IND_Last_Updated] [datetime] NULL
CREATE TABLE [dbo].[SYS_Courses] (
[COURSE_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[COURSE_Date_Entered] [datetime] NOT NULL ,
[COURSE_Title] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COURSE_Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[COURSE_Last_Modified] [datetime] NULL
CREATE TABLE [dbo].[SYS_Courses_Venues] (
[COURSE_VEN_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[COURSE_VEN_Date_Entered] [datetime] NOT NULL ,
[COURSE_VEN_Address] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[COURSE_VEN_Last_Updated] [datetime] NULL
CREATE TABLE [dbo].[SYS_Courses_TimeTable] (
[COURSE_TT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[COURSE_TT_Date_Entered] [datetime] NOT NULL ,
[COURSE_TT_Date] [datetime] NOT NULL
CREATE TABLE [dbo].[SYS_Courses_Tutors] (
[COURSE_TUT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[COURSE_TUT_Date_Entered] [datetime] NOT NULL ,
[COURSE_TUT_First_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COURSE_TUT_Surname] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COURSE_TUT_Last_Updated] [datetime] NULL
CREATE TABLE [dbo].[SYS_Xref_Join_Courses] (
[XREF_Courses_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[XREF_Courses_Date_Entered] [datetime] NOT NULL ,
[COURSE_ID] [numeric](18, 0) NOT NULL ,
[COURSE_VEN_ID] [numeric](18, 0) NOT NULL ,
[COURSE_TT_ID] [numeric](18, 0) NOT NULL ,
[COURSE_TUT_ID] [numeric](18, 0) NOT NULL ,
[XREF_Courses_Last_Updated] [datetime] NULL
CREATE TABLE [dbo].[SYS_Xref_Join_Ind_Courses] (
[XREF_Ind_Course_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[XREF_Ind_Course_Date_Entered] [datetime] NOT NULL ,
[IND_ID] [numeric](18, 0) NOT NULL ,
[XREF_Courses_ID] [numeric](18, 0) NOT NULL ,
[XREF_Ind_Course_Last_Updated] [datetime] NULL
Now for the View:
VIEW 5:
CREATE VIEW dbo.SYS_Individual_Course_List
AS
SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title,
dbo.SYS_Courses_Venues.COURSE_VEN_Address,
dbo.SYS_Xref_Join_Ind_Courses.XREF_Ind_Course_ID
FROM dbo.SYS_Courses_Venues INNER JOIN
dbo.SYS_Courses INNER JOIN
dbo.SYS_Xref_Join_Ind_Courses INNER JOIN
dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID INNER JOIN
dbo.SYS_Xref_Join_Courses ON
dbo.SYS_Xref_Join_Ind_Courses.XREF_Ind_Course_ID =
dbo.SYS_Xref_Join_Courses.XREF_Courses_ID ON
dbo.SYS_Courses.COURSE_ID =
dbo.SYS_Xref_Join_Courses.COURSE_ID INNER JOIN
dbo.SYS_Courses_TimeTable ON
dbo.SYS_Xref_Join_Courses.COURSE_TT_ID =
dbo.SYS_Courses_TimeTable.COURSE_TT_ID INNER JOIN
dbo.SYS_Courses_Tutors ON
dbo.SYS_Xref_Join_Courses.COURSE_TUT_ID =
dbo.SYS_Courses_Tutors.COURSE_TUT_ID ON
dbo.SYS_Courses_Venues.COURSE_VEN_ID =
dbo.SYS_Xref_Join_Courses.COURSE_VEN_ID
Keith et al:

> I am having a problem creating a many-to-many-to-many type relationship.
It
In a single word: `No shit'. This is by design.

> I am having a problem creating a many-to-many-to-many type relationship.
It
> works fine, but when I create a view to query it and test it, it does
not
> generate the results I expected.
You should not have expected it to work in the First Place.
If you need to create a many-to-many relationship between relations A and
B, then we create a third relation C.
Intersect relations A and B with C (that is C accepts foreign keys from
relations A and B).
Since relation C should only contain the foreign key attributes from
relations A and B you should be able to figure out how to work with it.
Do not include DDL/source-code in usenet posts. People will try to
correct your code instead of your broken logic. For best results do not
even mention your `specific implementation'.
Have fun with your new `toy',
Timothy J. Bruce
uniblab@.hotmail.com
</RANT>
|||"Keith" <@..> wrote in message
news:uzMhc.32757$h44.4860659@.stones.force9.net...
> I am having a problem creating a many-to-many-to-many type relationship.
It
> works fine, but when I create a view to query it and test it, it does not
> generate the results I expected.
>
> Below if the DDL for the tables and the SQL for the view.
>
> Any help would be most appreciated.
>
>
> Many thanks in advance.
>
> Regards
>
> Keith
>
<snip>
See the reply to your previous post - as Jacco suggested, it seems that you
are using inner joins where you should be using outer joins. I suggest you
check out the examples of outer joins in Books Online, so you can see how
they work using a simple example. Your solution will probably involve left
joins from SYS_Individual to the other tables.
Simon
|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
works fine, .. <<
No, you seem to have 5NF problems. You cannot create a true three-way
relationship as a series of binary relationships; look up join-project
normal forms.
But you have a lot of other problems.
1) Why did you make everything NUMERIC(18,0)? Think about what an
amazing thing that would be if reality was like that.
2) Why did you use IDENTITY instead of looking for real keys?
3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
lack of any industry standards for columns, etc.
4) Why put physical history into the tables? There are tools for
that.
5) Isn't a venue an attribute of a course?
6) What is the logical meaning of those XREF tables in terms of a
logical data model?
7) What is a "_type_id"? An attribute is either a type or it is an
identifier, but never both. Again, you don't understand the
differences between data and metadata, so you mix them in wreird ways.
Your DDL ought to look more like this:
CREATE TABLE IndividualCourses
(ssn CHAR(9) NOT NULL
REFERENCES Individuals (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
course_id CHAR(5) NOT NULL)
REFERENCES Courses(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ssn, course_id));
You need constraints, defaults, real keys, logical names, etc. Start
over and get a book on data modeling.
|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> relationship. It
> works fine, .. <<
> No, you seem to have 5NF problems. You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
> But you have a lot of other problems.
> 1) Why did you make everything NUMERIC(18,0)? Think about what an
> amazing thing that would be if reality was like that.
> 2) Why did you use IDENTITY instead of looking for real keys?
> 3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
> lack of any industry standards for columns, etc.
> 4) Why put physical history into the tables? There are tools for
> that.
> 5) Isn't a venue an attribute of a course?
> 6) What is the logical meaning of those XREF tables in terms of a
> logical data model?
> 7) What is a "_type_id"? An attribute is either a type or it is an
> identifier, but never both. Again, you don't understand the
> differences between data and metadata, so you mix them in wreird ways.
> Your DDL ought to look more like this:
> CREATE TABLE IndividualCourses
> (ssn CHAR(9) NOT NULL
> REFERENCES Individuals (ssn)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> course_id CHAR(5) NOT NULL)
> REFERENCES Courses(course_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> PRIMARY KEY (ssn, course_id));
> You need constraints, defaults, real keys, logical names, etc. Start
> over and get a book on data modeling.
Just from curiosity - and I don't claim to have any answer to this question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not where
they were born.
Simon
|||Timothy J. Bruce wrote:
[]
> Do not include DDL/source-code in usenet posts. People will try to
> correct your code instead of your broken logic. For best results do not
> even mention your `specific implementation'.
> Have fun with your new `toy',
> Timothy J. Bruce
> uniblab@.hotmail.com
> </RANT>
>
That's rather poor advice. What posters should do is present as simple a
sample of the problem as possible, including standard SQL. Mentioning the
specific tools and environment used would be helpful.
The related suggestion is that posters should post DBMS specific questions in
the specific area and general questions in the general area. IOW, they should
post on topic questions.
How many ORACLE or SQL SERVER specific questions have we seen here in the
generic comp.databases group? Too many. So the original poster on this thread
was misguided in his posting to both specific SQL SERVER groups and to the
comp.databases group. Such a posting is nearly always off topic in one or the
other group.
Since his question was about relations and not SQL SERVER syntax, the
generic databases group is more appropriate.
If replies are just picking apart syntax, then I would suggest they are off
topic in the comp.databases group (unless maybe they are pointing out
something is not Standard SQL).
Bottom line is:
1. post to the appropriate group (goes for both original and reply posting)
2. post as complete and succuint information as possible (including your
platform DB and OS) for the comp.databases group.
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
|||Simon Hayes wrote:
[]
> Just from curiosity - and I don't claim to have any answer to this question
> myself - what primary key would you use for European students? The SSN
> doesn't exist, and students routinely study in a country which is not where
> they were born.
> Simon
>
Don't use SSN, assign a unique student ID.
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf
|||>> Just from curiosity - and I don't claim to have any answer to this
question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not
where they were born. <<
1) New York State used to make them get an SSN.
2) Use the holes in the Social Security Number. The SSN is composed of
3 parts, XXX-XX-XXXX, called the Area, Group, and Serial. The areas
are assigned as follows:
000 unused
627-699 unassigned, for future use
729-899 unassigned, for future use
900-999 not valid SSNs
3) invent a number if your state has privacy laws that require the SSN
not be used.
|||Ed Prochak <ed.prochak@.magicinterface.com> wrote:
[vbcol=seagreen]
>Timothy J. Bruce wrote:
>[]

>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
I will second this most strenuously. Details help. For example,
sometimes, a problem is due to a bug with a particular version of a
particular DBMS. If a poster is going to make me guess about details
like this, I guess I will probably just move on to the next post.
[snip]
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Help with Many-to-Many-to-Many Problem

I am having a problem creating a many-to-many-to-many type relationship. It
works fine, but when I create a view to query it and test it, it does not
generate the results I expected.

Below if the DDL for the tables and the SQL for the view.

Any help would be most appreciated.

Many thanks in advance.

Regards

Keith

DIAGRAM 5: SYS_Relationship_Individuals_Courses
(http://www.step-online.org.uk/diagram5.png)

This is the relationship I am having a problem with. Each individual can
attend many courses. I have tried to model this by creating this diagram.
It has the following tables in it. SYS_Individual (to show individuals).
To show courses (which already have a many-to-many relationship
(http://www.step-online.org.uk/diagram2.png), I added all the same tables as
in diagram 2 - SYS_Courses, SYS_Courses_Venues, SYS_Courses_TimeTable,
SYS_Courses_Tutors (joined using SYS_Xref_Join_Courses). Now as each
individual could attend many courses, I assumed that the correct way to
model this would be by creating another many-to-many between the
SYS_Individual table and the SYS_Xref_Join_Courses by using a new join table
(SYS_Xref_Join_Ind_Courses).

I tried to check this works using a view (SYS_Individual_Courses_List) -
VIEW 5 below.

The problem is that no matter how many entries I put in the
SYS_Xref_Join_Ind_Courses, the test view (VIEW 5 below), only ever shows one
record. While playing around with the view, I got it to show all the
entries, but they were duplicated 4 times each! I can't remember how I did
this now either.

Now for the DDL for the tables:

CREATE TABLE [do].[SYS_Individual] (

[IND_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[IND_Date_Entered] [datetime] NOT NULL ,

[IND_Date_on_Project] [datetime] NULL ,

[IND_First_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Surname] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Address] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Post_Code] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Telephone_Home] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Telephone_Mobile] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Telephone_Other] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Email_Address] [varchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Date_Started] [datetime] NULL ,

[IND_Trading_Name] [varchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Description_Proposed] [text] COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Profile] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_DD_Economic_Activity_ID] [numeric](18, 0) NULL ,

[IND_DD_Referal_Source_Code] [numeric](18, 0) NULL ,

[IND_DD_Training_Status_ID] [numeric](18, 0) NULL ,

[IND_DD_Age_ID] [numeric](18, 0) NULL ,

[IND_DD_Potential_Business_Type_ID] [numeric](18, 0) NULL ,

[IND_DD_Exit_ID] [numeric](18, 0) NULL ,

[IND_DD_Disadvantage_ID] [numeric](18, 0) NULL ,

[IND_DD_Bank_ID] [numeric](18, 0) NULL ,

[IND_DD_Start_Up_Confirmation_ID] [numeric](18, 0) NULL ,

[IND_DD_Growth_Potential_ID] [numeric](18, 0) NULL ,

[IND_DD_Ethnicity_ID] [numeric](18, 0) NULL ,

[IND_DD_Marital_Status_ID] [numeric](18, 0) NULL ,

[IND_DD_Gender_ID] [numeric](18, 0) NULL ,

[IND_DD_Status_ID] [numeric](18, 0) NULL ,

[IND_Related_To_Another] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_DD_Business_Status_ID] [numeric](18, 0) NULL ,

[IND_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Courses] (

[COURSE_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_Date_Entered] [datetime] NOT NULL ,

[COURSE_Title] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[COURSE_Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,

[COURSE_Last_Modified] [datetime] NULL

CREATE TABLE [dbo].[SYS_Courses_Venues] (

[COURSE_VEN_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_VEN_Date_Entered] [datetime] NOT NULL ,

[COURSE_VEN_Address] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,

[COURSE_VEN_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Courses_TimeTable] (

[COURSE_TT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_TT_Date_Entered] [datetime] NOT NULL ,

[COURSE_TT_Date] [datetime] NOT NULL

CREATE TABLE [dbo].[SYS_Courses_Tutors] (

[COURSE_TUT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_TUT_Date_Entered] [datetime] NOT NULL ,

[COURSE_TUT_First_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[COURSE_TUT_Surname] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[COURSE_TUT_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Xref_Join_Courses] (

[XREF_Courses_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[XREF_Courses_Date_Entered] [datetime] NOT NULL ,

[COURSE_ID] [numeric](18, 0) NOT NULL ,

[COURSE_VEN_ID] [numeric](18, 0) NOT NULL ,

[COURSE_TT_ID] [numeric](18, 0) NOT NULL ,

[COURSE_TUT_ID] [numeric](18, 0) NOT NULL ,

[XREF_Courses_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Xref_Join_Ind_Courses] (

[XREF_Ind_Course_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[XREF_Ind_Course_Date_Entered] [datetime] NOT NULL ,

[IND_ID] [numeric](18, 0) NOT NULL ,

[XREF_Courses_ID] [numeric](18, 0) NOT NULL ,

[XREF_Ind_Course_Last_Updated] [datetime] NULL

Now for the View:

VIEW 5:

CREATE VIEW dbo.SYS_Individual_Course_List

AS

SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title,

dbo.SYS_Courses_Venues.COURSE_VEN_Address,
dbo.SYS_Xref_Join_Ind_Courses.XREF_Ind_Course_ID

FROM dbo.SYS_Courses_Venues INNER JOIN

dbo.SYS_Courses INNER JOIN

dbo.SYS_Xref_Join_Ind_Courses INNER JOIN

dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID INNER JOIN

dbo.SYS_Xref_Join_Courses ON
dbo.SYS_Xref_Join_Ind_Courses.XREF_Ind_Course_ID =
dbo.SYS_Xref_Join_Courses.XREF_Courses_ID ON

dbo.SYS_Courses.COURSE_ID =
dbo.SYS_Xref_Join_Courses.COURSE_ID INNER JOIN

dbo.SYS_Courses_TimeTable ON
dbo.SYS_Xref_Join_Courses.COURSE_TT_ID =
dbo.SYS_Courses_TimeTable.COURSE_TT_ID INNER JOIN

dbo.SYS_Courses_Tutors ON
dbo.SYS_Xref_Join_Courses.COURSE_TUT_ID =
dbo.SYS_Courses_Tutors.COURSE_TUT_ID ON

dbo.SYS_Courses_Venues.COURSE_VEN_ID =
dbo.SYS_Xref_Join_Courses.COURSE_VEN_ID>> I am having a problem creating a many-to-many-to-many type
relationship. It
works fine, .. <<

No, you seem to have 5NF problems. You cannot create a true three-way
relationship as a series of binary relationships; look up join-project
normal forms.

But you have a lot of other problems.

1) Why did you make everything NUMERIC(18,0)? Think about what an
amazing thing that would be if reality was like that.

2) Why did you use IDENTITY instead of looking for real keys??

3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
lack of any industry standards for columns, etc.

4) Why put physical history into the tables? There are tools for
that.

5) Isn't a venue an attribute of a course?

6) What is the logical meaning of those XREF tables in terms of a
logical data model?

7) What is a "_type_id"?? An attribute is either a type or it is an
identifier, but never both. Again, you don't understand the
differences between data and metadata, so you mix them in wreird ways.

Your DDL ought to look more like this:

CREATE TABLE IndividualCourses
(ssn CHAR(9) NOT NULL
REFERENCES Individuals (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
course_id CHAR(5) NOT NULL)
REFERENCES Courses(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ssn, course_id));

You need constraints, defaults, real keys, logical names, etc. Start
over and get a book on data modeling.|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
> No, you seem to have 5NF problems. You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
> But you have a lot of other problems.
> 1) Why did you make everything NUMERIC(18,0)? Think about what an
> amazing thing that would be if reality was like that.
> 2) Why did you use IDENTITY instead of looking for real keys??
> 3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
> lack of any industry standards for columns, etc.
> 4) Why put physical history into the tables? There are tools for
> that.
> 5) Isn't a venue an attribute of a course?
> 6) What is the logical meaning of those XREF tables in terms of a
> logical data model?
> 7) What is a "_type_id"?? An attribute is either a type or it is an
> identifier, but never both. Again, you don't understand the
> differences between data and metadata, so you mix them in wreird ways.
> Your DDL ought to look more like this:
> CREATE TABLE IndividualCourses
> (ssn CHAR(9) NOT NULL
> REFERENCES Individuals (ssn)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> course_id CHAR(5) NOT NULL)
> REFERENCES Courses(course_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> PRIMARY KEY (ssn, course_id));
> You need constraints, defaults, real keys, logical names, etc. Start
> over and get a book on data modeling.

Just from curiosity - and I don't claim to have any answer to this question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not where
they were born.

Simon|||Keith et al:

> I am having a problem creating a many-to-many-to-many type relationship.
It
In a single word: `No shit'. This is by design.

> I am having a problem creating a many-to-many-to-many type relationship.
It
> works fine, but when I create a view to query it and test it, it does
not
> generate the results I expected.
You should not have expected it to work in the First Place.
If you need to create a many-to-many relationship between relations A and
B, then we create a third relation C.
Intersect relations A and B with C (that is C accepts foreign keys from
relations A and B).
Since relation C should only contain the foreign key attributes from
relations A and B you should be able to figure out how to work with it.

Do not include DDL/source-code in usenet posts. People will try to
correct your code instead of your broken logic. For best results do not
even mention your `specific implementation'.

Have fun with your new `toy',
Timothy J. Bruce
uniblab@.hotmail.com
</RANT|||"Keith" <@..> wrote in message
news:uzMhc.32757$h44.4860659@.stones.force9.net...
> I am having a problem creating a many-to-many-to-many type relationship.
It
> works fine, but when I create a view to query it and test it, it does not
> generate the results I expected.
>
> Below if the DDL for the tables and the SQL for the view.
>
> Any help would be most appreciated.
>
>
> Many thanks in advance.
>
> Regards
>
> Keith

<snip
See the reply to your previous post - as Jacco suggested, it seems that you
are using inner joins where you should be using outer joins. I suggest you
check out the examples of outer joins in Books Online, so you can see how
they work using a simple example. Your solution will probably involve left
joins from SYS_Individual to the other tables.

Simon|||>> Just from curiosity - and I don't claim to have any answer to this
question myself - what primary key would you use for European students?
The SSN doesn't exist, and students routinely study in a country which
is not where they were born. <<

Years ago, the policy in many US universities was to make all students
get a Social Security Number (ssn); you needed one to work anyway.

Later, schools kept a block of bogus SSNs. The Social Security Number
(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
Serial. For the most part, (there are exceptions), the Area is
determined by where the individual APPLIED for the SSN (before 1972) or
RESIDED at time of application (after 1972). The "bogus" areas are
assigned as follows:

000 unused
627-699 unassigned, for future use
700-728 Railroad workers through 1963, then discontinued. Anyone in
the Railroad retirement program is now dead, over 120 years of age or
converted to an SSN.
729-899 unassigned, for future use.
900-999 not valid SSNs, but were used for program purposes when state
aid to the aged, blind and disabled was converted to a federal program
administered by SSA. Again, this goes back to the 1930's and as far as I
know anyone in those programs is now dead or converted to an SSN.

The 627-699 areas let you treat each foreign student group separately
with its own area number.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Years ago, the policy in many US universities was to make all students
>get a Social Security Number (ssn); you needed one to work anyway.
>Later, schools kept a block of bogus SSNs. The Social Security Number
>(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
>Serial. For the most part, (there are exceptions), the Area is
>determined by where the individual APPLIED for the SSN (before 1972) or
>RESIDED at time of application (after 1972). The "bogus" areas are
>assigned as follows:
> 000 unused
> 627-699 unassigned, for future use
> 700-728 Railroad workers through 1963, then discontinued. Anyone in
>the Railroad retirement program is now dead, over 120 years of age or
>converted to an SSN.
> 729-899 unassigned, for future use.
> 900-999 not valid SSNs, but were used for program purposes when state
>aid to the aged, blind and disabled was converted to a federal program
>administered by SSA. Again, this goes back to the 1930's and as far as I
>know anyone in those programs is now dead or converted to an SSN.
>The 627-699 areas let you treat each foreign student group separately
>with its own area number.
>--CELKO--

Very enlightning. Thanks.
Got any more info or links where I can find out more info on this.

Randy
http://members.aol.com/rsmeiner|||>> Got any more info or links where I can find out more info on this. <<

Try doing a Google and go to the SSA site.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>>> Got any more info or links where I can find out more info on this. <<
>Try doing a Google and go to the SSA site.
>--CELKO--

You mean you actually expect me to do my own research ?
Well, ok.

Randy
http://members.aol.com/rsmeiner|||"RSMEINER" <rsmeiner@.aol.comcrap> wrote in message
news:20040425123019.27697.00000064@.mb-m17.aol.com...
> >Years ago, the policy in many US universities was to make all students
> >get a Social Security Number (ssn); you needed one to work anyway.

Years ago. This is no longer true, as places like NYS outlawed that use.

RPI had to convert to a new internal ID.

So, SSN may not always be a great choice.

However, as Joe's sure to ask or point out, the RPI assigned ID exists
independent of any single database.

i.e. they're not using some random IDENTITY field and going from there.|||Timothy J. Bruce wrote:

[]
> Do not include DDL/source-code in usenet posts. People will try to
> correct your code instead of your broken logic. For best results do not
> even mention your `specific implementation'.
> Have fun with your new `toy',
> Timothy J. Bruce
> uniblab@.hotmail.com
> </RANT>
>
That's rather poor advice. What posters should do is present as simple a
sample of the problem as possible, including standard SQL. Mentioning the
specific tools and environment used would be helpful.

The related suggestion is that posters should post DBMS specific questions in
the specific area and general questions in the general area. IOW, they should
post on topic questions.

How many ORACLE or SQL SERVER specific questions have we seen here in the
generic comp.databases group? Too many. So the original poster on this thread
was misguided in his posting to both specific SQL SERVER groups and to the
comp.databases group. Such a posting is nearly always off topic in one or the
other group.

Since his question was about relations and not SQL SERVER syntax, the
generic databases group is more appropriate.

If replies are just picking apart syntax, then I would suggest they are off
topic in the comp.databases group (unless maybe they are pointing out
something is not Standard SQL).

Bottom line is:
1. post to the appropriate group (goes for both original and reply posting)
2. post as complete and succuint information as possible (including your
platform DB and OS) for the comp.databases group.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost|||Simon Hayes wrote:

[]
> Just from curiosity - and I don't claim to have any answer to this question
> myself - what primary key would you use for European students? The SSN
> doesn't exist, and students routinely study in a country which is not where
> they were born.
> Simon
>
Don't use SSN, assign a unique student ID.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.

Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
works fine, .. <<

No, you seem to have 5NF problems. You cannot create a true three-way
relationship as a series of binary relationships; look up join-project
normal forms.

But you have a lot of other problems.

1) Why did you make everything NUMERIC(18,0)? Think about what an
amazing thing that would be if reality was like that.

2) Why did you use IDENTITY instead of looking for real keys??

3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
lack of any industry standards for columns, etc.

4) Why put physical history into the tables? There are tools for
that.

5) Isn't a venue an attribute of a course?

6) What is the logical meaning of those XREF tables in terms of a
logical data model?

7) What is a "_type_id"?? An attribute is either a type or it is an
identifier, but never both. Again, you don't understand the
differences between data and metadata, so you mix them in wreird ways.

Your DDL ought to look more like this:

CREATE TABLE IndividualCourses
(ssn CHAR(9) NOT NULL
REFERENCES Individuals (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
course_id CHAR(5) NOT NULL)
REFERENCES Courses(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ssn, course_id));

You need constraints, defaults, real keys, logical names, etc. Start
over and get a book on data modeling.|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
> No, you seem to have 5NF problems. You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
> But you have a lot of other problems.
> 1) Why did you make everything NUMERIC(18,0)? Think about what an
> amazing thing that would be if reality was like that.
> 2) Why did you use IDENTITY instead of looking for real keys??
> 3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
> lack of any industry standards for columns, etc.
> 4) Why put physical history into the tables? There are tools for
> that.
> 5) Isn't a venue an attribute of a course?
> 6) What is the logical meaning of those XREF tables in terms of a
> logical data model?
> 7) What is a "_type_id"?? An attribute is either a type or it is an
> identifier, but never both. Again, you don't understand the
> differences between data and metadata, so you mix them in wreird ways.
> Your DDL ought to look more like this:
> CREATE TABLE IndividualCourses
> (ssn CHAR(9) NOT NULL
> REFERENCES Individuals (ssn)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> course_id CHAR(5) NOT NULL)
> REFERENCES Courses(course_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> PRIMARY KEY (ssn, course_id));
> You need constraints, defaults, real keys, logical names, etc. Start
> over and get a book on data modeling.

Just from curiosity - and I don't claim to have any answer to this question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not where
they were born.

Simon|||>> Just from curiosity - and I don't claim to have any answer to this
question myself - what primary key would you use for European students?
The SSN doesn't exist, and students routinely study in a country which
is not where they were born. <<

Years ago, the policy in many US universities was to make all students
get a Social Security Number (ssn); you needed one to work anyway.

Later, schools kept a block of bogus SSNs. The Social Security Number
(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
Serial. For the most part, (there are exceptions), the Area is
determined by where the individual APPLIED for the SSN (before 1972) or
RESIDED at time of application (after 1972). The "bogus" areas are
assigned as follows:

000 unused
627-699 unassigned, for future use
700-728 Railroad workers through 1963, then discontinued. Anyone in
the Railroad retirement program is now dead, over 120 years of age or
converted to an SSN.
729-899 unassigned, for future use.
900-999 not valid SSNs, but were used for program purposes when state
aid to the aged, blind and disabled was converted to a federal program
administered by SSA. Again, this goes back to the 1930's and as far as I
know anyone in those programs is now dead or converted to an SSN.

The 627-699 areas let you treat each foreign student group separately
with its own area number.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Years ago, the policy in many US universities was to make all students
>get a Social Security Number (ssn); you needed one to work anyway.
>Later, schools kept a block of bogus SSNs. The Social Security Number
>(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
>Serial. For the most part, (there are exceptions), the Area is
>determined by where the individual APPLIED for the SSN (before 1972) or
>RESIDED at time of application (after 1972). The "bogus" areas are
>assigned as follows:
> 000 unused
> 627-699 unassigned, for future use
> 700-728 Railroad workers through 1963, then discontinued. Anyone in
>the Railroad retirement program is now dead, over 120 years of age or
>converted to an SSN.
> 729-899 unassigned, for future use.
> 900-999 not valid SSNs, but were used for program purposes when state
>aid to the aged, blind and disabled was converted to a federal program
>administered by SSA. Again, this goes back to the 1930's and as far as I
>know anyone in those programs is now dead or converted to an SSN.
>The 627-699 areas let you treat each foreign student group separately
>with its own area number.
>--CELKO--

Very enlightning. Thanks.
Got any more info or links where I can find out more info on this.

Randy
http://members.aol.com/rsmeiner|||>> Got any more info or links where I can find out more info on this. <<

Try doing a Google and go to the SSA site.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>>> Got any more info or links where I can find out more info on this. <<
>Try doing a Google and go to the SSA site.
>--CELKO--

You mean you actually expect me to do my own research ?
Well, ok.

Randy
http://members.aol.com/rsmeiner|||"RSMEINER" <rsmeiner@.aol.comcrap> wrote in message
news:20040425123019.27697.00000064@.mb-m17.aol.com...
> >Years ago, the policy in many US universities was to make all students
> >get a Social Security Number (ssn); you needed one to work anyway.

Years ago. This is no longer true, as places like NYS outlawed that use.

RPI had to convert to a new internal ID.

So, SSN may not always be a great choice.

However, as Joe's sure to ask or point out, the RPI assigned ID exists
independent of any single database.

i.e. they're not using some random IDENTITY field and going from there.|||Timothy J. Bruce wrote:

[]
> Do not include DDL/source-code in usenet posts. People will try to
> correct your code instead of your broken logic. For best results do not
> even mention your `specific implementation'.
> Have fun with your new `toy',
> Timothy J. Bruce
> uniblab@.hotmail.com
> </RANT>
>
That's rather poor advice. What posters should do is present as simple a
sample of the problem as possible, including standard SQL. Mentioning the
specific tools and environment used would be helpful.

The related suggestion is that posters should post DBMS specific questions in
the specific area and general questions in the general area. IOW, they should
post on topic questions.

How many ORACLE or SQL SERVER specific questions have we seen here in the
generic comp.databases group? Too many. So the original poster on this thread
was misguided in his posting to both specific SQL SERVER groups and to the
comp.databases group. Such a posting is nearly always off topic in one or the
other group.

Since his question was about relations and not SQL SERVER syntax, the
generic databases group is more appropriate.

If replies are just picking apart syntax, then I would suggest they are off
topic in the comp.databases group (unless maybe they are pointing out
something is not Standard SQL).

Bottom line is:
1. post to the appropriate group (goes for both original and reply posting)
2. post as complete and succuint information as possible (including your
platform DB and OS) for the comp.databases group.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost|||Simon Hayes wrote:

[]
> Just from curiosity - and I don't claim to have any answer to this question
> myself - what primary key would you use for European students? The SSN
> doesn't exist, and students routinely study in a country which is not where
> they were born.
> Simon
>
Don't use SSN, assign a unique student ID.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.

Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> Just from curiosity - and I don't claim to have any answer to this
question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not
where they were born. <<

1) New York State used to make them get an SSN.

2) Use the holes in the Social Security Number. The SSN is composed of
3 parts, XXX-XX-XXXX, called the Area, Group, and Serial. The areas
are assigned as follows:

000 unused
627-699 unassigned, for future use
729-899 unassigned, for future use
900-999 not valid SSNs

3) invent a number if your state has privacy laws that require the SSN
not be used.|||>> Just from curiosity - and I don't claim to have any answer to this
question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not
where they were born. <<

1) New York State used to make them get an SSN.

2) Use the holes in the Social Security Number. The SSN is composed of
3 parts, XXX-XX-XXXX, called the Area, Group, and Serial. The areas
are assigned as follows:

000 unused
627-699 unassigned, for future use
729-899 unassigned, for future use
900-999 not valid SSNs

3) invent a number if your state has privacy laws that require the SSN
not be used.|||Ed Prochak <ed.prochak@.magicinterface.com> wrote:

>Timothy J. Bruce wrote:
>[]
>>
>> Do not include DDL/source-code in usenet posts. People will try to
>> correct your code instead of your broken logic. For best results do not
>> even mention your `specific implementation'.
>>
>> Have fun with your new `toy',
>> Timothy J. Bruce
>> uniblab@.hotmail.com
>> </RANT
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.

I will second this most strenuously. Details help. For example,
sometimes, a problem is due to a bug with a particular version of a
particular DBMS. If a poster is going to make me guess about details
like this, I guess I will probably just move on to the next post.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.|||joe.celko@.northface.edu (--CELKO--) wrote:

>>> Just from curiosity - and I don't claim to have any answer to this
>question
>myself - what primary key would you use for European students? The SSN
>doesn't exist, and students routinely study in a country which is not
>where they were born. <<
>1) New York State used to make them get an SSN.

What do you do in the meantime for a PK? I--a Canadian--apply for
admission, and you will not admit me because I do not have an SSN? I
think I would want to take database courses elsewhere then!

>2) Use the holes in the Social Security Number. The SSN is composed of
>3 parts, XXX-XX-XXXX, called the Area, Group, and Serial. The areas
>are assigned as follows:
> 000 unused
> 627-699 unassigned, for future use
> 729-899 unassigned, for future use
> 900-999 not valid SSNs

And if the definition changes?

>3) invent a number if your state has privacy laws that require the SSN
>not be used.

I think #3 is best. 1) If your jurisdictions do not have privacy
laws, it does not mean that they never will. Some came into effect in
Canada this last New Year's Day. 2) If it is Their number, what do
you do if They change it or its definition?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.|||Ed Prochak <ed.prochak@.magicinterface.com> wrote:

>Timothy J. Bruce wrote:
>[]
>>
>> Do not include DDL/source-code in usenet posts. People will try to
>> correct your code instead of your broken logic. For best results do not
>> even mention your `specific implementation'.
>>
>> Have fun with your new `toy',
>> Timothy J. Bruce
>> uniblab@.hotmail.com
>> </RANT
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.

I will second this most strenuously. Details help. For example,
sometimes, a problem is due to a bug with a particular version of a
particular DBMS. If a poster is going to make me guess about details
like this, I guess I will probably just move on to the next post.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.|||joe.celko@.northface.edu (--CELKO--) wrote:

>>> Just from curiosity - and I don't claim to have any answer to this
>question
>myself - what primary key would you use for European students? The SSN
>doesn't exist, and students routinely study in a country which is not
>where they were born. <<
>1) New York State used to make them get an SSN.

What do you do in the meantime for a PK? I--a Canadian--apply for
admission, and you will not admit me because I do not have an SSN? I
think I would want to take database courses elsewhere then!

>2) Use the holes in the Social Security Number. The SSN is composed of
>3 parts, XXX-XX-XXXX, called the Area, Group, and Serial. The areas
>are assigned as follows:
> 000 unused
> 627-699 unassigned, for future use
> 729-899 unassigned, for future use
> 900-999 not valid SSNs

And if the definition changes?

>3) invent a number if your state has privacy laws that require the SSN
>not be used.

I think #3 is best. 1) If your jurisdictions do not have privacy
laws, it does not mean that they never will. Some came into effect in
Canada this last New Year's Day. 2) If it is Their number, what do
you do if They change it or its definition?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.