Selecting From Column A Where Column B Matches Column A Twice?
Here's a table called Creatures containing Creatures and EntryIDs:
Code:
[Creatures]
Creature EntryID
==================
fish 100
cat 100
fish 200
bird 200
pig 300
bird 400
I would like to select all EntryIDs that contain both "fish" and "bird".
From the above table, "200" should be returned, seeing as both fish and bird are the only rows that both use the same EntryID.
How can I do this? Do I need to use Group By or Left Join or something?
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
View Replies !
Can I: Export Column, Optimize & Fill New Column With Value To Original Column?
I have a database with over 40,000 rows and 28 columns (learned how to import large files by changing php.ini!). Using Excel, I am able to: (A) copy and paste original column (e.g., SIZE_TEXT) (B) ALONG with each records' UNIQUE_KEY, (C) sort SIZE_TEXT column, (D) filter for unique values, (E) fill new column with its SIZE_TEXT__KEY, and then (F) import the new SIZE_TEXT_KEY value into the database by creating a new column or pasting over the columns original value. However, I think this can be performed, with less potential for error, using phpMyAdmin, but I haven't found anything demonstrating how to perform these actions. I searched the terms optimization and normalization. I am hoping one can use phpMyAdmin to: 1) create a new table with export column of the original table (SIZE_TEXT_TABLE), 2) sort new table for duplicates and show only unique values, then 3) import the column results into each Unique Records original column (SIZE_TEXT) 4) link new column values to Foreign Key (the Parent Table's SIZE_TEXT_KEY)
View Replies !
A Date Column That Auto-updates When Any Column In The Row Is Updated
Let's say I have a table with several columns. I would like to add a column called "date last updated". How this would work is that any time one of the other columns in that row was updated, the 'date last updated' column would update. I *know* how to do this with PHP, but here is the special part... Is there a way to have this automatically just within MySQL, so that EVEN IF if update a column manually through the command line or PHPmyAdmin, the 'date last updated' field updates.
View Replies !
Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part. I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:
View Replies !
Selecting MAX Value Of VARCHAR Column
MySQL Version = 4.0.17 I am having some troubles with a db and php written by another developer - unfortunately I do not have the luxury of altering the way in which this has been implemented - basically I am just trying to patch this up! Basically its a table of appointment time slots. The first column is VARCHAR(10) and stores the ApptID eg A9826 Each time an appointment slot is created, the PHP script runs the following SQL: SELECT MAX(ApptID) FROM AppointmentDates The script then gets the substring of the result so that it only has the numeric content - eg A9826 becomes 9826. The script then adds 1 to this value in order to create the next ApptID (dont ask me why its been implemented in this manner! ) Basically, the appointment ID has now reached 10000+ and when the SQL runs, it always returned 9999 as the max.....Therefore making the next ApptID = 10000 - creating a duplicate key error when attempting to insert... My question is - is there a limit on the value that MAX can return? Or is there some other explanation for this?
View Replies !
Selecting Certain Dates From Date Column
I wonder how this can be done, data is like this +---------------------+ | whenstamp | +---------------------+ | 2005-02-21 12:27:54 | | 2005-02-21 12:27:54 | | 2005-02-21 12:27:55 | | 2005-02-21 12:42:55 | +---------------------+ 4 rows in set (0.00 sec) mysql> I tried something like this :: $this_month = mysqli_query($dbcon,"select date_format(whenstamp,'%d-%m-%Y') as datex from logs where %m = '02'"); Basically I want to retrive records corresponding to the current month, I know I have hardcore '02' in their but that was just for testing and getting started.
View Replies !
Selecting Based On Matched Column?
Lets say I have table with two columns zip_work and zip_home. I'd like to construct a query looking something like this: SELECT {here is the problem} FROM myTable WHERE zip_home=99999 OR zip_work=99999 GROUP BY zip_home, zip_work LIMIT 100; In response I'd like get two column table with zipcode as the first column and COUNT() of matched users. The idea is to know how many users are working or living in 99999 zipcode.
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table. example of a sql command -------------------------- UPDATE table1 SET table1.columnname1 = table1.columnname2 FROM table WHERE blah blah blah Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then. for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together. any ideas?
View Replies !
Order By On Column And Reoder By Another Column
im using php and mysql, but i would like from the sql statement to get the results of a query order by a column and then reorder by another column that is: i want to select the last four entries accoding to the date. SELECT * FROM table ORDER BY Date DESC LIMIT 4 but I also want to order those 4 entries i got, according to their values at the order column like this SELECT * FROM table ORDER BY ordercol ASC LIMIT 4 how can i put this two together? i thougt of SELECT * FROM table ORDER BY Date DESC, ordercol ASC LIMIT 4 but it gets the last four entries and then if two entries have the same date it orders them according to the ordercol, how can it do it?
View Replies !
Re-name A Column
I am using Query Browser version 1.1.2, MySQL Administrator 1.0.14, MySQL Version 4.1.7-nt on Windows XP Service Pack 2 ALTER TABLE `purpleflavours`.`purchaseorderitem` CHANGE COLUMN `PurchaseCost` `PurchaseOrderItemCost` DOUBLE UNSIGNED ZEROFILL CHARACTER SET NULL COLLATE NULL NOT NULL DEFAULT 0000000000000000000000 Generates when I click the apply changes button in the MySQL Table editor window after changing the column name. This generates an error telling me I have an error in my SQL Syntax Can anyone tell me what is wrong? By the way I can use the table editor to change VarChar column names with no problem but I always get a syntax error if the column type is not a VarChar, i.e. Double, DateTime, MediumBlob, Integer etc.
View Replies !
As Column
I have a query that selects... YEAR(date) as theyear But I get an error every time I try to use 'theyear' in my WHERE clause... It just says 'Unkown column "theyear"'
View Replies !
Column Value
I made this query which has this sum fuction which calculates the value of the contents of the individual rows. e.g SELECT (SUM(case when R1='yes' then 1 else 0 end) + SUM(case when R2='yes' then 1 else 0 end) + SUM(case when R3='yes' then 1 else 0 end) ) AS yes, (SUM(case when R1='no' then 1 else 0 end) + SUM(case when R2='no' then 1 else 0 end) + SUM(case when R3='no' then 1 else 0 end) ) AS no FROM tblname1 where faculty_name = 'abc' AND student_name = 'efg; i want to display the values of the faculty_name and the student _name and one other column (comments) . but it shows error. i tried doing union but its not working either
View Replies !
Where Column
I initially thought when doing a query with a where clause that has WHERE something='value' that it would look for exact value. but when I ran a query like this: "SELECT * FROM table where id='65'or id='64' it brings back the row where id=65 (id is column type: id INT(5) NOT NULL auto_increment) (I was testing some queries to verify user input and its results in php)
View Replies !
SUM(column)
I trying to add a column where the field is equal to 0' I try something like this but does not work SUM(IF(pitcherstats.win = 0)) AS loose,
View Replies !
Associated Column
Consider the following dataset: ID / Category 1 / 1 2 / 1 2 / 2 3 / 2 If I SELECT where Category = 1 or Category = 2, I will get two results. What would be the correct way to accomplish obtaining only the IDs which contain BOTH Category 1 and 2 (In this example, ID 2)?? -- Basically a boolean 'AND' search of some sort... saying 'SELECT * .. WHERE Category = 1 AND Category = 2' obviously doesn't work.
View Replies !
Set Column Value
i have 2 colmuns that i want to use the difference between them as the value of another column. all columns are in the same table. the type of the 2 column is "Date". so what i want is to get the difference between the 2 dates and assign it to the 3rd column.
View Replies !
Row Into Column
i have this structure of tables: tehnic_detalis(id_detail, detail_name) products(id_product, id_detail, detail_value) I wish is to transpose row from tehnic_detalis into column for products in something like this short_description(id_product, detail_name1, detail_name2...)
View Replies !
Column Name Containing . (dot)
I have a query that contains some column names with a dot (.) as part of the column name. How can I select these columns in another query? Example: The first column is 'Name', the second 'Name.Old' Now: SELECT * FROM (qryName) works fine. SELECT Name FROM (qryName) works fine. SELECT Name, Name.Old FROM (qryName) does NOT work.
View Replies !
Column = Column + 0
Is column = column + 0 allowed as part of a MySQL query? I am experiencing problems using it, but I can't be sure if it is that or something else in the code..
View Replies !
Add New Column
I Want To Add New Colume In Exitsing Table Which Have 3000 Records New Columns Not Contain Null Values How To Add Then Not Null Column In Exitsing Table
View Replies !
Cannot Change Column Name
I inadvertantly named a field "in" mysql> select * from guestbook; +----+-------+-------+-----------------+----------------+ | id | fname | lname | comments | in | +----+-------+-------+-----------------+----------------+ | 1 | Mick | White | Test 123 | 20050208000000 | | 2 | Ann | White | Hello World | 20050213101948 | | 3 | | | It's a nice day | 20050213102405 | +----+-------+-------+-----------------+----------------+ mysql> select in from guestbook; ERROR 1064: 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 'in from guestbook' at line 1 But I can't seem to be able to change it: mysql> alter guestbook change in time_in; ERROR 1064: 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 'guestbook change in time_in' at line 1 The table: mysql> desc guestbook; +----------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+----------------+ | id | int(4) unsigned | | PRI | NULL | auto_increment | | fname | varchar(10) | | PRI | | | | lname | varchar(10) | | | | | | comments | text | | | | | | in | timestamp(14) | YES | | NULL | | +----------+-----------------+------+-----+---------+----------------+
View Replies !
Auto Column
I want to select a single row called "row1" but let mysql auto append a NON-exist column call "row2" of it. And i want that non-exist column be auto-increment. how to? If i "select row1 from mytable", then i get ----------- - row1 - ----------- - a - - b - - c - - d - ----------- but i want: ---------------------- - row1 -- row1 - ---------------------- - a - 1 - - b - 2 - - c - 3 - - d - 4 - ----------------------
View Replies !
Best Practice Column
Is there an accepted best practice on whether to store decimal currency amounts (e.g. dollars and cents) in MySQL decimal column types? Certainly, the most straightforward way is to use decimal columns. But it appears that such values are stored as ASCII strings, which would be inefficient for calculations (requiring conversion to a numeric type for each calculation). I guess the alternative would be to use integer columns (and multiply by 100 to store the value as total cents). My particular context is a PHP/MySQL sales system.sie.nctu.edu.tw
View Replies !
Updated Column?
is there a way to obtain only the affected columns after an update query? After an update query, that involves 10 columns, I know that only 2 columns are really updated (because the values of the other cols don't change). I need to know the names of that changed cols, for logging purpose.
View Replies !
Column Privilege
I am having problems granting column privileges. I want to grant the update privilege (only) to a column (called pass) in a table (called Acct). Here's what I get: mysql> grant update pass on practicedb.Acct to JohnDoe@localhost; ERROR 1064: You have an error in your SQL syntax near 'pass on practicedb.Acct to JohnDoe@localhost' at line 1 I don't understand how the syntax is wrong. I think I followed the instructions in the manual. I don't have any problems granting table privileges--just column.
View Replies !
Column Heading
By default Mysql select statement displays column names header only once at the first row. I want the column names for the select statment to be displayed after every say 15 rows of display. ( This is very much equal to set line size 15 in oracle)
View Replies !
Add A Column To Get A Total
I need to know how to add a column of numbers and get a total using my sql command. SELECT v.vid, title, purchdate, sum(cost) AS Total FROM Video v, Vidcopy vc WHERE vid.v = vid.vc I need to know how to add the cost column and get a total.
View Replies !
Use An Array Column
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
When To Use An Array Column?
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
Column Constraints
Currently our software product supports Oracle, DB2, and we want to see what it takes to also include MySQL. One stumbling block we have come across is that it seems that the CREATE TABLE command does not support the CHECK CONSTRAINT on a column. I know there is such a thing as the ENUM column in MySQL, but what if we want to set the constraint between two values. Example: CREATE TABLE TMPTABLE COLONE CHAR(10), COLTWO INT CHECK (COLTWO BETWEEN 1 AND 200).... Is there such a thing in MySQL.
View Replies !
Column Restruction
Is there a way of ensuring that a mySQL table column will contain one and only one true (with the remainder being false) I want to set a value of true (or '1') to a row and be assured that the remaining rows are false (or '0'). Or is this something that has to be done php side?
View Replies !
Making A Column
ihave some Access background. I've created a table for which I intended the email column to be the primary key, but didn't specify it at creation. Now, I'm trying to figure out how to specify it post facto.
View Replies !
Column Formatting
Being new to mysql I have some queries about formatting a date column 1. Can I use dd-mm-yy, my website is Australian 2. Can I leave the date field in one row empty if the date is unknown. 3. If I only know the year an event took place, can I enter just the year. 4. Would it be best if I used varchar for the column
View Replies !
Add Column With Sums
I have a table lets say Table with two colums ID and Value like the following one a 10 b 30 c 20 d 10 How is it possible with one sql statement to get the following table a 10 10 b 30 40 c 20 60 d 10 70 The 3rd column is the sum of the above element and the left one.
View Replies !
Adding To A Column Value
I have something like this:PHP Code: TABLE: sectionrating section GOOD BAD 1 23 5 2 12 10 A user can comment on a section and either the good or bad value will be incremented accordingly by a value between 1and 5. My question is how do i add the value the user supplied to the value already in the column.
View Replies !
|
TRACKER
