Selecting the results of an INSERT command


Just as the title says.
How do I get values of fields of an INSERT command
i.e I perform an INSERT command, and then I want to find the value of the ID in that field which is processed by the server (it's an AutoNumber field).

I've looked on http://www.w3schools.com/sql and it doesn't say anything about that.

Please Help!
Posted On: Wednesday 7th of November 2012 02:12:27 PM Total Views:  82
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




selecting from 4 tables

im trying to write a query that selects data from 4 tables. the problem is that there is no key linking two of the tables (senders are receivers). the first 3 lines below are good to go (i think), im just not sure how to add the line for the receivers table Code: select * from senders left join messages on senders.id = messages.senderidfk left join codes on senders.codeidfk = codes.id select * from senders left join receivers on messages.id = receivers.msgsidfk Code: - senders.id links to messages.senderidfk - senders.codeidfk links to codes.id - codes.msgidfk links to messages.id - messages.id links to receivers.msgidfk should i use subqueries or union i think i need at least one of those since using only joins isnt gonna cut it. right
VIEWS ON THIS POST

196

Posted on:

Sunday 21st October 2012
View Replies!

Strange problem with selecting the top N rows from each group

. the following query does not work in the mysql commandline, and php "mysql_query" calls. SET @num :=0, @provider := ''; SELECT product, productcode, row_number FROM ( SELECT product, productcode, @num := IF ( @provider = provider, @num +1, 1 ) AS row_number, @provider := provider AS dummy FROM products WHERE product LIKE '%ell%' ORDER BY provider, productcode ) AS x WHERE x.row_number
VIEWS ON THIS POST

195

Posted on:

Wednesday 24th October 2012
View Replies!

Sql join (?) selecting from multiple linked tables

Hi , I am trying to select from these tables: (seems I am not allowed to post links or images, so if you could be so kind if checking out the db schema at gliffy.com/pubdoc/1262894/L.jpg, it would be easier to see what I am looking for) I'm trying to write a query that, given a arbitrary id_tabla_enum, would answer back with: cf_cuadrante.id_fila, cf_cuadrante.id_colu, cf_cuadrante.valor, cf_fila.nombre, cf_fila.posicion cf_colu.nombre, cf_colu.posicion, I wont post the queries I am testing, because they are so bad it's not even funny. Hopefully the case is somewhat clear from what I posted and someone can lend a hand, at least cluing me in in what part of the FM should I be consulting.
VIEWS ON THIS POST

200

Posted on:

Wednesday 24th October 2012
View Replies!

Query help: selecting data points to form a GPS track

I have one more SQL challenge in this project.. and once again I turn to you... I have a table of timestamped lat/lon position records. Some come from a transponder and are coded "TRP"; some come from verbal position reports and are coded "POS". What I want to do is build a track based on the records. I'd like to use the transponder records as the primary source of data, only using a verbal record when there isn't a transponder record within 10 minutes of the record. times are in Unix Timestamps... My table looks like this: id, person_id, lat, lon, time, code 1, 1, 48.55, -124.55, 175747372, TRP 2, 1, 48.58, -124.54, 175747398, TRP 3, 1, 48.61, -124.53, 175748120, POS 4, 1, 48.66, -124.55, 175749120, TRP 5, 1, 48.65, -124.50, 175749125, POS I'd like to get everything except row 5 because row 4 is a TRP within 10 mins. Could someone help me with the query to make this happen Cheers, whiteatom
VIEWS ON THIS POST

239

Posted on:

Wednesday 24th October 2012
View Replies!

Problems in selecting Rows count(column) > 1

Look everywhere without finding answers to this query. I got two tables one with student information and other like the following table: Code: +-----------+------+----------+--------------+-----+---------+---------+---------+------+--------+ | grades_id | s_id | semester | school_grade | gpa | spanish | english | science | math | social | +-----------+------+----------+--------------+-----+---------+---------+---------+------+--------+ | 1 | 2 | A2006 | 8 | 8.0 | 7 | 9 | 7 | 8 | 9 | | 2 | 2 | A2006 | 8 | 7.8 | 5 | 8 | 9 | 9 | 8 | | 3 | 9 | A2006 | 9 | 7.0 | 7 | 7 | 7 | 7 | 7 | | 4 | 9 | A2006 | 9 | 7.8 | 8 | 8 | 7 | 9 | 7 | | 5 | 9 | A2006 | 9 | 9.0 | 9 | 9 | 9 | 9 | 9 | | 6 | 8 | A2006 | 9 | 7.0 | 8 | 7 | 8 | 6 | 6 | +-----------+------+----------+--------------+-----+---------+---------+---------+------+--------+ I want is a query that tells me which "s_id" number is repeated more than one time. I tried the next one: "SELECT students.s_id, s_name, s_lastname1, s_lastname2, school, school_grade, grades.semester, count(grades.s_id) AS total FROM grades, students WHERE semester='A2006' AND students.s_id=grades.s_id AND total>1 GROUP BY grades.s_id" But it says this message: "ERROR 1054 (42S22): Unknown column 'total' in 'where clause'" Then I decide to change the sentence a little bit: "SELECT students.s_id, s_name, s_lastname1, s_lastname2, school, school_grade, grades.semester, count(grades.s_id) AS total FROM grades, students WHERE semester='A2006' AND students.s_id=grades.s_id HAVING (count(grades.s_id)>2) GROUP BY grades.s_id" The errors still there. "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY grades.s_id' at line 1" Ok people any suggestion
VIEWS ON THIS POST

149

Posted on:

Wednesday 24th October 2012
View Replies!

Help with GROUP BY selecting only one

I have a raffle system i am making that selects 5 random winners from a table. I would to be sure that the same user doesnt get selected twice. Is there a specific mysql function for that I was thinking something like this: Code: SELECT * FROM raffle WHERE id='2' GROUP BY uid ORDER BY RAND() LIMIT 5
VIEWS ON THIS POST

206

Posted on:

Wednesday 24th October 2012
View Replies!

selecting one table from joined table.

I want to select a table where is currently joined (has foreign key). is this possible or do i need to join select both the tables first before i can get the value of a field inside a table joined i just want to select one table (where is currently has foreign key). is this possible
VIEWS ON THIS POST

174

Posted on:

Thursday 25th October 2012
View Replies!

Problem with MAX and selecting the right rows...

Hi all, I am having a really weird issue with the MAX function. Any insights would be greatly appreciated. I have a table with 3 columns: id, group_id, and name id and name are unique, but group_id isn't. So, each group_id can contain multiple id/name combos My goal is to return a list of highest value ids in each group, so the query I have is: SELECT MAX(id), name FROM table GROUP BY group_id ORDER BY id DESC For some reason... all the id's I got are right, but the returned names have absolutely no correlation with id's what-so-ever.... Is there anyway I can return the right rows\t It feels like right now, a random name in that group is being randomly pulled... I know I can do a self-join, but I am assuming that's much less efficient Any tip would be greatly appreciated,
VIEWS ON THIS POST

203

Posted on:

Thursday 25th October 2012
View Replies!

selecting events based on various date criterias

I'm a little stuck at how to tackle this one efficiently. I'll try to explain concisely... I have a form to search for events. Form fields include Postcode Event name Venue name When (radios) -> today -> tomorrow -> this weekend OR instead of 'When' they can choose a specific date. In the events table there are a various columns including startdate (of type datetime). There is also a column for each day of the week that the event is repeated. For example, if the event is repeated on a monday then the column 'repeat_mo will' have a value of 1. My difficulty lies in creating a query to retrieve all the relevant results. For example, a search could include the following variables from the form: postcode -> YO14 7LX when -> tomorrow So I'd need to go to the events table and retrieve all rows that have a similar postcode, a start date that is tomorrow OR has been flagged as being repeated every wednesday. The postcode bit is fine and getting tomorrow date (with php) is fine. But how do I put it all together to also take into account the repeat day flags Many
VIEWS ON THIS POST

145

Posted on:

Thursday 25th October 2012
View Replies!

I thought to be complex query while selecting

DB Experts I have a table with following structure: Code: id, name, address 1 a dd 2 b cc 3 c mm 4 d aa 5 e nn 6 f cc 7 g kk I have used the following query to retrieved the data: Code: SELECT a.id, a.name, a.address FROM rr_table a WHERE a.id NOT IN( (SELECT id from rr-table1 where condition) UNION (SELECT id from rr-table2 where condition) UNION (SELECT id from rr-table3 where condition) ) ORDER BY a.name ASC; Now i want to exclude some of the ids in the NOT IN() above. Lets suppose the above query returns like this: Code: SELECT a.id, a.name, a.address FROM rr_table a WHERE a.id NOT IN(1,2,3,4,5) ORDER BY a.name ASC; Now i want to exclude from NOT IN 2 and 3 so that the query will be SELECT ...... NOT IN(1,4,5). Just fro your information I am using PHP and PostGres database. And the values 2 and 3 will be stored in PHP variable as Code php: $AssignedIDS = "2,3"; Thank you in advance.
VIEWS ON THIS POST

127

Posted on:

Thursday 25th October 2012
View Replies!

WHERE number1 NOT LIKE '%bla%' - Still is selecting it

I have a search on my website, but there is a problem that I ran into. My query is the following: Code: SELECT * FROM videos WHERE IFilename !='' AND VTitle NOT LIKE '%%' AND VTitle LIKE '%".$search."%' OR GTitle LIKE '%".$search."%' LIMIT $offset, $rowsPerPage The search grabs results rows from both VTitle (video title) and GTitle (game title), but only VTitle results are displayed (so far so good). When someone searchs for a video, for example when searching for "gun", it displays results where VTitle includes "gun", but I also want all the videos for the game titled Gun to be displayed, (so far so good) so the query includes AND GTitle LIKE '%".$search."%'. Now, my problem is I store headings for games such as the word Walkthrough within VTitle, so a search for "gun" also shows an out-of-place "Walkthrough" result. These headings all start with "" in the database, which is why I have AND VTitle NOT LIKE '%%' I have also added that if IFilename (image) is blank than don't display the result, but it still shows up. The weird thing is if you do a search for Walkthrough it will come back with 0 results (i think because Walkthrough is in VTitle and I have AND VTitle NOT LIKE '%%'. Its still showing these blocked results if the keyword is included in the game title. Urgh! To see the search so you can see what I mean go here and use the search on the homepage www.gameanyone.com
VIEWS ON THIS POST

126

Posted on:

Thursday 25th October 2012
View Replies!

selecting first character of a field

Not sure if this is an SQL thing or if I should be doing this in Coldfusion. What I would like to do is select the first character from an entry in a field in a database and show it on the coldfusion page. Is this possible with SQL Many
VIEWS ON THIS POST

202

Posted on:

Thursday 25th October 2012
View Replies!

Query help, selecting fields from different tables

Hi , i'm pretty new to mysql and I am having some problems writing some queries that I need completing. This is for a sample system i'm creating. I have the following tables: Patient (patientNo, patName, patAddr, DOB) Ward (wardNo, wardName, wardType, noOfBeds) Containts (patientNo, wardNo, admissionDate) Drug (drugNo, drugName, costPerUnit) Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) The primarys keys are in bold now the queries I need to create are as follows: I need a query to list all the patients contained in 'Childrens' ward (wardType). I need another query to list all the patients that have been admitted today. A query to find all the patients being prescribed a certain drug (drugName) i.e 'morphine'. Another query to find the cost of the drug 'morphine' being supplied to a patient. Can anybody help me with these Or would anybody here be able to create these queries for me in return for payment Hope one of you kind folk can help me in some way! Many
VIEWS ON THIS POST

