SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Not really a function, but SELECT shop.cake + '_' + shop.sweets AS cake_sweets should get you there.

  3. #3
    SitePoint Member
    Join Date
    Sep 2006
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MS Access I'm afraid - can I still use this function?

    Thanks for your help

    Regards

    James

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    SELECT shop.cake & "_" & shop.sweets AS cake_sweets

  6. #6
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much guys for your help. Got me out of some hot water there!

    Works perfectly!

  7. #7
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):-

    Code:
    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

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code:
    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
    Last edited by redhillccwebmas; Sep 26, 2006 at 04:40.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that last query looks fine -- further debugging would requiring knowing the exact column names and their datatypes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    JOIN literature
    ON literature.author & ' by ' & literature.title = sculptures.literaturecombine1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for that solution, r937 - works fine now

    Kind Regards

    James

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks -- so you figured out the little hidden whoopsie, eh?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Guru redhillccwebmas's Avatar
    Join Date
    Jun 2005
    Posts
    989
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, I saw that one. Was that supposed to make sure I was concentrating?!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •