SELECT Query : How can I .... ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT Query : How can I .... ?

Author

Topic

atzakas
Starting Member

5 Posts

Posted - 2006-04-17 : 09:06:52

Greetings to everyone,

I am new in this forum and I'm glad to join you.
I am writing a simple SQL database in SQL server 2005.

I would like to create a query that will return a single Column. This Column will contain the Food_Name in the first cell, and the food ingredients in the cells underneath.

Look at my current result. The Food_Name is repeated in a seperate column. Not good :(

If you have something else to propose (something more elegant), I 'm all ears !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 09:13:44

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-17 : 09:14:28

atzakas,
It may be done in SQL but with a lot of (or rather complicated) coding.
This type of thing can be easily done in the Front End.

What is ur Front End ? ASP.Net ?

Srinika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-17 : 09:23:34

U may use something like following if u want to use SQL (but this is not a formal way of doing it)


Select top 1 t_foods.Food_name from 
.....
....
UNION ALL
Select t_ingradients.indradient_name from 
.....
....

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-17 : 10:17:47

This should do it:

select
	FName = isnull(ingredient_name, Food_name )
from
	(
	select
		Food_name
		ingredient_name
	from
		Mytable
	group by
		Food_name
		ingredient_name
	with rollup
	) a
where
	Food_name is not null
order by
	case when ingredient_name is null then 0 else 1,
	Food_name,
	ingredient_name

CODO ERGO SUM

atzakas
Starting Member

5 Posts

Posted - 2006-04-17 : 15:44:41

Thank you for the replies!
I will take a good look at your advices and will let you know with my result :)

Cheers
Atzakas

atzakas
Starting Member

5 Posts

Posted - 2006-04-17 : 15:54:39

quote:
Originally posted by Srinika

What is ur Front End ? ASP.Net ?

Srinika

Yes, I am developing a device application with Visual Studio 2005

atzakas
Starting Member

5 Posts

Posted - 2006-04-17 : 15:57:44

quote:
Originally posted by madhivanan

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

My food database will contain several foods and many of them will have similar name. For example "spaghetti carbonara", or "spaghetti al pesto", etc.
So, if the user enter "spaghetti" , I would like the web service to return a list of the food names (spaghetti carbonara, spaghetti al pesto, etc) and he can then choose from this list.

I know I am a long way before I can do this. Being a newbie, I have to read many text lines just to fine the write command ! But that's the beauty I guess :)

cheers
Atzakas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 01:12:46

>>So, if the user enter "spaghetti" , I would like the web service to return a list of the food names (spaghetti carbonara, spaghetti al pesto, etc) and he can then choose from this list.

If your query doesnt give you the expected result,give us some sample data and your expected result

Madhivanan

Failing to plan is Planning to fail

atzakas
Starting Member

5 Posts

Posted - 2006-04-18 : 02:48:55

quote:
Originally posted by madhivanan

>>So, if the user enter "spaghetti" , I would like the web service to return a list of the food names (spaghetti carbonara, spaghetti al pesto, etc) and he can then choose from this list.

If your query doesnt give you the expected result,give us some sample data and your expected result

Madhivanan

Failing to plan is Planning to fail

For example, if I enter "Spaghetti" and the database contains both "Spaghetti Carbonara" and "Spghetti Napolitana", then the database sends a list with the 2 (in this case) foods. The user then select one and the database returns the ingredients.
As I see it, this will require 2 calls to the database (through the web service).

Alternatively, I could do the follow. For each query (food name search) the database sends the food name along with the ingredients. The user is presented with the food name first and he will have to "tap" on the food name (remember, I am developing this for a pocket pc) and the ingredients will be displayed. This way, if there are 2 foods containing the word "spaghetti", the user will be able to select which one he wants. If there is only one food, he will still have to tap on it.

I know my question is very general, so I 'll work on it and ask you if I get stuck.

Thx!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources