|
Michael Potter wrote:
Are you sure there is both id and custid in the customer table?
Yes you are right, There is no custid its just id in the customer table.
[code]
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where id in (
select distinct custid from pending
where custid <> ''
)
)
)
[/code]
|
|
|
|
|
Dear Michael,
So i modified my query like this
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where id in (
select distinct custid from pending
where custid <> ''
)
)
)
and your query.
SELECT pending.*
FROM pending
INNER JOIN customer cus ON (pending.custid = cus.id)
WHERE pending.custid <> '' AND
cus.status = 1 AND
cus.priority = (
SELECT max(priority)
FROM customer
WHERE id = pending.custid
)
But when in checked the execution plan my query was showing less percentage for all the counters there.
Where as your query is showing more.
So my doubt was, does my lazy query has any advantage or perfomance gain that i am not aware of, may be coz of not using joins.
btw still wondering how you guys are writing such optimized queries, actually i can program(c++) many complex issues, but still cannot optimize the SQL queries like that, is there any tips to get started ??
thanks
|
|
|
|
|
My experience is that joins usually take a shorter time then do in statements. It really depends upon the size of your datasets. There are few hard and fast rules that would work in all instances. Usually, I just follow my nose - If a query takes longer then I desire, I try a different approach or modify my indexes. For example, the following index may help my solution:
CREATE INDEX IX_CUSTOMER_ID_PRIORITY
ON customer
(
id,
priority
)
If the table is not large, than the index would be ignored and a simple table scan would be used.
The rule about not using * when you don't need every column is a rule that works in all instances.
|
|
|
|
|
I am guessing "id" is really custid since you are looking for custid in it.
Are you looking for the max(priority) for EACH customer (1) or the max(priority) for all customers in pending(2)?
1.
Select *
From(
Select custid, max(priority)as Priority
From customer
Where status = 1
and custid <> ''
Group by id
) as subtable Join pending
On subtable.custid= pending.custid
2.
SELECT *
FROM Pending Join Customer
on pending.custid = customer.custid
WHERE priority = (
SELECT max(priority)
FROM customer Join pending
on customer.custid = pending.custid
WHERE customer.custid <> ''
and status = 1
)
Where pending.custid <> ''
michanne
|
|
|
|
|
I am running SQL2000 on windows2003. I often connect to the database through the SQL Server Enterprise Manager on another machine through the company's intranet to administer it. The strange thing is sometimes I can connect to this database but other times cannot. THe error message is "SQL does not exist or not known to be running". I have checked the server status at the host terminal and it is running. I can even register with other database server residing on another machine.
Anyone knows what's the problem for such intermitent connection? Network prob? SQL registration at the client side? ODBC setting?
Thanks.
|
|
|
|
|
Is your login and the SQL Server system on the same domain? If not, you may have to preface the SQL server name with its domain name: TheDomain\TheSqlServer<\b>. Depending upon your previous network activity during the current login, Windows may not know where to look.
Just a thought.
|
|
|
|
|
Hi Micheal,
You are right my client terminal is in different domain from my host terminal. Do you know how can i find out in which domain my SqlServer lies? Is it found in the network setting on the host terminal.
Thanks.
|
|
|
|
|
On 2000 it can be found under "Network Identification". RClick [My Computer], choose properties.
|
|
|
|
|
hi micheal,
My host is in the same workgroup as my client but both are not join to any domain. I have found that by logging in to a shared folder in the host terminal I can succcessfully searched for my sqlserver. Looks like the client needs to establish the path to the host terminal first before it can locate the sqlserver.
Any advise how I can make do without the login yet still can locate the sqlserver?
Thank you.
|
|
|
|
|
I would think it would work the same as a domain.
Try:
<workgroup>\<server name="">
or
\\<workgroup>\<server name="">
If you fiddle around enough, you will find the required connection string.
|
|
|
|
|
Hi All
i have a sp which to querys a database, but each time the sp is run the name of one of the tables
changes, so i want to pass the table name into the query as a string
something like
DECLARE @table_name varchar(100)
set @table_name='mytable'
exec ('select my_id from ' + @table_name+' ORDER BY my_ID')
which works fine when im only using one table
i wanted to do a join across several tables, and do something like
set @sql_stmt = 'select callingnumber,seizedatetime,callednumber,new_action.telephone,new_action.action1,new_action.action2, new_call.student_id, datepart(dd,seizedatetime) as myday, null as sent_it
from new_call,new_action,'+@table_name+'
where new_call.call_ID=new_action.call_id'
EXEC ( @sql_stmt )
but that doesnt work
does ne1 have any ideas  
|
|
|
|
|
I don't see the error. What is the error msg?
You might try printing the result instead of exec right off the bat. Paste the result into another qa window and see what errors you get.
Hope that helps
michanne
|
|
|
|
|
Looks to me like you forget to put a statement in the WHERE clause to join @table_name to either new_call or new_action.
I can't be sure, but it also looks like you may have a whitespace problem.
Are you actually getting an error message, or are just just not getting the results you expected?
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Can anyone help me on how to change text field to ntext (ms-sql 2000), not using enterprise manager, rather programmatically ? or article that says it is not possible.
|
|
|
|
|
search the forum for your problem..
a similer problem was post in the last month as i recall..
I think there were no direct way ..
you must create the ntext column .. copy all data form the old text column to the new one .. and drop the old column..
|
|
|
|
|
ALTER TABLE tbl ALTER COLUMN col ntext
I think that ought to work, but I'm not completely sure.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
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!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
