SEARCH YOUR SOLUTION HERE  

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:  319
View Complete with Replies




Related Messages:

ENUM is selecting a default value when it should throw an error   (114 Views)
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
Help with selecting DISTINCT   (105 Views)
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!
Help with selecting records   (112 Views)
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
Page 2 - Get price for 7 nights selecting 8 days   (106 Views)
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
Problem selecting rows in groups larger than x   (128 Views)
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.
HAVING clauses when selecting from 2 tables (pre 4.1)   (106 Views)
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.
Help with selecting multiple rows   (112 Views)
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
selecting a corresponding column   (116 Views)
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.
Help selecting database system   (104 Views)
do those tables have any indexes defined on them
MySQL assign value while selecting to use in another calculation   (95 Views)
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
selecting distinct   (122 Views)
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
SELECT statement not selecting?   (118 Views)
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'
selecting from one table based on another?   (116 Views)
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"
selecting from 4 tables   (114 Views)
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
Strange problem with selecting the top N rows from each group   (119 Views)
. 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
Sql join (?) selecting from multiple linked tables   (153 Views)
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.
Query help: selecting data points to form a GPS track   (185 Views)
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
Problems in selecting Rows count(column) > 1   (105 Views)
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
Help with GROUP BY selecting only one   (111 Views)
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
How to export multiple tables as one csv file   (99 Views)
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