|
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
|
|
|
|
|
I had two tables, for Table 1 fields are contactId,Str_name and for table 2 contactId,str_phonenumber,Str_phonetype
example :
Table 1:
111 Krishna
112 Ramesh
113 Kishore
Table 2:
111 8389368936398 M1
111 3535353535353 M2
111 6326326326262 L1
111 3263262626326 L2
Now i need like this 111 Krishna 8389368936398 6326326326262
how to join this I just need any of M1 or M2 and L1 or L2 and contactid and Name how to get this. Please anyone can help me i have been strucked with this problem
Thanks
|
|
|
|
|
You need to write a procedure for getting your required output
Regards
KP
|
|
|
|
|
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'
Most likely you want to use outer join if there are no matching rows in table2.
|
|
|
|
|
Thank u very much for ur suggestion which it works
|
|
|
|
|
You're welcome
|
|
|
|
|
sir when there is no matching rows in table2, i have to retrieve the names from table1 where str_phonenumber are null. I tried with outer join getting errors please suggest me how to do
Thanks for Your Help
|
|
|
|
|
Nath wrote: I tried with outer join getting errors please suggest me how to do
What is the query like, could you post it? Also what's the error?
|
|
|
|
|
error was cleared but is not displaying record which str_phonenumber values are null
|
|
|
|
|
I don't see why str_phonenumber would affect since it wasn't part of the join. Without seeing your query, it's quite impossible to say what's wrong with it.
|
|
|
|
|
by this query i am getting records with the phone numbers, but if any one phonenumber is null then that record was not displaying, if the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you. could you please help me regarding to this as i was new to this database
|
|
|
|
|
Nath wrote: the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you
Yes, it's possible. I wrote the query for you so that you can get to start. I'm asking you to post the modified query you have written (the one which has problems) simply because I want to see that you have put effort into this. It doesn't matter if your query isn't working and has problems. The main thing is that you've tried.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
