Insert then select

Hi guys

I've got a problem with an SQL statement for my assignment(i think this is the right section for this).

The exact question is: Create a new support ticket for a customer with respect to a given product; provide the customer with the ticket ID for future reference.

Currently i'm assuming that i've got to create a new ticket and display the inserted customer ID along with the ticket ID.

Below is the given format of the table:

ID INTEGER, (this is the ticket ID)
Problem VARCHAR(1000),
Status VARCHAR(20),
Priority INTEGER,
LoggedTime TIMESTAMP,
CustomerID INTEGER,
ProductID INTEGER

I can easily insert a new ticket, I can also display CustomerID and ID seperatly from the insert. But, I can't for the life in me get it to work as a single line statement with INSERT and SELECT, as I'm aware using INSERT then SELECT is used to insert data in differently rather than writing INSERT INTO <table> VALUES ... ...

So alas, if any can be of assistance I would be most grateful, and if anymore information is needed I'll try my best to tell you as much as I know. Thanks for your time non-the-less.

DH

presumably ID is an auto_incrementing number

when you insert a new row, you supply values for all the columns except the ID, and the ID gets the next sequential number

thus, you need to find a way to determine what that number that just got assigned is

in many database systems, there is a special function for this -- e.g. SCOPE_IDENTITY in SQL Server

failing that, you could easily determine the value by simply querying back the row that was just inserted using all the same values

But in theory isn't using this just declaring another statement after the initial INSERT statement? I can see where your coming from, and unfortunatly my SQL knowledge is somewhat lacking.

From what you said, is there a way I can just call the last row then?

you might like to mention which DBMS you're using

well i don't have to like it, but it is reconmended of me isn't it? hehe

Yea im using IBM DB2 and MySQL, I believe the version is 8.2, but for some reason I can't connect to it from home, even the alleged techs at my uni cannot understand why, and hence im testing the statements in MySQL at home, but it will be used in DB2 in the end

well i've never done this in DB2 but apparently you use the IDENTITY_VAL_LOCAL() function -- see http://www.craigsmullins.com/zjdp_006.htm

in mysql, you would use the LAST_INSERT_ID() function

It seems to work out ok

Thank you so much, if it wern't for the fact it'll cost me more for the flight than a pint I would actually buy you one!