|
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.
|
|
|
|
|
The app is a matching app. So you could also change car and part to article and word, in which i want to find all articles that have at least some (1 or more) given words.
No specific reason again dynamic sql, but always want to know the best way before resorting to dynamic sql which is less maintainable then pure sql.
|
|
|
|
|
Hmm, let's take the car and part example. What happens if you use only one partid and then take the count as you proposed. For example if you have in the database:
carid partid
1 1
1 2
1 3
2 2
2 3
2 4
and if, without knowing what's in the database, use either partid 1 or 4 (user defines all partid's 1-4). In the previous case, carid 2 is eliminated and in the latter carid 1 is eliminated. Or perhaps I didn't understand the requirement.
|
|
|
|
|
Not sure if you understood the requirement. In the car case the question/query would be: give me all cars that have at least partid('s) x,y,z (in which x,y,z can be also only one specific part or a number of specific parts). Or in the article/word case: give me all articles that have at least the following words in them.
|
|
|
|
|
gnjunge wrote: give me all articles that have at least the following words in them
In that case I think your solution should work.
gnjunge wrote: Not sure if you understood the requirement
Obviously I didn't. I think I got confused because in the original example you wrote "select each Car that has both parts 5 and 7". That's why I tried to use all of the parameters with AND.
However, it doesn't matter since the main point is that the query you now have works 
|
|
|
|
|
Thanks for all the help in brainstorming. The solution works perfect!
|
|
|
|
|
|
I'd probably just use an IN statement because I am lazy and fear of carpal tunnel
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'd probably just use an IN statement
I thought of IN in the first place, but there are two differences if you use it. It's considered as an OR structure so if every part must exists, I think it wouldn't be usable. Also if you use IN directly on the table you would get the same car repeatedly if it has several part's that are defined in in-list.
But that was based my original interpretation on the question which later was found out to be wrong.
|
|
|
|
|
Hello,
How can I escape new line or "\n" from a string in select query?
Thanking in Advance
Johnny
|
|
|
|
|
|
 Hi all,
I want to split the ~ symbol from a field.so i wrote a split function ..it is wrking correctly when i give
SELECT * from [dbo].[FN_Split]('1~2','~')
1 and 2 splits correctly..
How to use the split function in a sub query??
st_id contains 1~7
SELECT * from [dbo].[FN_Split](select st_id from st_config ,'~'))
it shows the below Errors:
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
pls give me any solution ..
|
|
|
|
|
You are missing an opening bracket. And it'll die if you ever get more than one row from st_config.
|
|
|
|
|
We gave The brackets but alos it returns the same error
|
|
|
|
|
This is because of the fact that when you execute "select st_id from st_config", it gives a records set. In that case your function needs to be called for each and every rows which is not happening at present.
So you are getting this error.
Do I thing, use a loop(while) or Cursor and store the "st_id" values in a varchar variable. Next pass the variable to your split fucntion and your job will be done.
But in that case, you will get multiple records(if your original table has many columns).
Else you can do 1 more thing. While you are looping, make a string of all the values from "st_id" and place in a varchar variable.
e.g. st_id
1~2
3~4
5~6
Make it like set newstring = 1~2~3~4~5~6 (where newstring is of varchar type)
Next execute SELECT * from [dbo].[FN_Split](newstring,'~')
Hope this helps
Niladri Biswas
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
