|
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where custid in (
select distinct custid from pending
where custid <> ''
)
)
)
there is no constriants on both the tables.
|
|
|
|
|
Yup - remove Select * FROM
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
This looks a little cleaner. Are you sure there is both id and custid in the customer table? Something doesn't look quite right. You can also get rid of the correlated sub-query by placing it in the INNER JOIN.
<code>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
custid = cus.custid)
|
|
|
|
|
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!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
