Need Help SQL query

Hi,

I have to search in database in which structure of table is like this:-

------------------------------------------------------------ ----------------------------------
Col 1(Foreign Key) Col 2 Col3 Col4(Primary
Key)
------------------------------------------------------------ ----------------------------------
1 fruit apple
1
1 fruit mango
2
1 fruit banana
3

2 vegetable potato
4
2 vegetable tomato
5
............................................................ ........................................

Now I have to write a query to find out Col1 if Col2 is "fruit" and
Col3 contains only and only those elements that are in "where"
parameter.


Thanks in advance
Regards,
Rahul

Re: Need Help SQL query

rahulbindlish@gmail.com wrote:
> Hi,
>
> I have to search in database

What database? Type and version please. This is almost always relevant when
requesting help with queries.

> in which structure of table is like
> this:-
>
> ------------------------------------------------------------ ----------------------------------
> Col 1(Foreign Key) Col 2 Col3 Col4(Primary
> Key)
> ------------------------------------------------------------ ----------------------------------
> 1 fruit apple 1
> 1 fruit mango 2
> 1 fruit banana 3
> 2 vegetable potato 4
> 2 vegetable tomato 5
> ............................................................ .......................................
>
> Now I have to write a query to find out Col1 if Col2 is "fruit" and
> Col3 contains only and only those elements that are in "where"
> parameter.
>
>
This seems pretty easy so I must be misunderstanding your requirements.
However:

Select Distinct Col1 From table
Where Col2='fruit' and Col3='apple'

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Need Help SQL query

Bob Barrows [MVP] wrote:

> rahulbindlish@gmail.com wrote:
> > Hi,
> >
> > I have to search in database
>
> What database? Type and version please. This is almost always relevant when
> requesting help with queries.
>
> > in which structure of table is like
> > this:-
> >
> > ------------------------------------------------------------ ----------------------------------
> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
> > Key)
> > ------------------------------------------------------------ ----------------------------------
> > 1 fruit apple 1
> > 1 fruit mango 2
> > 1 fruit banana 3
> > 2 vegetable potato 4
> > 2 vegetable tomato 5
> > ............................................................ .......................................
> >
> > Now I have to write a query to find out Col1 if Col2 is "fruit" and
> > Col3 contains only and only those elements that are in "where"
> > parameter.
> >
> >
> This seems pretty easy so I must be misunderstanding your requirements.
> However:
>
> Select Distinct Col1 From table
> Where Col2='fruit' and Col3='apple'
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Hi,

Sorry for too much late reply.
I am going to develop my application independent of database but you
are not getting my point , It is due to my writing convention

------------------------------------------------------------ ----------------------------------
Col 1(Foreign Key) Col 2 Col3 Col4(Primary
Key)
------------------------------------------------------------ ----------------------------------
1 fruit apple 1
1 fruit mango 2
1 fruit banana 3
2 vegetable potato 4
2 vegetable tomato 5
3 fruit apple 6
3 fruit papaya 7

............................................................ ........................................
I try to again explain it

I have to write a query in which I have given category (col2 )"fruit"
and different values(col3 )("apple,papaya") now I have to find out
where it is exactly matching and finally return (col1) for eg. now
output will be 3 not 1 in this case

Thanks and Regards,
Rahul

Re: Need Help SQL query

On 13 Jun 2006 22:38:59 -0700, rahulbindlish@gmail.com wrote:

