SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 INNER JOIN problem , urgent help plz
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  09:30:40  Show Profile
I have 2 tables I want use join to use their data its working but it will return twice of each record

this :

strSQL = "SELECT Foods.*,SeFoods.* From Foods INNER JOIN SeFoods ON Foods.food_id = SeFoods.food_id WHERE SeFoods.type = 1 ORDER BY seFoods.sf_id DESC;"


table 1 FOODS

food_id
food_name
food_price


table 2 SeFOODS (selected foods)

sf_id
food_id
type
quantity

I want some thing like this as results

FoodName (quantity)

it will return twice record like this

Chiken (3)
Chiken (3)

or

Bread(2)
Bread(2)

what is the problem

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  09:36:24  Show Profile
Looks like for each record in FOODS table you have 2 recors in SeFOODS table.

"I want some thing like this as results
FoodName (quantity)"

try this

select food_name, count(*)
from   FOODS f inner join SeFOODS s
       on  f.food_id = s.food_id
group by food_name



KH

Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  10:06:17  Show Profile
the same results
twice records

whats the problem ?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  10:15:38  Show Profile
Post your table structure, some sample data and the result that you want


KH

Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  10:30:43  Show Profile
this is first table (FOODS)

table 1 FOODS

food_id (int) auto
food_name (varchar)
food_price (varchar)

>>> sample records

food_id food_name food_price

1 chiken $2



and table 2

table 2 SeFOODS (selected foods)

sf_id (int) auto
food_id (int) related to foods table PK
type (int)
quantity (int)

>>>>>> sample records

sf_id food_id type quantity

1 1 1 5


now users can select foods and we keep selected foods in sefoods table
now I want pull out selected foods from sefoods

the results should be this

FoodName from foods table
and quantity from sefoods table

like this

Chiken 5




Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  10:38:34  Show Profile
Can you post more sample data ?

In the mean time try this

select f.FoodName, sum(s.Quantity)
from   foods f inner join sefoods s
       on f.food_id = s.food_id
group by f.FoodName



KH

Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  10:44:53  Show Profile
the same problem again,

sample data

table 1 foods

food_id = 5
food_name = bread
food_price = $1

table 2 seFoods

sf_id = 6
food_id = 5 (related to foods table PK)
type = 1 (this is not important its just a value)
quantity = 7
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  10:50:30  Show Profile
Please post more sample data that will illustrate the problem that you have. Also post the query that you used


declare @FOODS table
(
	food_id		int,
	food_name	varchar(10),
	food_price	varchar(10)
)

declare @SeFOODS table
(
	sf_id		int,
	food_id		int,
	type		int,
	quantity	int
)

insert into @FOODS
select	1, 'chiken', '$2'	union all
select	5, 'bread',  '$1'

insert into @SeFOODS
select	1, 1, 1, 5	union all
select	6, 5, 1, 7

select	f.food_name, qty = sum(s.quantity)
from	@FOODS f inner join @SeFOODS s
	on	f.food_id	= s.food_id
group by f.food_name

/* RESULT
food_name  qty         
---------- ----------- 
bread      7
chiken     5
*/



KH

Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  10:57:35  Show Profile
thanks you are in a right way its ok
I am using this to pull out data :

strSQL = "SELECT f.food_name, s.quantity, " & _
" count(s.fid) as theCount " & _
"FROM Foods f INNER JOIN seFoods s ON f.food_id = s.food_id " & _
"WHERE s.type = 1 " & _
"GROUP BY f.food_name, s.quantity " & _
"ORDER BY theCount DESC;"

I have checked you code but did not work

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  11:01:04  Show Profile
because you are grouping by food_name & quantity.

Do you need the quantity or count ? Previoulsy you posted
quote:

like this

Chiken 5


5 looks like the Quantity to me



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30207 Posts

Posted - 10/29/2006 :  11:03:07  Show Profile  Visit SwePeso's Homepage
Have you tried used keyword DISTINCT?

select distinct .... from ....

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  11:03:39  Show Profile
yes 5 is quantity
I need these and price
like this

Chiken 5 $2

Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  11:05:44  Show Profile
No Mr Larsson
What is DISTINCT ?
what can it does?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  11:06:27  Show Profile
what is the result you want for this scenario

insert into @FOODS
select	1, 'chiken', '$2'

insert into @SeFOODS
select	1, 1, 1, 5	union all
select	2, 1, 1, 7

Chiken 12 $2 ?



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  11:09:37  Show Profile
quote:
Originally posted by youtoo
What is DISTINCT ?
what can it does?



see the Books OnLine aka Help File on Eliminating Duplicates with DISTINCT


KH


Edited by - khtan on 10/29/2006 11:10:06
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  11:11:59  Show Profile
Is this what you want ?
declare @FOODS table
(
	food_id		int,
	food_name	varchar(10),
	food_price	varchar(10)
)

declare @SeFOODS table
(
	sf_id		int,
	food_id		int,
	type		int,
	quantity	int
)

insert into @FOODS
select	1, 'chiken', '$2'	union all
select	5, 'bread',  '$1'

insert into @SeFOODS
select	1, 1, 1, 5	union all
select	2, 1, 1, 7	union all
select	6, 5, 1, 7

select	f.food_name, qty = sum(s.quantity), food_price
from	@FOODS f inner join @SeFOODS s
	on	f.food_id	= s.food_id
group by f.food_name, f.food_price

/* RESULT
food_name  qty         food_price 
---------- ----------- ---------- 
bread      7           $1
chiken     12          $2
*/



KH

Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  11:15:40  Show Profile
No I want this

Chiken 5 $2
Bread 7 $1

and ..
don't sum quantitys
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30207 Posts

Posted - 10/29/2006 :  11:16:26  Show Profile  Visit SwePeso's Homepage
How are you khtan? Does it hurt yet?

Finally...
quote:
Mammy, I do not want that, I want this instead

And all it took was proper explanation from where the 5 and 7 come from

youtoo is like a spoiled child.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/29/2006 11:20:04
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 10/29/2006 :  11:21:12  Show Profile
quote:
Originally posted by Peso

Finally...
All we needed was proper explanation from where the 5 and 7 come from
How are you khtan? Does it hurt yet?


Peter Larsson
Helsingborg, Sweden


No. But tired . It's way pass my usual sleeping time. You carry on. I need to catch some sleep


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30207 Posts

Posted - 10/29/2006 :  11:25:28  Show Profile  Visit SwePeso's Homepage
I don't want to!

Unless youtoo is able to really explain every single column in the wanted output.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 10/29/2006 :  11:29:02  Show Profile
don't sleep , please help me

somebody help me
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000