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
 Old Forums
 CLOSED - General SQL Server
 INNER JOIN problem , urgent help plz

Author  Topic 

youtoo
Starting Member

22 Posts

Posted - 2006-10-29 : 09:30:40
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)

17689 Posts

Posted - 2006-10-29 : 09:36:24
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 - 2006-10-29 : 10:06:17
the same results
twice records

whats the problem ?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 10:15:38
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 - 2006-10-29 : 10:30:43
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)

17689 Posts

Posted - 2006-10-29 : 10:38:34
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 - 2006-10-29 : 10:44:53
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)

17689 Posts

Posted - 2006-10-29 : 10:50:30
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 - 2006-10-29 : 10:57:35
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)

17689 Posts

Posted - 2006-10-29 : 11:01:04
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

30421 Posts

Posted - 2006-10-29 : 11:03:07
Have you tried used keyword DISTINCT?

select distinct .... from ....

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

youtoo
Starting Member

22 Posts

Posted - 2006-10-29 : 11:03:39
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 - 2006-10-29 : 11:05:44
No Mr Larsson
What is DISTINCT ?
what can it does?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 11:06:27
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)

17689 Posts

Posted - 2006-10-29 : 11:09:37
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

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 11:11:59
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 - 2006-10-29 : 11:15:40
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

30421 Posts

Posted - 2006-10-29 : 11:16:26
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 11:21:12
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

30421 Posts

Posted - 2006-10-29 : 11:25:28
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 - 2006-10-29 : 11:29:02
don't sleep , please help me

somebody help me
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -