Idiot SQL question...

AtlantaBob

Golden Member
Jun 16, 2004
1,034
0
0
Sorry, I'm just starting off with some SQL programing, and haven't been able to find too many good books at the library. Could someone help me figure out what a query should be like in the following circumstance? (This is using Access 2003 by the way).

Let's say that I have three fields in the table LOCATION: CITY, STATE and COUNTRY.

I'd like to write a query that will return CITY, STATE if the COUNTRY is "USA" and CITY, COUNTRY if the COUNTRY is not "USA."

I can do the concatenate thing with "&" but I'm not sure how to do the rest.

Thanks in advance. Hopefully, I can work from this and figure out some more SQL.
 

ugh

Platinum Member
Feb 6, 2000
2,563
0
0
select CITY, STATE from LOCATION where COUNTRY = "USA"

select CITY, COUNTRY from LOCATION where COUNTRY != "USA"

Uhh... Not sure if this is what you want coz I don't really get where you'd want to use the concatenation operator. :)
 

AFB

Lifer
Jan 10, 2004
10,718
3
0
Just grab all the fields and use the a programming language to parse it.
 

AtlantaBob

Golden Member
Jun 16, 2004
1,034
0
0
Ah, sorry, I don't think I was clear on that one... and I may be going about this all wrong. In this case, I don't think I want to actually select the records, but just display them in a particular way in a control so users can view this information in a combo box. Therefore, (I think) I want to use the concatenation operator so I can come up with "Atlanta, GA" or "Munich, Germany" in the control. Later on, it would be nice if this could also be used in a report, as well.

Thanks, and if you could tell me if I'm an idot, that would be helpful, too!
 

akubi

Diamond Member
Apr 19, 2005
4,392
1
0
Originally posted by: AFB
Just grab all the fields and use the a programming language to parse it.

that's a really dumb way to do it if there are thousands of rows..
 

TheUnk

Golden Member
Jun 24, 2005
1,810
0
71
Originally posted by: AtlantaBobI'd like to write a query that will return CITY, STATE if the COUNTRY is "USA" and CITY, COUNTRY if the COUNTRY is not "USA."

Use the CASE function

SELECT CASE Country WHEN 'USA' THEN State ELSE Country END as MyLocation FROM Location

 

AFB

Lifer
Jan 10, 2004
10,718
3
0
Originally posted by: akubi
Originally posted by: AFB
Just grab all the fields and use the a programming language to parse it.

that's a really dumb way to do it if there are thousands of rows..

I can't imagine there would be looking at the information given.
 

AtlantaBob

Golden Member
Jun 16, 2004
1,034
0
0
Hm. I've tried writing the CASE Statement, but that seems to be T-SQL, and Access doesn't like it.

So far, I've got two statements that do what I want; can someone explain to me how I can combine these into one statement--or, more importantly, get them to output to the same field? Would I want to create two update queries that both wrote the results to the same field?

Thanks for all of your patience and suggestions.

SELECT [LocationID], [City] & ", " & [Country] AS Location
FROM LocationTable
WHERE [Country]<>'USA';

SELECT [LocationID], [City] & ", " & [State] AS Location
FROM LocationTable
WHERE [Country]='USA'
 

AtlantaBob

Golden Member
Jun 16, 2004
1,034
0
0
TheUnk and Znaps:

Thanks for the suggestions! I've found two ways of writing this:

SELECT IIf(Country="USA",City & ", " & State,City & ", " & Country) AS Location
FROM LocationTable;

and as

SELECT
City & ', ' & State AS Location
FROM LocationTable
WHERE Country = 'USA'

UNION SELECT
City & ", " & Country AS Location
FROM LocationTable
WHERE Country <> 'USA';
 

statik213

Golden Member
Oct 31, 2004
1,654
0
0
Originally posted by: AtlantaBob
TheUnk and Znaps:

Thanks for the suggestions! I've found two ways of writing this:

SELECT IIf(Country="USA",City & ", " & State,City & ", " & Country) AS Location
FROM LocationTable;

and as

SELECT
City & ', ' & State AS Location
FROM LocationTable
WHERE Country = 'USA'

UNION SELECT
City & ", " & Country AS Location
FROM LocationTable
WHERE Country <> 'USA';


I'd go with the first i.e. IIF.
 

znaps

Senior member
Jan 15, 2004
414
0
0
The first way is ANSI SQL, the IIF isn't. Not sure if he cares about that, but I thought I'd mention it.
 

statik213

Golden Member
Oct 31, 2004
1,654
0
0
Originally posted by: znaps
The first way is ANSI SQL, the IIF isn't. Not sure if he cares about that, but I thought I'd mention it.

Is there an equi. IIF function in ANSI SQL? MySQL has a similiar function called IF.
 

Mark R

Diamond Member
Oct 9, 1999
8,513
16
81
Is there an equi. IIF function in ANSI SQL? MySQL has a similiar function called IF.

The ANSI-92 function is CASE.

It can be used in 2 ways:

CASE WHEN price > 1000 THEN 'Expensive' ELSE 'Cheap' END

or

CASE widget
WHEN 1 THEN 'Foo'
WHEN 2 THEN 'Bar'
WHEN 3 THEN 'Flange'
WHEN 4 THEN 'Grobble'
END