>
>Bob Barrows [MVP] wrote:
>
>> rahulbindlish@gmail.com wrote:
>> > Hi,
>> >
>> > I have to search in database
>>
>> What database? Type and version please. This is almost always relevant when
>> requesting help with queries.
>>
>> > in which structure of table is like
>> > this:-
>> >
>> > ------------------------------------------------------------ ----------------------------------
>> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
>> > Key)
>> > ------------------------------------------------------------ ----------------------------------
>> > 1 fruit apple 1
>> > 1 fruit mango 2
>> > 1 fruit banana 3
>> > 2 vegetable potato 4
>> > 2 vegetable tomato 5
>> > ............................................................ .......................................
>> >
>> > Now I have to write a query to find out Col1 if Col2 is "fruit" and
>> > Col3 contains only and only those elements that are in "where"
>> > parameter.
>> >
>> >
>> This seems pretty easy so I must be misunderstanding your requirements.
>> However:
>>
>> Select Distinct Col1 From table
>> Where Col2='fruit' and Col3='apple'
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so I
>> don't check it very often. If you must reply off-line, then remove the
>> "NO SPAM"
>
>Hi,
>
>Sorry for too much late reply.
>I am going to develop my application independent of database but you
>are not getting my point , It is due to my writing convention
>
>----------------------------------------------------------- -----------------------------------
> Col 1(Foreign Key) Col 2 Col3 Col4(Primary
>Key)
>----------------------------------------------------------- -----------------------------------
> 1 fruit apple 1
> 1 fruit mango 2
> 1 fruit banana 3
> 2 vegetable potato 4
> 2 vegetable tomato 5
>3 fruit apple 6
>3 fruit papaya 7
>
>........................................................... ........................................
>I try to again explain it
>
>I have to write a query in which I have given category (col2 )"fruit"
>and different values(col3 )("apple,papaya") now I have to find out
>where it is exactly matching and finally return (col1) for eg. now
>output will be 3 not 1 in this case
>
>Thanks and Regards,
>Rahul

Perhaps,

Select Col1 from table where (Col3 = 'apple' or Col3 = 'papaya');

To be very precise and perhaps make it more efficient ( depending on the data model) you can use:

Select Col1 from table where Col2 = 'fruit' and (Col3 = 'apple' or Col3 = 'papaya');

