Retrieving data from 4 tables

hi guys, i need some help in making an SQL statement.
i am really having a hard time making the ryt one so please
help!!!!!! :eek:

i'll first give an introduction.
i have this program that needs to display a data, however these
data will come from 4 tables.

(actual contents of the tables and scenarios of the program
was changed to make it easier for others to understand the
situation and the problem) :p

TABLE 1 (COLLEGES)
This table consists of the different colleges that a university has.
ie. College of Engineering, College of Law, etc

table design:
COLLEGE_NO
COLLEGE_NAME
SEM_NO

TABLE 2 (COURSES)
This table consist of the courses that a university offers.
ie. Theology, Chemistry, Algebra, etc.

table design:
COURSE_NO
SEM_NO

TABLE 3 (OFFERINGS)
This table consist of the number of course offerings of a
specific course for a specific college and semester.

table design:
COLLEGE_NO
COURSE_NO
NO_OF_OFFERING
SEM_NO

TABLE 4 (COURSE_MASTER)
Master table for the different courses available

table design
COURSE_NO
COURSE_NAME

--

There is a screen where a user can add Colleges
(ie. College of Architecture) and the data is stored in the
COLLEGES table (TABLE 1).
There is also a screen where a user can add Courses
(ie, Calculus, Programming, P.E., etc) and the data is stored
in COURSE_MASTER table (TABLE 4) and COURSES table (TABLE 2)
* this may be weird but please bare with me since i am just
immitating the actual scenario for better understanding

Now I have a 3rd screen where a user will input the number of
offering for a particular course for a specific college.
This is a sample image of the screen

ENGINEERING ARTS LAW
COURSE
Programming 3 0 0
Theology 3 3 3
Biology - - -

Now this data will be saved on the OFFERINGS table (TABLE 3)
The Colloge_No, the Course_No, the No_Of_Offering, and Sem_No
will be saved.

Now the behaviour of the program will be like this...
All the Colleges that is stored in the COLLEGES table will be
displayed as Column Headers in the screen
All the Courses that is stored in the COURSES table will be
displayed as Row Headers.
However, if you guys noticed we need to display the Course Name
and this data is not stored in COURSES table, it is stored in
COURSE_MASTER table.

Lastly we need to display the number of offerings.
this is displayed on the OFFERINGS table.
In the example, the Course Programming has 3 offerings
for the College of ENgineering and 0 offerings for Law
This data is stored in the OFFERINGS table.

If you guys noticed, that under the Course Biology,
"-" is written. This means that there is no record of
Number of Offerings yet at the OFFERINGS table

So now this is my problem, how or what SQL statement do
I need to make to retrieve all the data stored in the
OFFERINGS table, (this already contains the No of offerings,
courses, and colleges) and aside from this also retireve all
the other Colleges and Courses that is already stored
in their respective tables but may
not yet exists in the OFFERINGS table.

so this is my problem...
please help...
thnx in advance...Looks like you need to use a LEFT JOIN.
What have you got so far? (Post SQL statement)|||this is what i've done so far.

select t1.SEM_NO, t1.COURSE_NO, t2.COLLEGE_NAME, null
from COURSES t1, COLLEGES t2
where t1.SEM_NO = t2.SEM_NO and
t1.SEM_NO = 'User Input' and
and (t1.SEM_NO, t1.COURSE_NO, t2.COLLEGE_NAME)
not in (select
t4.SEM_NO, t4.COURSE_NO,t4.COLLEGE_NAME
from OFFERINGS t4
where t4.SEM_NO = 'User Input')
group by t1.COURSE_NO, t2.COLLEGE_NAME
union
select t4.SEM_NO, t4.COURSE_NO, t4.COLLEGE_NAME, t4.NO_OF_OFFERING
from OFFERINGS t4 where t4.SEM_NO = 'User Input';

This SQL statement is already OK, but I am having some problem
including the table COURSE_MASTER, so I can retrieve the
COURSE_NAME and I cant make the ORDER BY work...

any suggestions.
thnx.|||* this may be weird but please bare with me since i am just
immitating the actual scenario for better understanding
can you change your example to use the real tables?

the table designs you gave in post #1 don't make any sense

FYI it should be "please bear with me" because "please bare with me" means let's take our clothes off together|||*chuckle* :)|||already fixed the problem.

thnx for all the help.

:)|||Would you like to post your solution so that others with similar problems may benefit from it? :)

Retrieve Count from stored procedure and display in datagrid.

Hi Guys,

I have a sql procedure that returns the following result when I execute it in query builder:

CountE ProjStatus

6 In Progress

3 Complete

4 On Hold

The stored procedure is as follow:

SELECT COUNT(*) AS countE, ProjStatus
FROM PROJ_Projects
GROUP BY ProjStatus

This is the result I want but when I try to output the result on my asp.net page I get the following error:

DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Source Error:

Line 271: </asp:TemplateColumn>Line 272: <asp:TemplateColumn>Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>Line 274: </asp:TemplateColumn>Line 275: </columns>

My asp.net page is as follows:

<script runat="server">