186

Posted on:

Monday 29th October 2012
View Replies!

Avoid selecting the last 5 rows entered?

Hi all, Is there a way to make sure the 5 rows I'm selecting from the database aren't any of the 5 that were the last to be added I was initially going to do 5 seperate WHERE clauses to make sure they weren't those id's, but that method seems a bit clunky, I'm thinking there must be a slicker way of doing it in just the 1 WHERE clause, but just can't think of what that could be!
VIEWS ON THIS POST

206

Posted on:

Monday 29th October 2012
View Replies!

Key for selecting data between two dates

I have a table with the following fields: - id int(10) auto_increment primary key - staff_id tinyint(3) not null foreign key to id in staff table - action text - action_date datetime not null the keys on this table is as follows: - primary key: id - foreign key: staff_id (to id in user table) - key: login_date It is important to note that the login_date contains a value in UTC. I now need to pull up a report of all the activities the staff member was involved in for a month. This table holds records dating back to January 1987 and currently holds 7321713 records growing at between 50 and 300 per day. For one of the management reports we need to get data from the table for a specific user or all users based on a time period. For example I am busy getting the data for December 2007 which is in the region of 7000 records and also for 2007 which is around 930126 records. The trick is that I'm looking at December in pacific time which is UTC-8. My query for December 2007 looks like: SELECT action, action_date FROM actions WHERE DATE_FORMAT(CONVERT_TZ(action_date, 'UTC', 'PST8PDT'), '%Y-%m-%d %H:%i:%s') >= '2007-12-01 00:00:00' AND DATE_FORMAT(CONVERT_TZ(action_date, 'UTC', 'UTC'), '%Y-%m-%d %H:%i:%s')
VIEWS ON THIS POST

