Is there an SQL function that can combine two fields into one?

[redhillccwebmas]

Hi there,

I would like to find out whether theres an SQL function that combines two fields (in the same table) and processes them as one? So for example, I have two fields - cake & sweets.

I would like to combine the values contained within those fields. So my SQL function would output both values as one; 'cake_sweets'.

Is this possible - could it be done by combining the two fields and outputting them as an 'AS'?

EDIT: Could I do something like:-

SELECT shop.cake AND shop.sweets AS cake_sweets ?

Really appreciated your help as usual.

Kind Regards

james

[wwb_99]

Not really a function, but SELECT shop.cake + '_' + shop.sweets AS cake_sweets should get you there.

[mr_jb]

You could concatenate the two fields and return them as one but it also depends on the sql server you are using.

MySQL :
SELECT concat(shop.cake,' ' , shop.sweets) AS cake_sweets

MS SQL :
SELECT shop.cake + ' ' + shop.sweets AS cake_sweets

[redhillccwebmas]

MS Access I'm afraid - can I still use this function?

Thanks for your help

Regards

James

[longneck]

SELECT shop.cake & "_" & shop.sweets AS cake_sweets

[redhillccwebmas]

Thanks so much guys for your help. Got me out of some hot water there!

Works perfectly!

[redhillccwebmas]

Apologies if this is double-posted.

I've tried the following SQL query (part of it taken from the help you guys were nice enough to give me) but it's throwing up an error, pointing to the 'by' (highlighted in bold - right at the end of my SELECT line):-

strSQL = "SELECT sculptures.ID, sculptures.Name, artist.Firstname, sculptures.condition, sculptures.literature1, sculptures.literature2, sculptures.literature3, sculptures.literature4, sculptures.ImageIndivMain, sculptures.articlenumber, sculptures.circadate, sculptures.Description, artist.Surname, sculptures.artistID, sculptures.weight, sculptures.price, sculptures.depth, sculptures.width, sculptures.height, literature.title & " by " & literature.author AS literaturecombine" & strCRLF & _
   " FROM ((sculptures" & strCRLF & _
   " INNER JOIN artist ON artist.NameURL = sculptures.artistID)" & strCRLF & _
   " INNER JOIN literature ON literature.literaturecombine = sculptures.literaturecombine1)" & strCRLF & _
   " INNER JOIN sculpturetype ON sculpturetype.TypeURL = sculptures.sculpturetypeID" & strCRLF & _
   " WHERE sculptures.NameURL='" & lngRecord & "'"

Is there anyway I can get this successfully formatted so it works OK

Thanks for your help again

Kind Regards

James

[longneck]

you can't use double-quotes inside of a double-quoted string. based on your use of str at the beginning of your variable name, i'm assuming you're using ASP or VB. i don't know proper quoting technique for VB and ASP, but i know you can't use double quotes inside a double-quoted string.

[redhillccwebmas]

Thanks for your help, Longneck.

Yes I am using ASP. Would you suggest I try another SQL string format (one which doesn't include the double quotes)?

EDIT: I've tried running my query directly in Access and it's showing an error - it's saying that the join expression is not supported. I'm guessing that it's referring to the field, literaturecombine which is declared immediatly after the ON clause. I think that it may be because I need to declare it as literature.literaturecombine . I've tried that but it then says that the format of my ON clause is wrong. Any ideas on what I'm doing wrong?

SELECT sculptures.ID, sculptures.Name, artist.Firstname, sculptures.condition, sculptures.literaturecombine1, sculptures.literaturecombine2, sculptures.literaturecombine3, sculptures.literaturecombine4, sculptures.ImageIndivMain, sculptures.articlenumber, sculptures.circadate, sculptures.Description, artist.Surname, sculptures.artistID, sculptures.weight, sculptures.price, sculptures.depth, sculptures.width, sculptures.height, literature.title & " by " & literature.author AS literaturecombine
FROM ((sculptures
INNER JOIN artist ON artist.NameURL = sculptures.artistID)
INNER JOIN literature ON literaturecombine = sculptures.literaturecombine1)
INNER JOIN sculpturetype ON  sculpturetype.TypeURL = sculptures.sculpturetypeID
WHERE sculptures.NameURL='Tennis-Player'

Thanks all for the help you've given me so far

Kind Regards

James

[r937]

that last query looks fine -- further debugging would requiring knowing the exact column names and their datatypes

[redhillccwebmas]

Sorry for the delay in replying - some idiotic builders cut through my phone cable so no internet access for most of the day!

Thanks R937 for your invaluable help on this and past issues!

My complete database structure is below:-

literature.ID (primary key) -- Autonumber
literature.author -- Text
literature.title -- Text

sculptures.ID (primary key) -- Autonumber
sculptures.Name -- Memo
sculptures.LiteratureCombine1 -- Text
sculptures.LiteratureCombine2 -- Text
sculptures.ArtistID (foreign key to Artist.NameURL) -- Text
sculptures.sculpturetypeID (foreign key to sculpturetype.TypeURL) -- Text

artist.FirstName -- Text
artist.Surname -- Text
artist.NameURL (primary key) -- Text

images.ID (primary key) -- Autonumber
images.ImgURL -- Text
images.sculpturesID (foreign key to sculptures.ID) -- Number

sculpturetype.ID -- Autonumber
sculpturetype.type -- Memo
sculpturetype.TypeURL (primary key) -- Text


You'll notice that my 'literature' table isn't joined to another within Access as it is joined by concatenating the two fields, literature.title and literature.author by SQL within an Access drop down box (not an ideal way of doing it, but that's what the client asked for) - this drop down box populates the fields sculptures.LiteratureCombine1 and sculptures.LiteratureCombine2 within Access.

So basically by concatenating the two fields within my SQL string within my ASP statement, the result gives me the same value as the sculptures.LiteratureCombine1 field - these two fields I want to join together in one of the JOIN clauses.

I think I'm a bit out of my depth as to what I'm doing but I'd like to think of myself as a fast learner and would be more than happy for someone to tell me where I'm going wrong!

Again, thanks Sitepoint members for helping me out on this matter, especially r937

Kind Regards

James

[r937]

i have no idea what you are doing with the dropdowns and combines

but your problem is here --

INNER JOIN literature ON literaturecombine = sculptures.literaturecombine1

there is no column called "literaturecombine"

maybe you meant literature.author combined with literature.title?

what happened to shop.cake AND shop.sweets?

[redhillccwebmas]

You should know me by now, r937!. In examples that I don't understand, I like to take them out of context, so that I can apply them to the certain situation that I'm wanting to use it in. I feel this gives me a better understanding of what's actually going on within the example.

Well, in the SELECT clause, 'literature.title & " by " & literature.author AS literaturecombine' successfully bides both fields together to make one- e.g if the literature.author field had a value of 'dickens' and the literature.title had a value of 'famous five' both in the same record, the result from the concatenate function would be 'famous five by dickens'.

In my drop down menu in my form in Access I am using exactly the same concatenating function to output the same string (combines the two fields together) into the sculptures.literaturecombine1 field. So that it outputs the same result, 'famous five by dickens'. This is where I want to JOIN literature ON literaturecombine (created by concatenating function in SQL string) = sculptures.literaturecombine1 (created by concatenating function in Access drop down menu)

Alternatively, like you suggested in your last post, could I do something in my JOIN statement like;

JOIN literature ON literature.author AND literature.title = sculptures.literaturecombine1

The way I see it- this would ultimately give me the same result that I am looking for, but not having to use the extra field created by my concatenating function in my AS clause, 'literaturecombine'

Would this work?

EDIT - Sorry yes I did mean literature.title combine with literature.author = sculptures.literaturecombine1


Hope this makes sense, and thanks again for all the help

Kind Regards

James

[r937]

JOIN literature
ON literature.author & ' by ' & literature.title = sculptures.literaturecombine1

[redhillccwebmas]

Thanks so much for that solution, r937 - works fine now

Kind Regards

James

[r937]

thanks -- so you figured out the little hidden whoopsie, eh?

[redhillccwebmas]

yeah, I saw that one. Was that supposed to make sure I was concentrating?!