Dim myCommandPSAsNew SqlCommand("PROJ_GetProjStatus")

' Mark the Command as a SPROC

myCommandPS.CommandType = CommandType.StoredProcedure

Dim numasinteger

num =CInt(myCommand.ExecuteScalar)

'Set the datagrid's datasource to the DataSet and databind

Dim myAdapterPSAsNew SqlDataAdapter(myCommandPS)

Dim dsPSAsNew DataSet()

myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS

dgProjSumm.DataBind()

myConnection.Close()

</script>

<asp:datagridid="dgProjSumm"runat="server"

BorderWidth="0"

Cellpadding="4"

Cellspacing="0"

Width="100%"

Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"

Font-Size="xx-small"

AutoGenerateColumns="false">

<columns>

<asp:TemplateColumnHeaderText="Project Summary"HeaderStyle-Font-Bold="true">

<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem,"ProjStatus" ))%></itemtemplate>

</asp:TemplateColumn>

<asp:TemplateColumn>

<itemtemplate> <%# DataBinder.Eval(Container.DataItem,"Count" )%></itemtemplate>

</asp:TemplateColumn>

</columns>

</asp:DataGrid>

Please help if you can Im havin real trouble here.

Cheers

Since you have the count aliased as countE, it should be:

<%# DataBinder.Eval(Container.DataItem, "countE" )%>

|||

Thanks for pointing that out, I changed it and get the same error, any other ideas, do I have to create an output parameter or something.

Cheers

|||

Why are you using ExecuteScalar and a DataSet? Also, where is the SQL Connection as I didn't see that in your code?

Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
myCommandPS.CommandType = CommandType.StoredProcedure

Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()

retrieve columns information on a different database table

Hi guys!
Here is my situation:
I have a stored procedure in my first database and inside it I need to
retrieve column names of a table located in a different database. The
database name and the table name are provided as sysname parameters to my
stored procedure.
How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
Kind regards,
ConstantinYou can qualify the INFORMATION_SCHEMA.COLUMNS view with a database name.
Linchi
"Constantin Stan" wrote:
> Hi guys!
> Here is my situation:
> I have a stored procedure in my first database and inside it I need to
> retrieve column names of a table located in a different database. The
> database name and the table name are provided as sysname parameters to my
> stored procedure.
> How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
>
> Kind regards,
> Constantin
>
>|||Hi Linchi!
How may I qualify the name?
I tried something with @.FullSourceTableName but you may not select from that
name. It should be a table. It seems I missed some tsql classes.
Apreciate your help!
PS: this is my stored procedure:
ALTER PROCEDURE [dbo].[usp_CreateReportLocalizationForObject]
@.SourceDB as sysname,
@.SourceTable as sysname,
@.Culture varchar(10)
AS
BEGIN
-- i'm looking for something like this. but it doesn't work
declare @.FullSourceTableName sysname
set @.FullSourceTableName =OBJECT_NAME(OBJECT_ID('INFORMATION_SCHEMA.COLUMNS'), DB_ID(@.SourceDB));
print @.FullSourceTableName
-- open cursor to loop the columns list returned as a resultset
declare ColumnsList cursor for (select COLUMN_NAME from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.SourceTable)
open ColumnsList
-- loop records
declare @.ColumnName sysname
fetch next from ColumnsList into @.ColumnName
while (@.@.FETCH_STATUS = 0)
begin
-- if there is no record for this column, then insert, otherwise do nothing!
-- put a default value for DisplayName and Status
if not exists(select * from ReportLocalization where
((ViewName=@.SourceTable) AND (ColumnName=@.ColumnName) AND
(Culture=@.Culture)))
begin
INSERT INTO [ReportLocalization]
(ViewName,[ColumnName],[Culture],[DisplayName],[DisplayCategory],[Status])
VALUES (@.SourceTable, @.ColumnName, @.Culture, @.ColumnName, '', 'active')
end
fetch next from ColumnsList into @.ColumnName
end
-- close cursor and deallocate resources
close ColumnsList
deallocate ColumnsList
-- insert a record for the view name, with ColumnName as empty string, and
default value for DisplayName as SourceTable
if not exists(select * from ReportLocalization where
((ViewName=@.SourceTable) AND (ColumnName='') AND (Culture=@.Culture)))
begin
INSERT INTO [ReportLocalization]
(ViewName,[ColumnName],[Culture],[DisplayName],[DisplayCategory],[Status])
VALUES (@.SourceTable, '', @.Culture, @.SourceTable, '', 'active')
end
-- Return to caller
--
RETURN 0
END
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:A850A318-5925-4BF1-B0C6-D4E3BB64EB05@.microsoft.com...
> You can qualify the INFORMATION_SCHEMA.COLUMNS view with a database name.
> Linchi
> "Constantin Stan" wrote:
>> Hi guys!
>> Here is my situation:
>> I have a stored procedure in my first database and inside it I need to
>> retrieve column names of a table located in a different database. The
>> database name and the table name are provided as sysname parameters to my
>> stored procedure.
>> How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
>>
>> Kind regards,
>> Constantin
>>