Help Is CONCAT what I need?

[Popgun]

Hi All

 

Im trying to construct a recordset that combines the State col from a users table with the state col from a location table, as an alias so that I can dynamically pull the users Zip code and City from the location table for location_ID (assignment) and update in a form. I cant seem to get the CONCAT part of the SQL to work:

 

This works:

 

SELECT *

FROM USERS, LOCATION

WHERE user_ID = colname  AND USERS.`state` = LOCATION.stateabbreviation 

 

This does not:

 

SELECT *

FROM USERS, LOCATION

WHERE user_ID = colname  AND USERS.`state` = LOCATION.stateabbreviation 

CONCAT(USERS.`state`, LOCATION.stateabbreviation) AS state

 

Neither does this:

 

SELECT *

FROM USERS, LOCATION

WHERE user_ID = colname  AND USERS.`state` = LOCATION.stateabbreviation 

SELECT CONCAT('state', 'stateabbreviation') AS state

 

Getting a syntax error near CONCAT

 

Any help is greatly appriciated, new to MySQL

[fenway]

I don't understand what you're trying to do...but you can't use a column alias that way.

[Popgun]

sorry if I was unclear, what Im trying to do is combine the state col from one table (users) with the state col from another table (location) into an alias named state

[fenway]

Ah, I see.

 

You need to add this expression to the column list:

 

SELECT *

,CONCAT(USERS.`state`, LOCATION.stateabbreviation) AS state

FROM USERS, LOCATION

WHERE user_ID = colname  AND USERS.`state` = LOCATION.stateabbreviation

 

[Popgun]

fenway,

 

Thanks man I had the order wrong I guess you learn something new everyday right? (hopefully!!)

 

Now I have to figure out how to limit the results based on the alias state to pull the zip code and cities associated with that state for population in the form O.o

[fenway]

I'm confused... why combined the two parts just to make it hard to query?

[Popgun]

I guess the simpilest explaination is I'm trying to assign a location ID from the location table to a user_ID from the user table.

 

I need to be able to identify the users location so I can match them to a nearby service provider.

 

The table structure is such:

LOCATION

 

location_ID

zip

city

stateabbreviation

latitude

longitude

 

USERS

 

user_ID

location_ID

state (1 of 15 specific states, also abbreviated, as I only have service providers in 15 states at the moment)

 

 

So I'm using CONCAT to form an alias on state, so I can pull ONLY the ZIP codes and Cities from those 15 states rather than the entire LOCATION table

 

I'm not sure if there is a better way to do this, I made some headway last night using applying DISTINCT and a LEFT JOIN, but it needs to be refined a bit.

 

[fenway]

I still don't see why you just can't query each column separately.

[Popgun]

I'm probably being ignorant here but if they are all on one form, and I need to assign a foreign key dont i need a multi-table recordset query using a join?

[fenway]

I'm probably being ignorant here but if they are all on one form, and I need to assign a foreign key dont i need a multi-table recordset query using a join?

FKs do not impose any restrictions that requires concatenating columns... please clarify.