SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert multiple unknown foreign key values

    Guys, I posted this on a couple of sites and I'm surprised I haven't received a response because honestly I thought it was a common situation. Then it hit me to come to Sitepoint, so maybe you can help me out? I'm using MSSQL. Thanks.

    I'm trying to insert some data into 1(notes) table that has several foreign keys. The problem is that I'm not given the id of the foreign key, but another field from each table. How do I find the PK of another table from another field in those tables and INSERT the pk into the notes table? For example:

    cars
    --------------------
    (pk) car_id (ai)
    car_name

    images
    --------------------
    (pk) image_id (ai)
    image_name

    notes
    --------------------
    (pk) note_id (ai)
    (fk) image_id
    (fk) car_id
    note_text
    date_created

    I supply the values :
    noteid(not really supplied because auto incrmenting),mustang.jpg,Mustang,Stuff about this car,5/3/12

    I have tried:

    INSERT INTO notes (image_id,car_id, note_text,date_created)
    VALUES ( (SELECT img_id FROM images WHERE image_name = 'mustang.jpg'), (SELECT car_id FROM cars WHERE car_name = 'Mustang'), 'Stuff about this car', '5/3/12'))

    I always get the "Subqueries are not allowed in this context. Only scalar expressions are allowed."

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    INSERT 
      INTO notes 
         ( image_id
         , car_id
         , note_text
         , date_created )
    SELECT img_id 
         , ( SELECT car_id 
               FROM cars 
              WHERE car_name = 'Mustang' )
         , 'Stuff about this car'
         , '5/3/12'
      FROM images 
     WHERE image_name = 'mustang.jpg'
    will fail if there is more than one car called 'Mustang'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Real quick, I appreciate the quick response and I'm going to try this in a little bit. Question though looking at this real quick:

    Code:
    SELECT img_id 
         , ( SELECT car_id 
               FROM cars 
              WHERE car_name = 'Mustang' )
         , 'Stuff about this car'
         , '5/3/12'
      FROM images 
     WHERE image_name = 'mustang.jpg'
    Forget the car subquery in the above statement, you have SELECT img_id,what is returned from the subquery for car, 'Stuff about this car', '5/3/12'. I don't understand how this won't error because this query should be expecting:

    SELECT image_id, car_id, note_text, date_created but you have the literals ['Stuff about this car', '5/3/12'] . Would this SELECT statement error because the fields 'Stuff about this car', '5/3/12' don't exist? Thanks again for the help.

  4. #4
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I dont see the need for the first two tables. Since car_name and image_name will be specific to each record in the notes table !! So just add those details directly into the notes table.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notta View Post
    I don't understand how this won't error
    best way to find out is to test it

    run the SELECT by itself and see what it produces, then stick it into the INSERT statement as shown once you're convinced

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies guys. I have been swamped at work and had not had a chance, until now, to really look at your responses.

    I don't see the need for the first two tables.
    I haven't actually designed the database, but wanted to test out a few things before I did. Forget this example for a second, are you saying that you'll usually not have to INSERT multiple primary keys from other tables into a table? I see myself needing this a lot in the finished design.

    best way to find out is to test it
    Sorry man. As I said I was at work and really couldn't test it. I've got some time and tested it. I ran both sub queries individually and they both worked fine. When I run the full query I receive the following error in SSMS 2005 Express.

    Code:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '`'.
    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near ','.
    So SQL Server is not liking the combination of the 2 queries. They both run fine independently but when run together I receive the error.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notta View Post
    Incorrect syntax near '`'
    you've typed something incorectly see what i did there?

    that's a mysql backtick, and has no place in a ms sql server query

    look again at your sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha, I can't believe it. I swear man I'm not a complete noob. I replied with utter confidence that I had checked and re-checked that everything was entered right. Sure enough there was a little gray tick that looked like a piece of dirt on my screen I mean it was tiny Thanks brother.


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
  •