| Author |
 Topic  |
|
youtoo
Starting Member
22 Posts |
 Posted - 10/29/2006 : 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)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
 |
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 10:06:17
|
the same results
twice records
whats the problem ? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 10:15:38
|
Post your table structure, some sample data and the result that you want
KH
|
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/29/2006 : 11:03:07
|
Have you tried used keyword DISTINCT?
select distinct .... from ....
Peter Larsson
Helsingborg, Sweden |
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 11:03:39
|
yes 5 is quantity
I need these and price
like this
Chiken 5 $2
|
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 11:05:44
|
No Mr Larsson
What is DISTINCT ?
what can it does? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
Edited by - khtan on 10/29/2006 11:10:06 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 11:15:40
|
No I want this
Chiken 5 $2
Bread 7 $1
and ..
don't sum quantitys
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/29/2006 : 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 |
Edited by - SwePeso on 10/29/2006 11:20:04 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 10/29/2006 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/29/2006 : 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 |
|
|
|
youtoo
Starting Member
22 Posts |
Posted - 10/29/2006 : 11:29:02
|
don't sleep , please help me
somebody help me  |
|
|
|
Topic |
|
INNER JOIN problem , urgent help plz
Forum Locked
