locked
How to modify my query that I don't want result RRS feed

  • Question

  • Hi,

    I've four tables and each tables same like following picture

    And I Query same like following

    SELECT	Ctry.NumericCode, Ctry.Alpha2Code, Ctry.CountryID, Ctry.CountryName, Cncy.Alpha3Code
    ,		ROW_NUMBER() OVER(ORDER BY Ctry.NumericCode) AS OrderByNumericCode-- RunningNumber; use if required
    FROM	Temp
    JOIN	CountryOf3166 Ctry
    ON		Temp.NumericCode = Ctry.NumericCode
    JOIN	CurrencyOf4217 Cncy
    ON		Temp.Alpha3Code = Cncy.Alpha3Code

    The query result same like following;

    100	BG	35	 Bulgaria	BGN	1
    104	MM	153	 Myanmar	MMK	2
    108	BI	37	 Burundi	BIF	3
    116	KH	38	 Cambodia	KHR	4
    120	CM	39	 Cameroon	XAF	5
    120	CM	39	 Cameroon	XAF	6
    120	CM	39	 Cameroon	XAF	7
    120	CM	39	 Cameroon	XAF	8
    120	CM	39	 Cameroon	XAF	9
    120	CM	39	 Cameroon	XAF	10
    124	CA	40	 Canada	CAD	11
    132	CV	41	 Cape Verde	CVE	12
    136	KY	42	 Cayman Islands	KYD	13
    140	CF	43	 Central African Republic	XAF	14
    140	CF	43	 Central African Republic	XAF	15
    140	CF	43	 Central African Republic	XAF	16
    140	CF	43	 Central African Republic	XAF	17
    140	CF	43	 Central African Republic	XAF	18
    140	CF	43	 Central African Republic	XAF	19
    144	LK	210	 Sri Lanka	LKR	20
    148	TD	44	 Chad	XAF	21
    148	TD	44	 Chad	XAF	22
    148	TD	44	 Chad	XAF	23
    148	TD	44	 Chad	XAF	24
    148	TD	44	 Chad	XAF	25
    148	TD	44	 Chad	XAF	26
    152	CL	45	 Chile	CLP	27
    156	CN	46	 China	CNY	28
    158	TW	218	 Taiwan, Province of China	TWD	29
    162	CX	47	 Christmas Island	AUD	30
    162	CX	47	 Christmas Island	AUD	31
    162	CX	47	 Christmas Island	AUD	32
    162	CX	47	 Christmas Island	AUD	33
    162	CX	47	 Christmas Island	AUD	34
    162	CX	47	 Christmas Island	AUD	35
    162	CX	47	 Christmas Island	AUD	36
    162	CX	47	 Christmas Island	AUD	37
    166	CC	48	 Cocos (Keeling) Islands	AUD	38
    166	CC	48	 Cocos (Keeling) Islands	AUD	39
    166	CC	48	 Cocos (Keeling) Islands	AUD	40
    166	CC	48	 Cocos (Keeling) Islands	AUD	41
    166	CC	48	 Cocos (Keeling) Islands	AUD	42
    166	CC	48	 Cocos (Keeling) Islands	AUD	43
    166	CC	48	 Cocos (Keeling) Islands	AUD	44
    166	CC	48	 Cocos (Keeling) Islands	AUD	45
    

    But, this result is not I wanted.

    I really want result is same like following;

    NumericCode	CountryCode	CurrencyName	CurrencyCode
    4	AF	Afghan afghani 	AFN
    248	AX	Euro 	EUR
    8	AL	Albanian lek 	ALL
    12	DZ	Algerian dinar 	DZD
    16	AS		
    20	AD	Euro 	EUR
    24	AO	Angolan kwanza 	AOA
    660	AI	East Caribbean dollar 	XCD
    10	AQ		
    28	AG	East Caribbean dollar 	XCD
    32	AR	Argentine peso 	ARS
    51	AM	Armenian dram 	AMD
    533	AW	Aruban florin 	AWG
    36	AU	Australian dollar 	AUD
    40	AT	Euro 	EUR
    31	AZ	Azerbaijani manat 	AZN
    44	BS	Bahamian dollar 	BSD
    48	BH	Bahraini dinar 	BHD
    50	BD	Bangladeshi taka 	BDT
    52	BB	Barbadian dollar 	BBD
    112	BY	Belarusian ruble 	BYR
    56	BE	Euro 	EUR
    84	BZ	Belize dollar 	BZD
    204	BJ	West African CFA franc 	XOF
    60	BM	Bermudian dollar 	BMD
    64	BT	Bhutanese ngultrum 	BTN
    64	BT	 Indian rupee 	INR
    68	BO	Bolivian boliviano 	BOB
    535	BQ	United States dollar 	USD
    70	BA	Bosnia and Herzegovina convertible mark 	BAM
    72	BW	Botswana pula 	BWP
    74	BV		
    76	BR	Brazilian real 	BRL
    86	IO	United States dollar 	USD
    96	BN	Brunei dollar 	BND
    96	BN	Singapore dollar 	SGD
    100	BG	Bulgarian lev 	BGN
    894	ZM	Zambian kwacha 	ZMK
    716	ZW	Botswana pula 	BWP
    716	ZW	 British pound 	GBP
    716	ZW	Euro 	EUR
    716	ZW	South African rand	ZAR
    716	ZW	United States dollar 	USD
    716	ZW	Zimbabwean dollar	ZWL

    As Looking at 'ZW' row data in CountryCode field, they've six kinds of currency.

    So how can I query If I GET RESULT THAT REALLY WANTED?

    If I get result that I wanted, then I'll insert to CountryCurrency table each values.


    • Edited by David Hur Friday, August 31, 2012 9:06 AM
    Friday, August 31, 2012 5:50 AM