Depending on the database ( that's why it is important) you may be able to use:

Select Col1 from table where Col2 = 'fruit' and Col3 IN ('apple', 'papaya');

Hope it helps..

Re: Need Help SQL query

Turkbear wrote:

> On 13 Jun 2006 22:38:59 -0700, rahulbindlish@gmail.com wrote:
>
> >
> >Bob Barrows [MVP] wrote:
> >
> >> rahulbindlish@gmail.com wrote:
> >> > Hi,
> >> >
> >> > I have to search in database
> >>
> >> What database? Type and version please. This is almost always relevant when
> >> requesting help with queries.
> >>
> >> > in which structure of table is like
> >> > this:-
> >> >
> >> > ------------------------------------------------------------ ----------------------------------
> >> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
> >> > Key)
> >> > ------------------------------------------------------------ ----------------------------------
> >> > 1 fruit apple 1
> >> > 1 fruit mango 2
> >> > 1 fruit banana 3
> >> > 2 vegetable potato 4
> >> > 2 vegetable tomato 5
> >> > ............................................................ .......................................
> >> >
> >> > Now I have to write a query to find out Col1 if Col2 is "fruit" and
> >> > Col3 contains only and only those elements that are in "where"
> >> > parameter.
> >> >
> >> >
> >> This seems pretty easy so I must be misunderstanding your requirements.
> >> However:
> >>
> >> Select Distinct Col1 From table
> >> Where Col2='fruit' and Col3='apple'
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so I
> >> don't check it very often. If you must reply off-line, then remove the
> >> "NO SPAM"
> >
> >Hi,
> >
> >Sorry for too much late reply.
> >I am going to develop my application independent of database but you
> >are not getting my point , It is due to my writing convention
> >
> >----------------------------------------------------------- -----------------------------------
> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
> >Key)
> >----------------------------------------------------------- -----------------------------------
> > 1 fruit apple 1
> > 1 fruit mango 2
> > 1 fruit banana 3
> > 2 vegetable potato 4
> > 2 vegetable tomato 5
> >3 fruit apple 6
> >3 fruit papaya 7
> >
> >........................................................... ........................................
> >I try to again explain it
> >
> >I have to write a query in which I have given category (col2 )"fruit"
> >and different values(col3 )("apple,papaya") now I have to find out
> >where it is exactly matching and finally return (col1) for eg. now
> >output will be 3 not 1 in this case
> >
> >Thanks and Regards,
> >Rahul
>
> Perhaps,
>
> Select Col1 from table where (Col3 = 'apple' or Col3 = 'papaya');
>
> To be very precise and perhaps make it more efficient ( depending on the data model) you can use:
>
> Select Col1 from table where Col2 = 'fruit' and (Col3 = 'apple' or Col3 = 'papaya');
>
> Depending on the database ( that's why it is important) you may be able to use:
>
> Select Col1 from table where Col2 = 'fruit' and Col3 IN ('apple', 'papaya');
>
> Hope it helps..


Hi,
There can be any no. of values in Col3 so we will have to match all
values of col3 within table that are not fixed so Is there any
solution??????????????

Rahul

Re: Need Help SQL query

On 14 Jun 2006 09:37:10 -0700, rahulbindlish@gmail.com wrote:

>
>Turkbear wrote:
>
>> On 13 Jun 2006 22:38:59 -0700, rahulbindlish@gmail.com wrote:
>>
>> >
>> >Bob Barrows [MVP] wrote:
>> >
>> >> rahulbindlish@gmail.com wrote:
>> >> > Hi,
>> >> >
>> >> > I have to search in database
>> >>
>> >> What database? Type and version please. This is almost always relevant when
>> >> requesting help with queries.
>> >>
>> >> > in which structure of table is like
>> >> > this:-
>> >> >
>> >> > ------------------------------------------------------------ ----------------------------------
>> >> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
>> >> > Key)
>> >> > ------------------------------------------------------------ ----------------------------------
>> >> > 1 fruit apple 1
>> >> > 1 fruit mango 2
>> >> > 1 fruit banana 3
>> >> > 2 vegetable potato 4
>> >> > 2 vegetable tomato 5
>> >> > ............................................................ .......................................
>> >> >
>> >> > Now I have to write a query to find out Col1 if Col2 is "fruit" and
>> >> > Col3 contains only and only those elements that are in "where"
>> >> > parameter.
>> >> >
>> >> >
>> >> This seems pretty easy so I must be misunderstanding your requirements.
>> >> However:
>> >>
>> >> Select Distinct Col1 From table
>> >> Where Col2='fruit' and Col3='apple'
>> >>
>> >> --
>> >> Microsoft MVP - ASP/ASP.NET
>> >> Please reply to the newsgroup. This email account is my spam trap so I
>> >> don't check it very often. If you must reply off-line, then remove the
>> >> "NO SPAM"
>> >
>> >Hi,
>> >
>> >Sorry for too much late reply.
>> >I am going to develop my application independent of database but you
>> >are not getting my point , It is due to my writing convention
>> >
>> >----------------------------------------------------------- -----------------------------------
>> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
>> >Key)
>> >----------------------------------------------------------- -----------------------------------
>> > 1 fruit apple 1
>> > 1 fruit mango 2
>> > 1 fruit banana 3
>> > 2 vegetable potato 4
>> > 2 vegetable tomato 5
>> >3 fruit apple 6
>> >3 fruit papaya 7
>> >
>> >........................................................... ........................................
>> >I try to again explain it
>> >
>> >I have to write a query in which I have given category (col2 )"fruit"
>> >and different values(col3 )("apple,papaya") now I have to find out
>> >where it is exactly matching and finally return (col1) for eg. now
>> >output will be 3 not 1 in this case
>> >
>> >Thanks and Regards,
>> >Rahul
>>
>> Perhaps,
>>
>> Select Col1 from table where (Col3 = 'apple' or Col3 = 'papaya');
>>
>> To be very precise and perhaps make it more efficient ( depending on the data model) you can use:
>>
>> Select Col1 from table where Col2 = 'fruit' and (Col3 = 'apple' or Col3 = 'papaya');
>>
>> Depending on the database ( that's why it is important) you may be able to use:
>>
>> Select Col1 from table where Col2 = 'fruit' and Col3 IN ('apple', 'papaya');
>>
>> Hope it helps..
>
>
>Hi,
>There can be any no. of values in Col3 so we will have to match all
>values of col3 within table that are not fixed so Is there any
>solution??????????????
>
>Rahul


How are you going to specify the Col3 values ? - your post indicated that you knew which values for col3 you wanted.

Explain what yopu mean by ' all values of col3 within table that are not fixed' - what fix?

Re: Need Help SQL query

Turkbear wrote:

> On 14 Jun 2006 09:37:10 -0700, rahulbindlish@gmail.com wrote:
>
> >
> >Turkbear wrote:
> >
> >> On 13 Jun 2006 22:38:59 -0700, rahulbindlish@gmail.com wrote:
> >>
> >> >
> >> >Bob Barrows [MVP] wrote:
> >> >
> >> >> rahulbindlish@gmail.com wrote:
> >> >> > Hi,
> >> >> >
> >> >> > I have to search in database
> >> >>
> >> >> What database? Type and version please. This is almost always relevant when
> >> >> requesting help with queries.
> >> >>
> >> >> > in which structure of table is like
> >> >> > this:-
> >> >> >
> >> >> > ------------------------------------------------------------ ----------------------------------
> >> >> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
> >> >> > Key)
> >> >> > ------------------------------------------------------------ ----------------------------------
> >> >> > 1 fruit apple 1
> >> >> > 1 fruit mango 2
> >> >> > 1 fruit banana 3
> >> >> > 2 vegetable potato 4
> >> >> > 2 vegetable tomato 5
> >> >> > ............................................................ .......................................
> >> >> >
> >> >> > Now I have to write a query to find out Col1 if Col2 is "fruit" and
> >> >> > Col3 contains only and only those elements that are in "where"
> >> >> > parameter.
> >> >> >
> >> >> >
> >> >> This seems pretty easy so I must be misunderstanding your requirements.
> >> >> However:
> >> >>
> >> >> Select Distinct Col1 From table
> >> >> Where Col2='fruit' and Col3='apple'
> >> >>
> >> >> --
> >> >> Microsoft MVP - ASP/ASP.NET
> >> >> Please reply to the newsgroup. This email account is my spam trap so I
> >> >> don't check it very often. If you must reply off-line, then remove the
> >> >> "NO SPAM"
> >> >
> >> >Hi,
> >> >
> >> >Sorry for too much late reply.
> >> >I am going to develop my application independent of database but you
> >> >are not getting my point , It is due to my writing convention
> >> >
> >> >----------------------------------------------------------- -----------------------------------
> >> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
> >> >Key)
> >> >----------------------------------------------------------- -----------------------------------
> >> > 1 fruit apple 1
> >> > 1 fruit mango 2
> >> > 1 fruit banana 3
> >> > 2 vegetable potato 4
> >> > 2 vegetable tomato 5
> >> >3 fruit apple 6
> >> >3 fruit papaya 7
> >> >
> >> >........................................................... ........................................
> >> >I try to again explain it
> >> >
> >> >I have to write a query in which I have given category (col2 )"fruit"
> >> >and different values(col3 )("apple,papaya") now I have to find out
> >> >where it is exactly matching and finally return (col1) for eg. now
> >> >output will be 3 not 1 in this case
> >> >
> >> >Thanks and Regards,
> >> >Rahul
> >>
> >> Perhaps,
> >>
> >> Select Col1 from table where (Col3 = 'apple' or Col3 = 'papaya');
> >>
> >> To be very precise and perhaps make it more efficient ( depending on the data model) you can use:
> >>
> >> Select Col1 from table where Col2 = 'fruit' and (Col3 = 'apple' or Col3 = 'papaya');
> >>
> >> Depending on the database ( that's why it is important) you may be able to use:
> >>
> >> Select Col1 from table where Col2 = 'fruit' and Col3 IN ('apple', 'papaya');
> >>
> >> Hope it helps..
> >
> >
> >Hi,
> >There can be any no. of values in Col3 so we will have to match all
> >values of col3 within table that are not fixed so Is there any
> >solution??????????????
> >
> >Rahul
>
>
> How are you going to specify the Col3 values ? - your post indicated that you knew which values for col3 you wanted.

> Explain what yopu mean by ' all values of col3 within table that are not fixed' - what fix?

Hi,
The values which I have to match against Col3, are not fixed so they
may vary in that case we can't hardcode the values.

Rahul

Re: Need Help SQL query

On 14 Jun 2006 22:13:11 -0700, rahulbindlish@gmail.com wrote:

>
>Turkbear wrote:
>
>> On 14 Jun 2006 09:37:10 -0700, rahulbindlish@gmail.com wrote:
>>
>> >
>> >Turkbear wrote:
>> >
>> >> On 13 Jun 2006 22:38:59 -0700, rahulbindlish@gmail.com wrote:
>> >>
>> >> >
>> >> >Bob Barrows [MVP] wrote:
>> >> >
>SNIPPED
>> >> >Sorry for too much late reply.
>> >> >I am going to develop my application independent of database but you
>> >> >are not getting my point , It is due to my writing convention
>> >> >
>> >> >----------------------------------------------------------- -----------------------------------
>> >> > Col 1(Foreign Key) Col 2 Col3 Col4(Primary
>> >> >Key)
>> >> >----------------------------------------------------------- -----------------------------------
>> >> > 1 fruit apple 1
>> >> > 1 fruit mango 2
>> >> > 1 fruit banana 3
>> >> > 2 vegetable potato 4
>> >> > 2 vegetable tomato 5
>> >> >3 fruit apple 6
>> >> >3 fruit papaya 7
>> >> >
>> >> >........................................................... ........................................
>> >> >I try to again explain it
>> >> >
>> >> >I have to write a query in which I have given category (col2 )"fruit"
>> >> >and different values(col3 )("apple,papaya") now I have to find out
>> >> >where it is exactly matching and finally return (col1) for eg. now
>> >> >output will be 3 not 1 in this case
>> >> >
>> >> >Thanks and Regards,
>> >> >Rahul
>> >>
>> >> Perhaps,
>> >>
>> >> Select Col1 from table where (Col3 = 'apple' or Col3 = 'papaya');
>> >>
>> >> To be very precise and perhaps make it more efficient ( depending on the data model) you can use:
>> >>
>> >> Select Col1 from table where Col2 = 'fruit' and (Col3 = 'apple' or Col3 = 'papaya');
>> >>
>> >> Depending on the database ( that's why it is important) you may be able to use:
>> >>
>> >> Select Col1 from table where Col2 = 'fruit' and Col3 IN ('apple', 'papaya');
>> >>
>> >> Hope it helps..
>> >
>> >
>> >Hi,
>> >There can be any no. of values in Col3 so we will have to match all
>> >values of col3 within table that are not fixed so Is there any
>> >solution??????????????
>> >
>> >Rahul
>>
>>
>> How are you going to specify the Col3 values ? - your post indicated that you knew which values for col3 you wanted.
>
>> Explain what yopu mean by ' all values of col3 within table that are not fixed' - what fix?
>
>Hi,
>The values which I have to match against Col3, are not fixed so they
>may vary in that case we can't hardcode the values.
>
>Rahul


Not hard-coded, but rewritten depending on the need - You cannot match data if you do not supply what constitutes a
match in your where clause ...

You can get ALL col1 values for ALL fruits with
select col1 from table where col2='fruit';