Selecting one or more records by primary index


I'm trying to create a query that will extract one or more records from a database table. I've tried:

Code: SELECT * FROM 'accommodations' WHERE 'accn_id' = 2007 OR 'accn_id' = 2024 which gives an empty result -- that's to say there's no error message, but no rows are returned, even though there ARE records with those accn_id values.

I've also tried:
Code: SELECT * FROM accommodations WHERE 'accn_id' = 2007 and that does give an error message, so presumably I've not got the syntax quite right

I can't yet see what I've done wrong, but I hope I'm close.
Posted On: Thursday 25th of October 2012 10:30:51 PM Total Views:  440
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




ENUM is selecting a default value when it should throw an error

I have an ENUM with a NOT NULL clause. If I insert into the table omitting the ENUM column, MySQL just defaults itself to the first value in the ENUM. This must be expected behaviour but I am surprised that strict mode allows this. Server version: 5.0.77 CREATE TABLE `packingContainers` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY `description` varchar(45) NOT NULL, `packagingContainerType` ENUM('Box','Prepack','Tray') NOT NULL, `brand` varchar(45) NOT NULL, `minQty` int(11) NOT NULL, `maxQty` varchar(45) NOT NULL ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO packingContainers (description,brand,minQty,maxQty) VALUES('Test',1,50,100); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM packingContainers; +----+-------------+------------------------+-------+--------+--------+ | id | description | packagingContainerType | brand | minQty | maxQty | +----+-------------+------------------------+-------+--------+--------+ | 1 | Test | Box | 1 | 50 | 100 | +----+-------------+------------------------+-------+--------+--------+ 1 row in set (0.00 sec) mysql> SELECT @@sql_mode; +-------------------+ | @@sql_mode | +-------------------+ | STRICT_ALL_TABLES | +-------------------+ 1 row in set (0.00 sec) Unless I have done something wrong here, can anyone suggest a workaround to stop this happening
VIEWS ON THIS POST

196

Posted on:

Thursday 11th October 2012
View Replies!

Help with selecting DISTINCT

Hi Guys Battling to get the following type of query to work. An example of my problem is if I had a table with 4 columns, id nickname score1 score2 If I wanted unique records that are ordered by score, then I use: SELECT DISTINCT nickname FROM gametable order by score1,score2 Great, but I also want the id returned with the corresponding nickname, if I do the following, it messes the query up: SELECT DISTINCT nickname,id FROM gametable oder by score1,score2 Any ideas!
VIEWS ON THIS POST

159

Posted on:

Friday 12th October 2012
View Replies!

Help with selecting records

I have the following sql statement: Code: SELECT Subpages.ID as subpageID ,Subpages.fkMainPageID as fkMainPageID ,Subpages.strDisplayName as subpageDisplayName ,Subpages.strPageName as subpagePageName ,Subpages.blnActive as subpageBlnActive FROM my_Subpages as Subpages WHERE Subpages.strPageName = 'testpage' AND SubPages.blnActive = 1 ORDER BY SubPages.intDisplayOrder Where I want to be able to select all the subpages that share the same fkMainPageID value. How would I do such a thing
VIEWS ON THIS POST

209

Posted on:

Friday 12th October 2012
View Replies!

Page 2 - Get price for 7 nights selecting 8 days