Answers

  • Hi,

    Still not 100% sure whether this the same you want, I think you do not want duplicate rows.. See if this helps

    select ain.NumericCode
    	, ain.Alpha2Code
    	, ain.CountryID
    	, ain.CountryName
    	, ain.Alpha3Code
    	,ROW_NUMBER() OVER(ORDER BY ain.NumericCode) AS OrderByNumericCode
    from
    (
    	SELECT	distinct Ctry.NumericCode, Ctry.Alpha2Code, Ctry.CountryID, Ctry.CountryName, Cncy.Alpha3Code
    	FROM	Temp
    	JOIN	CountryOf3166 Ctry ON Temp.NumericCode = Ctry.NumericCode
    	JOIN	CurrencyOf4217 Cncy ON Temp.Alpha3Code = Cncy.Alpha3Code
    ) ain


    - Chintak (My Blog)

    • Marked as answer by David Hur Sunday, September 2, 2012 8:12 AM
    Friday, August 31, 2012 9:35 AM

All replies

  • Hello David,

    It will be good if you add the excepted result set.


    - Chintak (My Blog)

    Friday, August 31, 2012 8:49 AM
  • Hello David,

    It will be good if you add the excepted result set.


    - Chintak (My Blog)

    I'm sorry. I just want exactly expression for my question so that I attached all data.

    Now I reduced Results.

    I hope you helping me :)

    you are my super hero.

    Friday, August 31, 2012 9:11 AM
  • Hi,

    Still not 100% sure whether this the same you want, I think you do not want duplicate rows.. See if this helps

    select ain.NumericCode
    	, ain.Alpha2Code
    	, ain.CountryID
    	, ain.CountryName
    	, ain.Alpha3Code
    	,ROW_NUMBER() OVER(ORDER BY ain.NumericCode) AS OrderByNumericCode
    from
    (
    	SELECT	distinct Ctry.NumericCode, Ctry.Alpha2Code, Ctry.CountryID, Ctry.CountryName, Cncy.Alpha3Code
    	FROM	Temp
    	JOIN	CountryOf3166 Ctry ON Temp.NumericCode = Ctry.NumericCode
    	JOIN	CurrencyOf4217 Cncy ON Temp.Alpha3Code = Cncy.Alpha3Code
    ) ain


    - Chintak (My Blog)

    • Marked as answer by David Hur Sunday, September 2, 2012 8:12 AM
    Friday, August 31, 2012 9:35 AM
  • Thank you for helping me even though this is not result that I wanted.

    As you can see the result that I wanted,

    For example that more understanding my question,

    NumCode / CountryCode / CurrencyName / CurrencyCode

    716 ZW Botswana pula BWP 716 ZW British pound GBP 716 ZW Euro EUR 716 ZW South African rand ZAR 716 ZW United States dollar USD 716 ZW Zimbabwean dollar ZWL

    Look at the result that I wanted.

    There are CountryCode field in row 'ZW' was duplicated because ZW='Zimbabwe'.

    There country using 5 kinds of currency. 1.BWP & 2.GBP & 3.ZAR & 4.USD & 5.ZWL.

    So I designed many to many relationship between Country table and Currency table.

    Because Country can using lots of Currency. and Currency can using lots of Country.

    First time, I just think about 1:N relationship the situation, But I recognized there needed M:N relationship on Yesterday.

    So If you understand that I wanted result, Please help me MY HERO Chintak Chhapia~!

    Friday, August 31, 2012 10:01 AM
  • I'm so sorry.

    The result that I not wanted caused by duplicated data in currency table.

    It's my mistake.

    So now I done work great! Thanks!

    Sunday, September 2, 2012 8:14 AM