|
Tony_P wrote: Is subquerying the only solution
No it's not. I think there are several ways.
Depending on your indexing an inline view may be faster. Then it would be something like:
SELECT COUNT(*), SUM(price)
FROM (SELECT TOP 50 Price
FROM Orders
WHERE cust_id=3
ORDER BY date DESC) tableAlias
Check the execution plan to see which one is better in your case. Additionally add relevant indexes if you need.
|
|
|
|
|
Ha right I like this solution. I'm going to benchmark both and see how it goes. Thank you!
|
|
|
|
|
|
|
What's in ad.InsertCommand property? That should contain the insert statement to use.
|
|
|
|
|
Can you give me an example
|
|
|
|
|
Example 
hatan86 wrote: OleDbDataAdapter ad;
You have a variable named ad which is an instance of OleDbDataAdapter in your class. When you execute the Update method, you should have an insert statement in ad.InsertCommand property.
hatan86 wrote: OleDbException was unhandled
Syntax error in INSERT INTO statement
I've checked it and I can't find any syntax errors in it
You posted that you have checked the syntax of the insert command but have not found anything wrong in it. However, the insert statement is not visible in your post. Which statement did you check?
|
|
|
|
|
Hi to all,
I am working on a web project.The concept is based on online shopping cart.Please reply so that I can explain my problem.
thanx....
|
|
|
|
|
If I were to reply first without you explaining your problem yet would cause a paradox in the space-time continuum causing the entire universe to unravel.
So, you might want to explain your problem now and then people can reply and give you some proper advice.
|
|
|
|
|
aaaaaaaaaaaaaaaaaaaaaahhhhhhhhhhhhhhhhhhhhhhhh.......................I'm getting sucked into a vortex of stupid questions..................
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Jon_Boy wrote: I'm getting sucked into a vortex of stupid questions
Just make sure you don't pass the event horizon. If you do, you'll never leave!
|
|
|
|
|
|
And, if you want to talk about web applications then the database forum is probably not the best place for it. You might want to try the ASP.NET forum[^]
|
|
|
|
|
Please stop spamming the forums with this question. You have been told repeatedly that it is too vague to get an answer, and that this is the wrong forum.
|
|
|
|
|
Dummy..............wrong forum and you haven't provided any code for what you've attempted.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Hi,
Suppose I have the following table:
CarID PartID
1 4
1 5
1 7
2 4
2 6
3 5
3 7
4 8
Now I want to select each Car that has both parts 5 and 7 (which are cars 1 and 3), what would be the best query?
I started with some join, but saw it didn't give the right results.
This query should be able to run under heavy stress.
Thank you all
|
|
|
|
|
gnjunge wrote: I started with some join, but saw it didn't give the right results.
This query should be able to run under heavy stress.
I think join would be the easiest and propable a very efficient way. Just define the table twice, join with carid and for the first table, restrict with partid 5 and for the second table, restrict with partid 7. something like
...
from tablename alias1
inner join
tablename alias2
on alias1.carid = alias2.carid
where alias1.partid = 5
and alias2.partid = 7
If you want, you can also convert this to an exists structure or in-list operation, but I don't see any reason.
|
|
|
|
|
Thanks, but what happens if I have more than two parts (forgot to add that the number of parts is variable and can range between 1 and up).
|
|
|
|
|
gnjunge wrote: what happens if I have more than two parts
There are several ways to do this. One way is to use correlated exists-clause for each part. For example
...
from tablename
where tablename.partid = 5
and exists (select 1
from tablename sub1
where sub1.carid = tablename.carid
and sub1.partid = 7)
and exists (select 1
from tablename sub1
where sub1.carid = tablename.carid
and sub1.partid = 15)...
|
|
|
|
|
But the number of parts can vary. The solution you show is good in case there is a constant number of parts.
Is in my case the only solution a dynamic query?
|
|
|
|
|
If your part numbers are coming from the client, I think you will need the query to be dynamic since you have undefined number of parameters. In the previous example you would multiply the exists clause. It could also be modified to an in-list but still parameter amount is dynamic.
|
|
|
|
|
Actually just thought of something: select all cars that have at least one of the specific part ids, group on car and it's sum of parts and select only those that have the correct sum of parts. This way no dynamic sql is needed. Didn't try it yet so don't know if this is viable or fast enough.
|
|
|
|
|
I believe that would give you very different results. What if one car has part id's 1, 2 and 3 and another car has part id's 1 and 5. Both sum up to 6 but they have only one common part, part id 1.
|
|
|
|
|
I didn't mean the sum of the part ID's , but the number of parts (that were returned in the query) per car.
so if we have the following
carid partid
1 2
1 3
1 4
2 2
2 5
3 2
3 3
3 4
3 5
and i would be looking for cars with parts 2, 3, and 4. I would first select all the cars that have at least one of the 3 parts, so cars 1, 2 and 3:
carid partid
1 2
1 3
1 4
2 2
3 2
3 3
3 4
Note that car 2 has now only one record, and car 3 only has 3.
Then I count how many records per car:
carid count parts
1 3
2 1
3 3
I know I was looking for 3 parts, so I select carid 1 and 3.
I guess these steps can go into one query.
|
|
|
|
|
I see your point, but I understood the original question so that you need to know the cars that have some exact parts (like 2 and 3). Now you know that the car has part 2 but you're unable to say which other parts the car has, only that the count matches. I don't know your application logic so it may or may not be correct.
Is there some reason you want to avoid dynamic sql? The query would be very simple and performing well if you'd use dynamic statements.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
