Need to avoid repeated data in a DataGrid

Hi there :)

I am developing a system for my uni course and I am stuck a little problem...

Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.

I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.

How can I stop the repeats?

Heres my sql select code in my cs file:

string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();

And here is a pic of my Data Model:
Data Model Screenshot

Any ideas? Many thanks :) !Its ok, sorted it now :)

Need some help with query

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Try to use joins instead of alias names
sample:
3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME from Project b
left join WORKSON c on C.Pnumber=b.Pnumber
left join EMPLOYEE a on a.SSN=c.SSN
WHERE b.PLOCATION !=Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

select distinct Employee.Name from Project
left join Workson on Workson.PNumber=Project.PNumber
left join Employee on Employee.SSN=Workson.SSN
where Project.PLOcation='Houston'

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

Select Name from Employee where SSN=(Select SSN from
(Select SSN,count(SSN) as cnt from
(select distinct SSN,Workson.PNumber from Project
left join Workson on Workson.Pnumber= Project.PNumber
where Project.PLocation='Houston'
group by SSN,Workson.PNumber) as A
group by SSN) as B
where cnt=(select Count(Pnumber) from Project Where Plocation='Houston'))|||

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. List the name(s) of employee(s) who works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Hi
Different way for different queries exist.
if u still have problem in 3rd query u can use the following.

SELECT a.NAME
FROM
EMPLOYEE AS a, WORKSON AS b
WHERE a.SSN=b.SSN and b.PNUMBER IN
(SELECT PNUMBER
FROM PROJECT
WHERE PLOCATION NOT IN('HOUSTON'));

IF U STILL HAVE ANY PROBLEM IN ANY OTHER QUERY PLZ TELL I WILL TRY MY BEST TO SOLVE THAT ONE.sql

Need ID after OLEDB command (insert)

Hi,

I'm stuck on the following thing:

After a slowly changing dimension task I replaced the OLE DB Destination task by an OLE DB Command and created the insert manually. This because I need to work further on the dataset. So I do a union all between the output of the two OLE DB Commands (insert and update). Untill here no problem. But than, because I need an ID further on, I do a lookup in the table in which I just inserted and updated my data for the right ID's. When I run this project I get the error message "row yielded no match during lookup".

I don't understand this beacause I just inserted the data and I've checked, it's there.

I could resolve this by splitting up in two control flows (reselect all the needed data wíth the ID-field in my selection) but I would prefer to solve it in another way

Greets,

Tom

You cannot be assured that the inserted records exist and are avaiable to you until after the data-flow completes. You will need two data-flows I'm afraid. Use a raw file to pass data between them if you require.

-Jamie

|||

Hi Jamie,

In my opinion SSIS does a sort of precaching (I mean loading the lookup table in to memory before real execution of the whole data flow task and that must be the reason why he can't perform a lookup in an 'empty' table during the execution). << Can't I force him not to cache that table? just an idea >>

But in order to continue and to satisfy my boss, I will split it up...

Thanks anyway

Greets,
-Tom

|||

Tom DC wrote:

Hi Jamie,

In my opinion SSIS does a sort of precaching (I mean loading the lookup table in to memory before real execution of the whole data flow task and that must be the reason why he can't perform a lookup in an 'empty' table during the execution). << Can't I force him not to cache that table? just an idea >>

But in order to continue and to satisfy my boss, I will split it up...

Thanks anyway

Greets,
-Tom

Yes, you can easily configure the LOOKUP component not to cache any data. Still though you cannot guarantee that a row that you think will be there will indeed be there. [This is due to the buffer architecture of the pipeline which is elaborated on at various places around the internet and in textbooks if you fancy some heavy reading.]

So, regardless of your caching options you still need two data-flows.

-Jamie

|||

Allright, I've split it up. Thanks for your help!

|||

Setting the CacheType to none (and using a lookup table instead of SQL statement to avoid weird errors about unmatched parameters) did solve our problem. The data we expect to find in the table was written in the same dataflow by an OLE DB Command - it is not part of the dataflow itself.

Also, the lookup happens in a different "Execution Tree" than the insert via the OLE DB Command.

I think we are safe and do not have to split the dataflow.

Tom (the satisfied boss)

|||

Tom VdP wrote:

Setting the CacheType to none (and using a lookup table instead of SQL statement to avoid weird errors about unmatched parameters) did solve our problem. The data we expect to find in the table was written in the same dataflow by an OLE DB Command - it is not part of the dataflow itself.

Also, the lookup happens in a different "Execution Tree" than the insert via the OLE DB Command.

I think we are safe and do not have to split the dataflow.

Tom (the satisfied boss)

Fair enough. I can only advise as strngly as I possibly can that you do not under any circumstances do this. There is no way to guarantee that a record that you expect to be in teh table will indeed be there. This is because SSIS processes data buffer-by-buffer, not row-by-row.

It is of course up to you

-Jamie

|||

Jamie,

I understand. But how could SSIS treat an OLEDBCommand differently than just directly executing it ? The data flow engine doesn't know what the SQL command does, hence there is no way it would be able to buffer the result. Every "row" in the dataflow below the OLEDBCommand must have been processed (in our case: inserted in a table). Or am I thinking too much the old "DTS-way" ?

Regards,

Tom

|||

Tom VdP wrote:

Jamie,

I understand. But how could SSIS treat an OLEDBCommand differently than just directly executing it ? The data flow engine doesn't know what the SQL command does, hence there is no way it would be able to buffer the result. Every "row" in the dataflow below the OLEDBCommand must have been processed (in our case: inserted in a table). Or am I thinking too much the old "DTS-way" ?

Regards,

Tom

Tom,

Basically, yeah. You're thinking in DTS terms. The data-flow processes rows in groups called buffers whereas DTS was more row-by-row. Hence, if a row enters the LOOKUP component and it requires a row from the same buffer to be in the lookup table - it won't (yet) be in the lookup table.

So when i talk about buffers - I don't mean that any result is being buffered. Sorry, I should have elaborated on that more. The buffer architecture is a key feature of SSIS and I highly recommend you read around it to understand what it does and why it does it.

-Jamie

|||

Slight misunderstanding :-)

I thought you were hinting at the fact that the output of the OLEDBCommand might not yet be available. But you are referring to the input of the Lookup component. But then what is the CacheType property for ? If this is set to "none", doesn't it indicate that each lookup should be done against the actual contents of the table ? This property is undocumented in MSDN...

Regards,

Tom

|||

Correct. But like I'm trying (and failing miserably ) to explain is that there is no guarantee that a row put there by an OLE_DB Command/Destination adapter/whatever... will be available to all subsequent rows in the data-flow.

-Jamie

|||

Ok... to roundup allow me to summarize:

all data below an OLEDBCommand will have had its corresponding SQL statement executed, hence those changes are visible in the database