changed back to use php to substract the departure date, however I have another query that checks availability that uses date_sub also, but in this case its substracting the dates that are in the table, I suppose there is nothing that can be done to performance that query, however at this moment it works fine: SELECT arrival, departure, propiedad from bookings where ( propiedad = '$propiedad' ) AND (('$arrival' BETWEEN arrival AND date_sub(departure, interval +1 day)) or ('$departure' BETWEEN date_sub(arrival, interval -1 day) AND departure) or (arrival = '$departure') or (arrival >= '$arrival' AND departure
VIEWS ON THIS POST

164

Posted on:

Friday 12th October 2012
View Replies!

Problem selecting rows in groups larger than x

As the title says, I'm trying to build a query that returns rows belonging to a group larger than 10, in this case. What I've come up with is: select * from headerdata group by place_name having count(place_name) >10 that returns a single row for any group larger than 10, so I'm close. I've tried a few sub selects, but they've been failures ranging from epic to amusing. Apologies if this has been answered before. I did a search and didn't find any obvious answers, but I could easily have been searching for the wrong thing.
VIEWS ON THIS POST

201

Posted on:

Friday 12th October 2012
View Replies!

HAVING clauses when selecting from 2 tables (pre 4.1)

I'm trying to do a GROUP BY with a HAVING clause to return the information for the latest version of a project, stored across two tables. Here are the relevant tables: Code: CREATE TABLE t_project ( id int(10) NOT NULL auto_increment, title varchar(63) NOT NULL default '', description longtext NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM; CREATE TABLE t_history ( id bigint(15) NOT NULL auto_increment, project_id int(10) NOT NULL default 0, version varchar(10) NOT NULL default '', changes longtext NOT NULL default '', date date NOT NULL default '0000-00-00', PRIMARY KEY (id) ) TYPE=MyISAM; For each project in t_project, there will be a number of versions, version 0.1, 0.2, etc., each of which has its own entry in t_history. I'd like to get all the information from t_project joined with the row that has the highest version in t_history for each project. Can this be done in one query Once again, I'm forced to do without nested queries as I'm working with an old version of MySQL. This is what I have at the moment: SELECT p.title, p.slug, p.desc_short, h.project_id AS project_id, h.id, h.date AS updated, h.version FROM t_project AS p INNER JOIN t_history AS h ON p.id = h.project_id GROUP BY project_id HAVING h.id = MAX(h.id) Unfortunately, this returns no rows. I've looked all over the place for a solution to this, but all I get is the same tutorial on how to select employees from departments! Of course I could do it in two queries, but it feels like there should be a better way.
VIEWS ON THIS POST

216

Posted on:

Friday 12th October 2012
View Replies!

Help with selecting multiple rows

Hi Guys, Can anyone possibly help me out here I am trying to achive the following but cant work out the correct format: PHP Code: $SQL="SELECT'productCode'FROMsizesWHEREsize6
VIEWS ON THIS POST

169

Posted on:

Friday 12th October 2012
View Replies!

selecting a corresponding column

Okay, this should be simple, but I've spent a day trying to figure it out to no avail. I have this table: Code: wkpts teamid year wk1 wk2 wk3 1 2003 31 41 51 2 2003 32 42 22 3 2003 53 43 23 1 2004 36 42 70 2 2004 26 58 55 3 2004 55 44 61 The query I want would display the MAX pts they scored for a given week, along with the year it occured. Such as for week 1: Code: teamid year wk1 1 2004 36 2 2003 32 3 2003 55 I can easily get the MAX(wk01) for each teamid, but I cannot figure out how to get the corresponding year. I've tried various subqueries and futile attempts using a HAVING clause, but no luck. I know this shouldn't be that hard, but I can't figure it out.
VIEWS ON THIS POST

175

Posted on:

Friday 12th October 2012
View Replies!

Help selecting database system

do those tables have any indexes defined on them
VIEWS ON THIS POST

187

Posted on:

Sunday 21st October 2012
View Replies!

MySQL assign value while selecting to use in another calculation

Err, yea, didnt explain that too well in the subject. Basically, what I want to do is have a select statement that allows me to perform a calculation (dividing two columns) then assign this to a variable that I can use in the MySQL statement. Currently I have: Code: SELECT r.id , SUM(s.ab_hourstd) as total_abhourstd , s.ab_crewstd , SUM(s.ab_crewstd) as total_abcrewstd , s.hour_tmax , SUM(s.hour_tmax) as total_hourtmax , (SUM(r.run_time)/60) / SUM(s.hour_tmax) AS tmax_units As you see, in the last line of the statment I perform a calculation. How do I then refer to the value of this calculation in the next line When I try to use tmax_units it errors out. , I wanted to avoid coding this kind of information in the app, which is what led to that first query you see. In an effort to maybe allow you to help me I will try to give a detailed explanation of what I am trying to do. My 'reports' table contains an id, item, line, run_time, case_count, actual_crew, shift, and date. My 'sku_data' table contains sku, description, hour_tmax, std_crew. Now, ultimately the goal is to display information grouped by the report.line. I would also need to be able to group by report.line as well as report.date and report.shift so I can view data broken down by date, by line, and by shift. Now, just getting the data I can do. However, its the calculations that are really throwing me off. Lets say I have the following data in the reports table. For each individual item I will need to lookup some values in sku_data table, and perform calculations based on these lookup values. Is by doing the calculations row by row the only way to accomplish this Then the application does totals For example, in the sample data you see there are 2 rows with the same line number, on the same date. However, they both have different item numbers. So, for each of these I need to lookup the correct values in the sku_data table and perform my calculations. The only way I can, in my head, think about doing this would be doing the final totals in the app side. If I need to view data by date, and shift I would order by this information, and have logic to do the rest. Do you see anything else here Am I totally missing the boat on something
VIEWS ON THIS POST

180

Posted on:

Sunday 21st October 2012
View Replies!

selecting distinct

I have a table that is set up like: Code: id | artist | song | album Where there could be multiple entries that had the same artist and song. I want to select distinct how that if there are rows which the artist and song are the same as other rows, I only get one occurrence returned. So, something that would look like this (but obviously this doesn't work): PHP Code: $sql->query("SELECTDISTINCT(artist,song)AStitleFROMlifethemesongLIMIT$x"); Is there anyway to do this
VIEWS ON THIS POST

185

Posted on:

Sunday 21st October 2012
View Replies!

SELECT statement not selecting?

I'm trying to select the thumb_name from the photo table where the provider (in the album table) is test, but the query isnt working... Whats wrong (album_id is the linking fields of each table..) SELECT thumb_name, provider FROM photo INNER JOIN album ON photo.album_id = album.album_id WHERE album.provider = 'test'
VIEWS ON THIS POST

170

Posted on:

Sunday 21st October 2012
View Replies!

selecting from one table based on another?

ok say I have a table for products and another table for the type of product it is like this: ok so I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" \t
VIEWS ON THIS POST

192

Posted on:

Sunday 21st October 2012
View Replies!

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

210

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

211

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

207

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

245

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

155

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

223

Posted on:

Wednesday 24th October 2012
View Replies!

How to export multiple tables as one csv file

How would a client of mine export a mysql database in say a .csv format, when the data is abstracted across multiple tables (Since having a crack at normalising my database, it has become less human-readable!) Is there way in phpmyadmin to perform an SQL command that say joins all tables together and then export the results of the SQL That could potentially be a large amount of data though... Any ideas so the client can have a readable spreadsheet file ie. .csv
VIEWS ON THIS POST

181

Posted on:

Thursday 25th October 2012
View Replies!