121

Posted on:

Monday 29th October 2012
View Replies!

selecting max value of a column [was: Very Urgent, Need u guys help a lot]

PHP Code:
VIEWS ON THIS POST

224

Posted on:

Monday 29th October 2012
View Replies!

Outer join, and selecting two values from the same joined table

, hope i can explain this well enough... i have a main table called property, and a lookup table called ptype (property type). the propertry table has a "type" field, and a "subtype" field. both these fields look at the ptype table to get their titles. so, i currently have the query below, but i also want to add to this the subtype. trouble is the field name is going to be the same for both the type and subtype values, so how do i get at them both I tried "AS subtype" after the LEFT OUTER JOIN but this didnt work. SELECT * FROM property LEFT OUTER JOIN ptype ON ptype.pty_id = property.pro_ptype WHERE property.pro_id = 1 Many
VIEWS ON THIS POST

103

Posted on:

Monday 29th October 2012
View Replies!

selecting distinct rows

I have a query which returns rows containing about 10 columns. One of these columns contains information repeated between 0 or more other rows. I would like to only return one row (the first preferrably). I have tried to use the distinct keyword however it does not work in this situation. A simplified example: when not using distinct like this: SELECT col1, col2, col3 FROM ..... I get: foo | bar | blah | foo | bat | gur | foo | hat | grips | But when I use the query: SELECT DISTINCT(col1), col2, col3 FROM ... I get the exact same output, eg the DISTINCT even with the brackets does not eliminate duplicate rows on col1. I suppose this is because the other columns are not distinct. Is there a way to do this Hope that makes sense and any help would be greatly appreciated!
VIEWS ON THIS POST

145

Posted on:

Monday 29th October 2012
View Replies!

Sql selecting rows based on percentage

A while ago i was told to use Code: SELECT c.ID, c.family, c.rarity FROM creatures AS c RIGHT JOIN user_access AS a ON a.cid = c.ID WHERE c.type = 'Egg' AND a.uid=1 ORDER BY (RAND( ) * (CASE c.rarity WHEN 'Common' THEN 60 WHEN 'Uncommon' THEN 20 WHEN 'Rare' THEN 10 ELSE 4 END)) DESC LIMIT 1 to select a record by percentage. Now on a forum i've been told it doesn't actually work What i want is Common has a 60% chance of appearing, Uncommon 20%, Rare 14%, Ultra-Rare 6% How would i do it correctly
VIEWS ON THIS POST

84

Posted on:

Monday 29th October 2012
View Replies!

Help with selecting datatype

, I need to have a text field that will hold data that will possibly have other language characters, which datatype can I use to handle this
VIEWS ON THIS POST

101

Posted on:

Wednesday 7th November 2012
View Replies!