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 recordthis :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 FOODSfood_idfood_namefood_pricetable 2 SeFOODS (selected foods)sf_idfood_idtypequantityI want some thing like this as resultsFoodName (quantity)it will return twice record like thisChiken (3)Chiken (3)orBread(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 resultsFoodName (quantity)"try thisselect food_name, count(*)from FOODS f inner join SeFOODS s on f.food_id = s.food_idgroup by food_name KH |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 10:06:17
|
the same resultstwice recordswhats the problem ? |
|
|
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 |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 10:30:43
|
this is first table (FOODS)table 1 FOODSfood_id (int) autofood_name (varchar)food_price (varchar)>>> sample recordsfood_id food_name food_price1 chiken $2and table 2table 2 SeFOODS (selected foods)sf_id (int) autofood_id (int) related to foods table PKtype (int)quantity (int)>>>>>> sample recordssf_id food_id type quantity 1 1 1 5now users can select foods and we keep selected foods in sefoods tablenow I want pull out selected foods from sefoodsthe results should be thisFoodName from foods table and quantity from sefoods tablelike thisChiken 5 |
|
|
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 thisselect f.FoodName, sum(s.Quantity)from foods f inner join sefoods s on f.food_id = s.food_idgroup by f.FoodName KH |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 10:44:53
|
the same problem again,sample datatable 1 foodsfood_id = 5food_name = breadfood_price = $1table 2 seFoodssf_id = 6food_id = 5 (related to foods table PK)type = 1 (this is not important its just a value)quantity = 7 |
|
|
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 useddeclare @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 @FOODSselect 1, 'chiken', '$2' union allselect 5, 'bread', '$1'insert into @SeFOODSselect 1, 1, 1, 5 union allselect 6, 5, 1, 7select f.food_name, qty = sum(s.quantity)from @FOODS f inner join @SeFOODS s on f.food_id = s.food_idgroup by f.food_name/* RESULTfood_name qty ---------- ----------- bread 7chiken 5*/ KH |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 10:57:35
|
thanks you are in a right way its okI 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)
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 postedquote: like thisChiken 5
5 looks like the Quantity to me KH |
|
|
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 LarssonHelsingborg, Sweden |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 11:03:39
|
yes 5 is quantityI need these and pricelike thisChiken 5 $2 |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 11:05:44
|
No Mr LarssonWhat is DISTINCT ?what can it does? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-29 : 11:06:27
|
what is the result you want for this scenarioinsert into @FOODSselect 1, 'chiken', '$2'insert into @SeFOODSselect 1, 1, 1, 5 union allselect 2, 1, 1, 7Chiken 12 $2 ? KH |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-29 : 11:09:37
|
quote: Originally posted by youtooWhat is DISTINCT ?what can it does?
see the Books OnLine aka Help File on Eliminating Duplicates with DISTINCT KH |
|
|
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 @FOODSselect 1, 'chiken', '$2' union allselect 5, 'bread', '$1'insert into @SeFOODSselect 1, 1, 1, 5 union allselect 2, 1, 1, 7 union allselect 6, 5, 1, 7select f.food_name, qty = sum(s.quantity), food_pricefrom @FOODS f inner join @SeFOODS s on f.food_id = s.food_idgroup by f.food_name, f.food_price/* RESULTfood_name qty food_price ---------- ----------- ---------- bread 7 $1chiken 12 $2*/ KH |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 11:15:40
|
No I want thisChiken 5 $2Bread 7 $1and ..don't sum quantitys |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 LarssonHelsingborg, 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
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 LarssonHelsingborg, Sweden |
|
|
youtoo
Starting Member
22 Posts |
Posted - 2006-10-29 : 11:29:02
|
don't sleep , please help me somebody help me |
|
|
Previous Page&nsp;
Next Page
|