Query Question
-
Hi, Suppose I have the following table:
CarID PartID
1 4
1 5
1 7
2 4
2 6
3 5
3 7
4 8Now 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
-
Hi, Suppose I have the following table:
CarID PartID
1 4
1 5
1 7
2 4
2 6
3 5
3 7
4 8Now 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 = 7If you want, you can also convert this to an exists structure or in-list operation, but I don't see any reason.
The need to optimize rises from a bad design.My articles[^]
-
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 = 7If you want, you can also convert this to an exists structure or in-list operation, but I don't see any reason.
The need to optimize rises from a bad design.My articles[^]
-
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)...The need to optimize rises from a bad design.My articles[^]
-
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)...The need to optimize rises from a bad design.My articles[^]
-
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.
The need to optimize rises from a bad design.My articles[^]
-
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.
The need to optimize rises from a bad design.My articles[^]
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.
-
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.
The need to optimize rises from a bad design.My articles[^]
-
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.
The need to optimize rises from a bad design.My articles[^]
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 5and 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 4Note 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 3I know I was looking for 3 parts, so I select carid 1 and 3. I guess these steps can go into one query.
-
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 5and 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 4Note 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 3I 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 need to optimize rises from a bad design.My articles[^]
-
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 need to optimize rises from a bad design.My articles[^]
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.
-
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.
The need to optimize rises from a bad design.My articles[^]
-
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.
The need to optimize rises from a bad design.My articles[^]
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.
-
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 :)
The need to optimize rises from a bad design.My articles[^]
-
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 :)
The need to optimize rises from a bad design.My articles[^]
-
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 = 7If you want, you can also convert this to an exists structure or in-list operation, but I don't see any reason.
The need to optimize rises from a bad design.My articles[^]
-
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.
The need to optimize rises from a bad design.My articles[^]