|
I think I'm either getting over my head, have complicated the issue beyond the scope of SQL, or maybe I have some gaps of knowledge missing for SQL queries. Irregardless, I will try to present the idea, my attempted SQL query, and the issue.
I have 7 tables I want to join (seriously).
- Table 1: ERAS - EraCode, Description
- Table 2: AGES - EraCode, AgeCode, Description
- Table 3: GROUPS - GroupCode, Description
- Table 4: CATEGORIES - GroupCode, CategoryCode, Description
- Table 5: SKILLS - GroupCode, CategoryCode, SkillCode, Description
- Table 6: SUBSKILLS - GroupCode, CategoryCode, SkillCode, SubSkillCode, Description
- Table 7: SKILLSINAGES - EraCode, AgeCode, GroupCode, CategoryCode, SkillCode, SubSkillCode
Assume these table have a one to many relationship on the fields that are common.
I created a successful query in Access that manages to get Table 3, 4, 5, and 6 together
<br />
SELECT s.Synopsis, ss.Synopsis, ss.Class, ss.Progression, s.Class, s.Progression, c.Progression, c.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode, cg.Description, c.Description, s.Description, ss.Description, ss.Table FROM ((CATEGORYGROUPS AS cg LEFT JOIN CATEGORY AS c ON cg.GroupCode = c.GroupCode) LEFT JOIN SKILLS AS s ON (c.CategoryCode = s.CategoryCode) AND (c.GroupCode = s.GroupCode)) LEFT JOIN SUBSKILLS AS ss ON (s.GroupCode = ss.GroupCode) AND (s.CategoryCode = ss.CategoryCode) AND (s.SkillCode = ss.SkillCode) ORDER BY cg.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode;";<br />
But now I need to add in more restrictions which include Table 1 and 2, therefore I created table 7 as the "relational table" if that is the correct term but I am having difficulty in applying it to my previous query. I though I could do this
<br />
SELECT s.Synopsis, ss.Synopsis, ss.Class, ss.Progression, s.Class, s.Progression, c.Progression, c.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode, cg.Description, c.Description, s.Description, ss.Description, ss.Table FROM (((CATEGORYGROUPS AS cg LEFT JOIN CATEGORY AS c ON cg.GroupCode = c.GroupCode) LEFT JOIN SKILLS AS s ON (c.CategoryCode = s.CategoryCode) AND (c.GroupCode = s.GroupCode)) LEFT JOIN SUBSKILLS AS ss ON (s.GroupCode = ss.GroupCode) AND (s.CategoryCode = ss.CategoryCode) AND (s.SkillCode = ss.SkillCode)) LEFT JOIN SKILLSINAGES sia ON (cg.GroupCode = sia.GroupCode) AND (c.CategoryCode = sia.CategoryCode) AND (s.SkillCode = sia.SkillCode) AND (ss.SubSkillCode = sia.SubSkillCode) WHERE sia.EraCode LIKE 'ERANAME' AND sia.AgeCode LIKE 'AGENAME' ORDER BY cg.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode;<br />
But I get an error in access that says the Join operation is not supported? Does anyone know what that means? (a completely different question) Does this design make sense?
Thanks!
|
|
|
|
|
Your database design reminds me of old main frame DB designs. They did not have random access to the data (reel to reel tapes) so they duplicated the data across a lot of tables.
I think you have to make better use of Primary Keys and stop the data duplication. First define your descrete entities. Find the attributes that unique to those entities - ignore the attributes that connect entities.
It is hard to give you a sample structure without knowing what the data describes but, things do not look correct.
As an Example: SKILLSINAGES contains an EraCode and an AgeCode. AGES also contains an EraCode. How or why would you keep them in sync? Maybe you need both because they can be different.
I think a better DB design would go a long way in eliminating your query problems.
|
|
|
|
|
the data simply describes skills a person may have where these skills are categorized into Groups, Categories, Skills and SubSkills. Also these skills could be different across time spans, so I have Era and Ages which are categories within an Era. So at the top we have an Era, within that an Age, within that Groups, and so on to Categories, Skills and Subskills.
I think I know what you mean however let me make sure I understand what you are proposing.
I could perhaps make ERAS just a table of Eras, same with AGES, GROUPS, CATEGORIES, SKILLS, and SUBSKILLS. Then define a table that combines the keys for those tables into one table called RELATIONALSKILLS or something. This table would be large, that is it's not improbable for there to be 1000's of records in it.
I think that makes sense, so then, let me ask another question. Say this implementation is correct. Now I make my Query to RELATIONALSKILLS, and I want to gain access to the ERA, AGES, etc tables. Would I still have to perform joins or is there a way to setup the database where I gain access to those fields automagically?
Thanks for the feedback. It may humiliating, but it shows I have a lot more to learn. I would rather make a strong relational table than have a application suffer through a clunky and weak designed Table. 
|
|
|
|
|
You have identified the need for a time-span. We can attempt to solve this task first. Ignore the rest of the problem right now.
Questions:
1) How many ERAS are there?
2) How many AGES per ERAS are there?
3) Do AGES overlap ERAS in time?
3) Do AGES descriptions repeat between ERAS?
4) Is this basically a fixed list once it is in production?
You may be over doing the relationship. You may only need one table to represent both ideas if the numbers are small enough and there is no overlap.
CalendarSpan
============
CalendarSpanId INTEGER --AutoNumber(Identity) primary Key
TimeOrder INTEGER --The order of when this Era/Age occurs (0=Earliest)
Era TEXT(30) --The Era
Age TEXT(30) --The Age within the Era
|
|
|
|
|
Well.. to answer
1) unknown, but currently this has only one record. A user has the ability to add more.
2) unknown, but currently there are only 11 ages per the one era.
3) No. Ages are defined to be a unique time span
4) It's possible to have an age that could exist in multiple eras but there wouldn't be a connection there other than the name of the age being the same.
5) No. I'm working on a interface to add, edit and delete.
Also, I defined a field to hold a "Code" version of the Description, that is I stripped all characters but alpha/numeric characters (such as periods, dashes, colons, etc). I did this for searching purposes. In your own personal opinion, is this worth it? or no.
|
|
|
|
|
I am assuming the ERACODE and AGECODE are Primary Keys that are never displayed to the user. If this is the case then your time-spans are defined correctly. You are not storing any sequence information but, that might not be important for you application.
Currently, you have 11 AGES records. If the count is not going to be enourmous (100+), I would just load them all into memory and use RegEx to do any fancy searches. I always try to never store calculated data - it can make your edit code more difficult. You can also create a query in Access that handles the calculation on the fly and query against that.
Currently, I would only remove ERACODE from the SKILLSINAGES since AGES already contains a link to it. It is unnecessary duplication. Of course, you will have to back it up with a unique constraint on the EARCODE + AGES.DESCRIPTION Columns. You don't want your users defining two AGES within the same ERAS that have the same DESCRIPTION.
Your next chain looks like this:
GROUPS
|----CATEGORIES
|----SKILLS
|----SUBSKILLS
Knowing the SUBSKILLS you should be able to get the SKILLS, CATEGORIES and GROUPS without a problem. There is no need to duplicate this information within each table.
Your SKILLSINAGES table becomes.
SKILLSINAGES
=================
SkillInAgesId - Primary Key
AgeCode
SubSkillCode
Some people would use AgeCode + SubSkillCode as the primary key. I have always liked PK's that have no meaning other than to uniquely identify the row. There are good arguements on both sides of the issue.
|
|
|
|
|
Well, unfortunately, there is more complication to SubSkills than one would think. I have a field in SubSkills called Table that is a Yes/No field. If marked then the SubSkill Code Field (these are just fields that work like SeqNum-autonumbers but they have more meaning than just some arbitrary number) will contain a user-defined table. We can return to this later.
In regards to your recommendations I have adjusted these tables as far as I think they can logically go.
ERAS - EraCode, Description, Synopsis
AGESISERAS - EraCode, AgeCode, Age, TimeOrder, Synopsis
GROUPS - GroupCode, Description
CATEGORIES - GroupCode, CategoryCode, Description, ...
SKILLS - SkillCode, Description, ...
SUBSKILLS - SkillCode, SubSkillCode, Description, ...
the elipse means there are more fields which are dependent on the Description field. I need to have a link to GroupCode from CategoryCode because there might be a Category that appears in two Groups, for example, the Category "General". So I just can't eliminate GroupCode from CATEGORIES since I can't be too sure I will grab the right Group. However, each skill should be unique per Category. I don't see how a person could categorize the same skill in two places. So I have made that definition to eliminate some columns there, however SubSkills needs to know what Skill because there could be a SubSkill that exists in two or more skills, for example, the user defined tables. If I have a Skill called Riding and another called Herding, both of these skills could in turn have the same subskills so I would be unsure what skill I am grabbing if I just looked at SubSkills.
In light of this I think I have gone down as far can, however i light of what you said I think the best I can do for SKILLSINAGES is the following
AgeCode
GroupCode
CategoryCode
SkillCode
SubSkillCode
Because I could have the same Group in different Ages, the same Category in different Groups and the same Subskill in different skills. Maybe there is something I am missing.
thanks for your help in this matter though. It is helping me see my database data a touch differently in light of connections, relations and such. 
side question: I could combine tables but wondering if that is also worth it. That is, take for example Groups and Categories. I could combine them so that I have
CATEGORYGROUPS: CategoryGroupID, Group, Category, (other fields here)
I could perform the same with Skills and SubSkills and get
SKILLS: SkillsID, Skill, SubSkill, (other fields here)
A forseeable issue is that not all Skills have SubSkills, so SubSkill may be blank. Does this seem like a logical solution and perhaps a better design? Thanks!
|
|
|
|
|
I think your problem is all the extra unnecessary parens you have. If you'll notice, each of your original joins only joins with a table one level deeper than the join. (For instance, ss is only joined to s, not to c or cg.)
But when you added the new join, you tried to join all the way back to the deepest level. (i.e. you're trying to join all the way back to cg.)
It's easier to visualize here:
SELECT
...
FROM
(
(
(
CATEGORYGROUPS AS cg
LEFT JOIN CATEGORY AS c
ON cg.GroupCode = c.GroupCode
)
LEFT JOIN SKILLS AS s
ON (c.CategoryCode = s.CategoryCode)
AND (c.GroupCode = s.GroupCode)
)
LEFT JOIN SUBSKILLS AS ss
ON (s.GroupCode = ss.GroupCode)
AND (s.CategoryCode = ss.CategoryCode)
AND (s.SkillCode = ss.SkillCode)
)
LEFT JOIN SKILLSINAGES sia
ON (cg.GroupCode = sia.GroupCode)
AND (c.CategoryCode = sia.CategoryCode)
AND (s.SkillCode = sia.SkillCode)
AND (ss.SubSkillCode = sia.SubSkillCode)
WHERE
...
I'm not sure about Access, because I haven't used it for years, but if it were on a SQL Server, I'd remove the parens, like this:
SELECT
...
FROM
CATEGORYGROUPS AS cg
LEFT JOIN CATEGORY AS c
ON cg.GroupCode = c.GroupCode
LEFT JOIN SKILLS AS s
ON (c.CategoryCode = s.CategoryCode)
AND (c.GroupCode = s.GroupCode)
LEFT JOIN SUBSKILLS AS ss
ON (s.GroupCode = ss.GroupCode)
AND (s.CategoryCode = ss.CategoryCode)
AND (s.SkillCode = ss.SkillCode)
LEFT JOIN SKILLSINAGES sia
ON (cg.GroupCode = sia.GroupCode)
AND (c.CategoryCode = sia.CategoryCode)
AND (s.SkillCode = sia.SkillCode)
AND (ss.SubSkillCode = sia.SubSkillCode)
WHERE
...
I think the deep nesting is confusing the parser.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Dear All,
In sql-server2000
In a table i am having a column of datatype varchar(8000).
While inserting the record through executenonquery, i am insert only
255 characters rest of the characters are getting trucated.
My question in how i will able to insert the row of that particular
column more than 255 characters
Thanx in advance.
Regards
|
|
|
|
|
It's not the insert that gets truncated, it's the select from the table.
In Query Analyser set the following option:
Tools->Options->Results->Maximum Characters Per Column=8000
By the way, you can see how much data is in the column by using the datalength() function.
|
|
|
|
|
Hi friends
This is my first post in the Forum.
How Can I Join more than two tables in SQL.
|
|
|
|
|
JawedVikia80 wrote:
How Can I Join more than two tables in SQL.
Same way as you join two tables, just keep using joins until you've joined all the tables you need.
For Example:
SELECT TableA.ID as AID, TableB.ID as BID, TableC.ID as CID
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.AID
INNER JOIN TableC ON TableB.ID = TableC.BID
This example supposes there are one-to-many relationships between TableA & TableB and also between TableB & TableC. And that TableB has a foreign key to tableA called AID, and also TableC has a foreign key to TableB called BID
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
|
|
|
|
|
Dear Friend There are many types of the Joins. which type of the join u want to use??
1-Inner join
2-Outer join
3-Self Join
You have example of inner join by our one friend if u need other 2's then reply..
Raj Khatri
|
|
|
|
|
Hi
Please dont get upset if u see this message in two places,
As it is bit urgent i have posted at two place one is at c# forums and another is at this place
How can i handle concurrency in DotNet?
I need to update a record into a database table, by the time I will access the record there is a chance that somebody can change the record.
I know this kind of problem is common.
But never i had an oppurtunity to work this kind of problem.
please let me know the solution.
|
|
|
|
|
Do a search in SQL Books Online for "transactions". More specifically:
begin transaction
commit transaction
and
rollback transaction
|
|
|
|
|
Also, assuming that you're using SQL Server 2000 (or MSDE) have a look at:
sp_getapplock
sp_releaseapplock
In most of the situations where our OLTP system has concurrency issues, I've found app locks to be infinitely more useful than transactions. Transactions (IMHO) should be used for atomicity, not for concurrency issues.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
This will work.....
But i am using Oracle and also i want to handle this at BLL but not in database.
|
|
|
|
|
Couple of Query questions:
1. is there a way to step through a query and view variables in the Anaylzer for MS SQL Server ?
2. I'm attempting to create a query that will be run dailly for the previous day's infomation, I'm having trouble getting the previous date in a usable format.
The following uis what i have so far:
DECLARE @Year int
DECLARE @Month int
DECLARE @Day int
DECLARE @FullDate varchar(10)
DECLARE @Yesterday datetime
SET @Yesterday=DATEADD(day,-1,GETDATE())
SET @Year = CAST(DATEPART(yyyy,@Yesterday) as varchar(4))
SET @Month = CAST(DATEPART(mm,@Yesterday) as varchar(2))
SET @Day = CAST(DATEPART(dd,@Yesterday) as varchar(2))
SET @FullDate=@Year+'-'+@Month+'-'+@Day
SELECT * FROM Document WHERE (ProcessDate=@FullDate)
Can someone point out where I'm going wrong ?
|
|
|
|
|
Couple of Answers:
1) Create a stored proc and debug it. You debug buy r-clicking on the stored proc in anaylzer and chosing debug. Just destroy the SP if you don't need it.
2) I believe that creating dates from strings is dependent upon SQL Server localization settings. What I answer here may not work exactly the same on your setup.
I have created a UDF to handle the removal (set to 12am) of time information from a DATETIME. \
CREATE FUNCTION DateOnly
(
@Test_DT DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @tmp DATETIME
SET @tmp = CAST(CONVERT(VARCHAR(20),@Test_DT,101) AS DATETIME)
RETURN @tmp
END
Given this function installed in your database, your query would look like this:
DECLARE @Yesterday DATETIME
SET @Yesterday = DATEADD(d,-1,dbo.DateOnly(GETDATE()))
SELECT * FROM Document WHERE ProcessDate = @Yeseterday
My SQL installation is set for American style dates. This may not work unchanged in your system. Check out the options in the CONVERT() function in Books-On-Line for what will work best in your locality.
|
|
|
|
|
Hi,
I have a problem. I try to load a table into a DataSet. The problem is that the rows that are recieved in the DataSet are duplicated. Say we have 3 records (1,2,3), I get in the DataSet 6 (1,2,3,1,2,3).
When I read using a DataReader the amount recieved is just fine.
Does anybody know what the problem may be?
Here is the code:
OleDbCommand commandNeType = new OleDbCommand("SELECT * FROM NeTypes WHERE Index = ?", m_connection);
commandNeType .Parameters.Add(new OleDbParameter(DB_NeTypes.FLD_INDEX, OleDbType.Integer, 0));
commandNeType .Parameters[DB_NeTypes.FLD_INDEX].Value = index;
OleDbDataAdapter daNeType = new OleDbDataAdapter(commandNeType);
try
{
daNeType.Fill(m_ne, TBL_NETYPES);
}
catch (Exception e)
{
closeConnection(ref m_connection);
return (int)ERR_DB.FILL_DATASET;
}
// create table
DataTable tableNeType = new DataTable();
tableNeType = m_ne.Tables[TBL_NETYPES];
now this: tableNeType.Rows.Count returns twice the amount of actual rows.
Thank you!
|
|
|
|
|
I dont think u will get double the amount..just try out...there is no way u can receive double the amount.
|
|
|
|
|
This should be a simple answer for most people, but its stumping me.
I need to query a table for all records that are between two dates, but my query staement return no values, can someone tell me whats wrong ?
SELECT * FROM MyTable WHERE (WorkValue=15 AND (ProcessDate>'5/19/2004' AND ProcessDate<'6/16/2004'))
|
|
|
|
|
I always put the dates in in ISO format. e.g. '2004-05-19'
Also, have you checked there is actually data for that date range with WorkValue=15
"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
|
|
|
|
|
Yes, I have also tried
SELECT * FROM Document WHERE (ProcessDate>'20040519' AND ProcessDate<'20040616') ORDER BY ProcessDate
and Yes there are plenty of records that fall within the requested date range.
|
|
|
|
|
Try SET DATEFORMAT ymd first. The US English default is MDY, i.e. '04/19/2004'; yours may be different though.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
