Database

4-Jul-04 14:59

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!

Hesham Amin4-Jul-04 22:50

Hesham Amin

4-Jul-04 22:50

brchris wrote:
This doesn't work

ok..what is the error message you get ?

brdavid6-Jul-04 4:49

6-Jul-04 4:49

hehe... sorry.
It tells Join expression not supported

Grimolfr6-Jul-04 9:58

Grimolfr

6-Jul-04 9:58

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

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

brdavid6-Jul-04 14:00

Editing Pre-existing Queries

6-Jul-04 14:00

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. Confused | :confused:

brdavid4-Jul-04 14:56

4-Jul-04 14:56

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!

Variable in SQL Query

brdavid4-Jul-04 2:01

Re: Variable in SQL Query

4-Jul-04 2:01

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!

Colin Angus Mackay4-Jul-04 2:27

Colin Angus Mackay

4-Jul-04 2:27

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

Re: Variable in SQL Query

brdavid4-Jul-04 5:46

running mysql_install_db on windows

4-Jul-04 5:46

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. Wink | ;)

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! Smile | :)

Hesham Amin4-Jul-04 0:43

Hesham Amin

4-Jul-04 0:43

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 ?

michanne4-Jul-04 18:00

michanne

4-Jul-04 18:00

I believe that instruction is for linux. Those db's should already be installed. Look for section 2.4.1 in the manual.

michanne

Hesham Amin4-Jul-04 22:23

Hesham Amin

4-Jul-04 22:23

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 ?

michanne15-Jul-04 6:29

michanne1

5-Jul-04 6:29

your welcome
yes - it is for the grant tables. I suppose you might need it if something happened to that db.

michanne

What is the main advantage of subqueries?

Grimolfr6-Jul-04 10:05

Grimolfr

6-Jul-04 10:05

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

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

dataview

Member 11412663-Jul-04 0:41

Member 1141266

3-Jul-04 0:41

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.

wgdesigner2-Jul-04 22:31

wgdesigner

2-Jul-04 22:31

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

Stored Procedure call yields RecordSet error

Peter Weyzen2-Jul-04 8:29

Peter Weyzen

2-Jul-04 8:29

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>

Re: Stored Procedure call yields RecordSet error

Steven Campbell2-Jul-04 11:19

Steven Campbell

2-Jul-04 11:19

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

Re: Stored Procedure call yields RecordSet error

Peter Weyzen2-Jul-04 11:52

Peter Weyzen

2-Jul-04 11:52

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>

Could use an extra brain...

brdavid2-Jul-04 6:31

Re: Could use an extra brain...

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!

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