A Querying to Date Problm - Database Discussion Boards

brdavid2-Jul-04 6:31

2-Jul-04 6:31

I need a little brainstorming from anyone here who is willing to help me figure out a way to represent the following scenerio in a database.

I have a setup routine that will allow users to identify Skills and Subskills for an individual. I have the Skills represented in a Table called SKILLS and the SubSkill in a table of course called SUBSKILLS. Subskills are simply just that, a skill within a skill, such as a skill could be Programming and a subskill could be C++.

there is a possibility that a skill could have 100s of subskills. I was thinking of separating such subskills into a separate table but it seems as if there is so much over head and in many instances categorizing some of the subskills into separate tables seems more positive than having them crammed into the same table. Unless there is something I may not be seeing here, which is why I am looking for a second opinion.

Say you have these two tables SKILLS and SUBSKILLS and SUBSKILLS is dependant on the SKILL but exists N skills, perhaps 20 or more, that will have at least 100 or even 1000 Subskills. How could you easily represent this in a relational setting and allow for an easy way to update. I hope I explained this well. If not, I will elaborate further.

Thanks for the help!

Re: Could use an extra brain...

Steven Campbell2-Jul-04 11:15

Steven Campbell

2-Jul-04 11:15

Thats a fairly common problem, at least in my experience. The simplest from a data capture point of view is to make no distinction between a skill and a subskill, and to store them in the same table, with one of the fields being a foreign key referencing the same table. That allows a lot of flexibility in terms of setting up heirarchies.

A top-level skill will usually have a NULL value in the foreign key field, indicating that it does not have a parent.

If you stick to allowing only a few levels of skills, then this structure is adequate. If you need more, then it may lead to performance concerns when attempting to display the structure on the screen.

If you just need a simple heirarchy of 2 levels (skill & subskill) then you can just have a skill & subskill table, with a simple 1 to many relation.

You can extend either model with categories by creating a category table, linked to the subskills or the skills table, such that a skill/subskill can have multiple categories, independent of its relation to other skills.

my blog

Re: Could use an extra brain...

brdavid2-Jul-04 14:53

brdavid

2-Jul-04 14:53

There was also another part I forgot to mention but your idea seems interesting. The one thing I forgot to mention is that another table exists called SKILLSINAGES that represents Skills which reflect different periods of time. That is a skill may become non-existant after a while so I would see this skill either appears or not if looking at a specific period of time. SKILLS would thus hold all information, that is, the Description and other pertenant info.
So far, from your ideas, let me show you what I have to see if I not only understand your idea but to also see if this is a good enough solution. Smile | :)

SKILLS (represents all skills and subskills. Key is SkillID)
SKILLSINAGES (AgeID, SkillID, ReferencesTableID)
Where ReferencesTableID represents the name of the table that holds my Skill field.

In my original design I had a table for each set of SubSkills, i.e. HISTORY, plus another "adjoining" table which would follow in name HISTORYINAGES. This led to a database with the possibility of a LOT of tables in the end result. I wanted to curb that. So it seems you may have given me an idea.

I can have just ONE table which holds the Description much like the SKILLS table for Skills for for my SubSkills. Then when it comes to defining these Skills into an Age I was thinking of putting them in the SKILLSINAGES table. Or should I put them in another table and not complicate the SKILLSINAGES table further. There is possibility that SKILLSINAGES could get into the thousands of records, maybe even break 10,000.

Thanks for that idea. It certainly helped! Big Grin | :-D

SQL query required

Imtiaz Murtaza1-Jul-04 23:02

Imtiaz Murtaza

1-Jul-04 23:02

Friends,
I got a table in which records are in the following manner:

category               product
========               =======
clothes                jeans
clothes                shirts
clothes                ties
weapons                Machine Gun
weapons                Missile
ColdDrinks             Pepsi
ColdDrinks             Coca Cola

Now i want to select a result from the above table in such a manner that that the first record of each "category" gets returned. For example, select result should be:

category               product
========               =======
clothes                jeans
weapons                Machine Gun
ColdDrinks             Pepsi

How can i do so ?

Imtiaz

Re: SQL query required

wgdesigner2-Jul-04 22:23

wgdesigner

2-Jul-04 22:23

Dear Friend,

I don;t know which type of query u want, as ur mind asks..

but i have one query which will give u same result which u want..

select category,product from table_name where product
in('jeans','machine gun','pepsi');

from taht u can view same result as u want,

Raj Khatri

Re: SQL query required

Go2Canada5-Jul-04 3:07

Go2Canada

5-Jul-04 3:07

Try the following SQL statement:

SELECT Category, FIRST(Product) AS FirstOfProduct
FROM YourTable GROUP BY Category;

You may substitue FIRST() with either MIN() or MAX() if FIRST() is not supported. Hope that works...

Go2Canada

Re: SQL query required

irshu8-Jul-04 21:09

irshu

