|
I'm just loaded with questions. But you guys do give some the best help out there!
I have an SQL statement here:
<br />
SELECT *<br />
FROM ((((((SK_SKILLSINAGES AS sia INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON sia.GroupCode = cg.GroupCode) INNER JOIN SK_CATEGORY AS c ON (sia.CategoryCode = c.CategoryCode) AND (sia.GroupCode = c.GroupCode)) INNER JOIN SK_SKILLS AS s ON sia.SkillCode = s.SkillCode) LEFT JOIN SK_SUBSKILLS AS ss ON (sia.SkillCode = ss.SkillCode AND sia.SubSkillCode = ss.SubSkillCode) OR (sia.SkillCode = ss.SkillCode AND ss.Table = -1)) LEFT JOIN UnionQuery AS uq ON sia.SubSkillCode = uq.Code<br />
ORDER BY e.Description, ae.Description, cg.Description, c.Description, s.Description, ss.Description;<br />
This above works. However if I add the following
<br />
SELECT *<br />
FROM ((((((SK_SKILLSINAGES AS sia INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON sia.GroupCode = cg.GroupCode) INNER JOIN SK_CATEGORY AS c ON (sia.CategoryCode = c.CategoryCode) AND (sia.GroupCode = c.GroupCode)) INNER JOIN SK_SKILLS AS s ON sia.SkillCode = s.SkillCode) LEFT JOIN SK_SUBSKILLS AS ss ON (sia.SkillCode = ss.SkillCode AND sia.SubSkillCode = ss.SubSkillCode) OR (sia.SkillCode = ss.SkillCode AND ss.Table = -1)) LEFT JOIN UnionQuery AS uq ON sia.SubSkillCode = uq.Code AND uq.Table = ss.Table<br />
ORDER BY e.Description, ae.Description, cg.Description, c.Description, s.Description, ss.Description;<br />
This doesn't work. Anyone know why adding to the LEFT JOIN like that would break it?
Thanks!
|
|
|
|
|
brchris wrote:
This doesn't work
ok..what is the error message you get ?
|
|
|
|
|
hehe... sorry.
It tells Join expression not supported
|
|
|
|
|
You asked a similar question last week. Not sure if you've gone back and read all those responses, or whether any worked for you.
Last week's thread[^]
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Yeah... I realize that now. I was able to get around that one question but I guess I didn't see your response or at least remembered it when I reached this problem. You're most likely right, and there is a parsing error happening. I tried removing the parens but now I think Access is just confused even more. It's telling me I'm missing operators and what not.
What's funny here is that you could reverse the statements that are joined with the AND, that is, take the added statement and replace it with the statement which works and the query still holds. This is what got me. Why would both queries work by themselves but not together in an AND statement.
I'll keep plugging away. Hopefully I come up with something. If not, I'm hoping someone sees the needle in the haystack i have obviously overlooked. 
|
|
|
|
|
Is it possible to edit a pre-existing Query programmically in ADO? I see a SetQuery() method however I think that is meant more for runtime queries and commands and not to update the database itself. That is, I already have a query defined in my database as an SQL statement, and would like to update the SQL in the query, if necessary, through ADO.
Thanks!
|
|
|
|
|
Is it possible to list a field value as a value in an SQL query? For example.
instead of this
<br />
SQL.Format("SELECT * FROM TABLE1;");<br />
if(query(SQL) is ok)<br />
{<br />
GetField("DatabaseTable", Table);<br />
SQL.Format("SELECT * from %s;", Table);<br />
if(query(SQL) is ok)<br />
{<br />
...<br />
}<br />
}<br />
I want this
<br />
SQL.Format("SELECT * FROM [SELECT DatabaseTable FROM TABLE1] t1");<br />
if(query(SQL) is ok)<br />
{<br />
...<br />
}<br />
that is if I have a field in TABLE1 that has for its value a table name, how can use that table name automatically without parsing one SQL query for that field then making a whole new query on the DatabaseTable. Using a current database design doing what I am doing which is example 1, bogs down the program since it is making 100's of these calls. I would like to keep down the SQL queries if possible.
If not, does anyone know another possible solution? In a sense, I make probably 500 SQL calls in a single function thanks to this database design. If I can't come with a solution, then the database design needs to change so my SQL calls go down.
The reason this is, is because I allow a user to define custom tables to represent subskills in skills. That is they can define a skill with either a set list of subskills in a SUBSKILL table or define a table. I do this because I have some skills with 100's of subskills and separating the content seemed easier to tackle and handle in a database than shoving it all into the SUBSKILLS table.
Thanks!
|
|
|
|
|
brchris wrote:
Is it possible to list a field value as a value in an SQL query?
No you can't
brchris wrote:
If not, does anyone know another possible solution?
Use stored procedures. You can write a stored procedure to query the first table, create a string that contains the second query and return the result back - this will mean you only need to call the database once. It is still inefficient as the query optimiser can't pre-optimise a stored procedure that is executining a string as it has to parse the string at runtime, rather than at compile time. But at least it will cut down on database calls and network roundtrips and so on.
brchris wrote:
I make probably 500 SQL calls in a single function thanks to this database design
You could extend stored procedure approach, if possible, and do everything from one call. Of course, your database design may not allow it, but it would be worth seeing if there is anything that could be suitably grouped as stored procedures are much more efficient and have added security benefits (you can deny direct access to tables and route all queries/inserts/updates/deletes through stored procedures, which means you always know what queries are being executed)
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
|
|
|
|
|
In a sense. I never heard of Stored Procedures until now. Pretty cool stuff. I looked into and it seems I can't create them using what I have currently. I read in the help that I need to be connected to a MS SQL Server which I am not. I tried to test it with this SQL statement
<br />
"CREATE PROC procProductsList AS SELECT * FROM SK_SKILLS;"
which didn't work. I may not be able to create Stored Procedures. Or else maybe I didn't do enough research. 
A possible workaround solution (if I can't find how to make Store Procedures in Access XP) could be to make a query in Access that combines all my userdefined tables and I just join this table with my SQL query in ADO
thanks for the tip! 
|
|
|
|
|
Hi
I installed MySQL 4.0.16 on windows.
the manual says I should run mysql_install_db to initialize the mysql database containing the grant tables.
now in the scripts folder there are : mysql_install_db and mysql_install_db.sh files..
both are not executables as you see..
now what should I install to run these scripts ? is it Perl or what ?
|
|
|
|
|
I believe that instruction is for linux. Those db's should already be installed. Look for section 2.4.1 in the manual.
michanne
|
|
|
|
|
thank you..
If it's ment by (grant tables) the MySQL DB and it's tables (user,db,...) then maybe you are right..they were installed automatically..
but in this case why are these scripts included with the binaries ?
|
|
|
|
|
your welcome
yes - it is for the grant tables. I suppose you might need it if something happened to that db.
michanne
|
|
|
|
|
hspc wrote:
but in this case why are these scripts included with the binaries ?
Welcome to the wonderful world of open-source, cross-platform development.
Most likely, whoever did the packaging is more familiar with the Linux distro than the Win32 distro, and didn't know which pieces did and didn't need to be in the Win32 distro, so they were all thrown in.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi all, I have a DataGrid and two text controls bound to the same source. I have no problem changing the CurrencyManager position to navigate through the data. However, if I bind my DataGrid to a DataView, the position changing routines no longer work on the grid.
please help me.
|
|
|
|
|
Hello Friends,
I know that why subqueries used, but that work we want to use from subqueries same we can do from signle query, then why it is important to use subqueries in oracle??
tell me good example...
Raj Khatri
|
|
|
|
|
Using SQL Server & Stored Procedures...
I am getting a bad RecordSet as a result of an Execute() call. Upon attempting to use the RecordSet, and checking EOF I am getting an HRESULT of 0x800A0E78 ("operation not allowed when object is closed")
Upon checking the database to see if the data made it -- it did. So the stored procedure looks OK. Also I tested the stored procedure from the "SQL Query Analyzer" and it was returning the expected data -- a single row with a single field.
Any clues out there to what could be causing this? I know it's hard without seeing code, but any hints about common issues would be great.
Thanks in advance
-peter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br />
Peter Weyzen<br />
Staff Engineer<br />
<A HREF="http://www.santacruznetworks.com">Santa Cruz Networks</A>
|
|
|
|
|
When a stored procedure does other things before returning a set of records, it may be interpreted as returning multiple recordsets by the code. When I worked with stored procedures a lot, I used to put a SET NOCOUNT ON line at the top of the stored procedure to fool it into thinking there was only one recordset.
That was a long time ago, so I do not know if it still works with today's drivers/providers.
my blog
|
|
|
|
|
Thank you so much -- that was it.
My stored procedures "do a few things"... and adding the "SET NOCOUNT ON" made em work!
Thanks
and Regards
-peter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br />
Peter Weyzen<br />
Staff Engineer<br />
<A HREF="http://www.santacruznetworks.com">Santa Cruz Networks</A>
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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.
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! 
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