8-Jul-04 21:09

Your solution is here it works fine for your problem.

select distinct(category),
(select top 1 product from myTable b where b.category = a.category) as 'Product' from myTable a

A Querying to Date Problm

Anonymous1-Jul-04 20:50

Anonymous

1-Jul-04 20:50

i m using VC++ n Access as Database

if i m using hard code values of date n month n year its gives result fine

as query is

rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM Record WHERE (([Record].[Date] Between #9/27/2001# And #7/30/2004#))");

But i wanaa use values of date . year n month that r input by user .. in form of String or integer in variables .. m_date . m_month .. and m_year.

how can i use these variables values in Query now

i used like this but Wrong Query message appears
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM Record WHERE (([Record].[Date] Between #m_month/m_date/m_year# And #m_month1/m_date1/m_year1#))");

I also Tried by TO_Date Function ,, but its said undefined function

how can i do it by user values ..

thanx

Please help - can't update screen while waiting for SP in C#.

unRheal1-Jul-04 12:40

unRheal

1-Jul-04 12:40

Hello,

I've been banging my head on the wall trying to figure this out.

I'm very new to Visual Studio (as you may be able to tell..)

I have a very simple C# program that calls a stored procedure on a sql server. The SP takes a bit of time, so I just wanted to do something to show that the user has actually hit the button and the system is busy processing.

I found a couple of excellent examples of what I could do here on the code project, but the problem is that no matter what I do, the system won't paint it on the screen until after the SP returns. (kind of defeating the purpose)

For simplicity, and to reduce the problem as far as possible, I even use a dummy SP that simply goes:

WAITFOR DELAY '00:00:10'<br />
select * from My_Table where Last_Name = 'Smith'

The select is just so that it'll return something, and I know it worked.

My C# program as I say is dead simple. I use sqlDataAdapter and Fill to fill my dataset.

Again to make things simple, I just put a label on my form with the text "Working.." in it and set it's visible property to false. When the user hits the run button, I set it to true and then do the Fill... but it doesn't display until after the SP returns, 10 seconds later, with the data. I just go:

label1.Visible = true;<br />
sqlDataAdapter1.Fill(dataSet11);

I found that I can at least set the cursor to hourglass.. but that seems inadequate (if the user slips the cursor off the window, it's no longer hourglass) as well as it seems like I should be able to do what I'm trying to do.

So.. well.. Any help or suggestions would be very much appreciated!

Thank you.

- Andrew

Re: Please help - can't update screen while waiting for SP in C#.

Colin Angus Mackay2-Jul-04 15:34

Colin Angus Mackay

2-Jul-04 15:34

Rheal_Notes wrote:
When the user hits the run button, I set it to true and then do the Fill... but it doesn't display until after the SP returns, 10 seconds later, with the data.

When you set the visible property the system will fire an event to say that the visible property has changed. However the events are only processed when the application is about to become idle, which your application is not about to do.

Off the top of my head: You could call DoEvents() after setting the visible property to get the paint event to get processed. Alternatively you could force a refresh by calling Refresh() on the form.

Does this help?

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

My Blog

Re: Please help - can't update screen while waiting for SP in C#.

unRheal2-Jul-04 16:39

unRheal

2-Jul-04 16:39

Colin,

Awesome! A most excellent answer. I tried them both and either one worked perfectly!

I cannot thank you enough, much appreciated!

Cheers,

- Andrew

Re: Please help - can't update screen while waiting for SP in C#.

Mike Dimmick4-Jul-04 6:52

Mike Dimmick

4-Jul-04 6:52

Aargh. Reentrancy is not a fun thing. Steer well clear of DoEvents - IMO, it's dangerous.

The OP should be using a function called by a worker thread to perform the SQL query, then use Control.Invoke to get back on to the UI thread to update the UI. You can use the thread pool in .NET by calling ThreadPool.QueueUserWorkItem, passing in a WaitCallback delegate.

I don't think what you say about events is true in .NET. It was in VB6, IIRC, but events are called directly in .NET. What the poster is seeing is simply that the thread isn't processing the Windows message queue (since it's blocked doing the stored procedure call) and so isn't getting the WM_PAINT message to repaint itself. If the user brings up a window that covers the UI and then brings this app up again, whole areas won't be redrawn on some operating systems (XP caches some current displays, but IIRC not invalid areas).

Leave the UI thread solely for user interaction - do all processing on another thread, unless you're sure that it cannot possibly take less than about 0.3 seconds. More than that is noticeable delay.

If you do need to update a control synchronously, call Update.

Stability. What an interesting concept. -- Chris Maunder

Re: Please help - can't update screen while waiting for SP in C#.

Colin Angus Mackay4-Jul-04 7:16

Colin Angus Mackay

4-Jul-04 7:16

Mike Dimmick wrote:
Steer well clear of DoEvents - IMO, it's dangerous

A few months ago someone shot me down for suggesting Refresh() and "suggested" DoEvents() instead. So, to play it safe I suggested both ways. I think Bart Simpson said it best "Your damned if you do and your damned if you don't"

However, I do agree that if there are other events waiting it can go off and do some weird stuff.

Mike Dimmick wrote:
I don't think what you say about events is true in .NET. It was in VB6

I've never used VB6 so I don't know. I come from an C++/MFC background.

Mike Dimmick wrote:
, but events are called directly in .NET. What the poster is seeing is simply that the thread isn't processing the Windows message queue

Looking back at my post, it would appear that I misused the terminology somewhat. But, like I said "events[*] are only processed when the application is about to become idle"

[*] events here are the ones that are fired off in the Application.Run method that come from the Windows Message Queue.

Re: Please help - can't update screen while waiting for SP in C#.

unRheal4-Jul-04 13:42

unRheal

4-Jul-04 13:42

Mike,

Thanks for your reply. I think that once I understand what you're trying to tell me, I'll be able to have a lot more fun.

I was looking at an example here on the The Code Project:
Proper Threading in Winforms .NET
By Shawn Cicoria
http://www.thecodeproject.com/csharp/winformthreading.asp

but I'm still having trouble wrapping my mind around it.

I don't think I have any trouble with the concept or anything, but the exact syntax and calling procedures are still confusing me.

I'll keep at it.. I'm definitely enjoying this Visual Studio thing. Smile | :)

While Colin's suggestion works (thanks again Colin) Smile | :)

.. I can still only display a static message... To the user, I don't think it will make any difference at all.. but I think it would be a lot more fun to put up an animated icon or busy-bar or something... and hey, if it's safer, and it'll make my code more re-usable down the line, all the more reason to keep playing. I'm sure I'll figure it out eventually.

In the mean time, any pointers to other sample code or tips or whatever are always much appreciated.

Thanks again to both of you.

Cheers

- Andrew

DTS Package

Member 11509011-Jul-04 2:41

Member 1150901

1-Jul-04 2:41

I have to transfer a data base from old to new one in batch.
I am working with the Data transformation services at the sql server.
How can i run/excute a dts package from a stored procedure or from the query analyzer? .
Thanks in advance

Be Good...
Dudi

Master in SQL please Help

irshu1-Jul-04 1:05

irshu

1-Jul-04 1:05

Any master in SQL Queries

Help: Complicated query

I have a table, as similar to this

ID Status Deleted

1 Repeat y
2 Repeat y
3 Repeat N
4 Repeat N
5 sUBMIT y
6 sUBMIT y
7 sUBMIT N
8 sUBMIT N
9 Pending Y
.
.
.
.

from the table like this i have to retrive distinct records where

delted = N

eg: in the above table after executing query result should be

ID Status Deleted

3 Repeat N
7 SUBMIT N
9 Pending Y

Re: Master in SQL please Help

Xiangyang Liu 刘向阳1-Jul-04 1:12

Xiangyang Liu 刘向阳

1-Jul-04 1:12

irshu wrote:
from the table like this i have to retrive distinct records where

delted = N

eg: in the above table after executing query result should be

ID Status Deleted

3 Repeat N
7 SUBMIT N
9 Pending Y

The query does not seem to be complicated, but it is not clear what you want. You said "deleted = N" and your result set has a record with "deleted = Y". Please explain.

My articles and software tools

Re: Master in SQL please Help

irshu1-Jul-04 18:53

irshu

1-Jul-04 18:53

say if i have records with same status field and with deleted ='N' and delted = 'Y' then i should select one record with deleted = 'N' and i should not select deleted = 'Y'
and say if i have records with the same status and with deleted ='y' and No records with delted='N' with the same status then i should select delted ='Y'

and one more thing in this is, ID is the primary Key

Re: Master in SQL please Help

Xiangyang Liu 刘向阳2-Jul-04 3:59

Xiangyang Liu 刘向阳

2-Jul-04 3:59

Wow, that is complicated, but not impossible. Here is the SQL statement that will get what you want (using union and nested query):

SELECT min(ID), Status, 'N'
FROM myTable
WHERE Status in (select distinct Status from myTable where Deleted = 'N')
GROUP BY Status

UNION

SELECT min(ID), Status, 'Y'
FROM myTable
WHERE Status in (select distinct Status from myTable where not Status in (select distinct Status from myTable where Deleted = 'N'))
GROUP BY Status;

My articles and software tools

Re: Master in SQL please Help

Xiangyang Liu 刘向阳2-Jul-04 4:03

Xiangyang Liu 刘向阳

2-Jul-04 4:03

Oops, the following is simpler:

<br />
SELECT min(ID), Status, 'N'<br />
FROM myTable<br />
WHERE Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status<br />
<br />
UNION <br />
<br />
SELECT min(ID), Status, 'Y'<br />
FROM myTable<br />
WHERE not Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status;<br />