Using Virtual Column In WHERE Clause
SELECT IF( some_id > 3, 'foo', 'bar' ) AS 'virtualcolumn' FROM table WHERE virtualcolumn = 'foo'
MySQL will complain that 'virtualcolumn' is unknow in WHERE clause.
However, if I have another query like
SELECT IF( some_id > 3, 'foo', 'bar' ) AS 'virtualcolumn' FROM table ORDER BY virtualcolumn ASC .
View Complete Forum Thread with Replies
Related Forum Messages:
Virtual Column
I want to get users informations from multiple tables and each user has many dependants (in tbl_dependants) and the dependants table has user_id foriegn key. Is it possible to SELECT user informations and beside the user results I create a virtual column contains dependants names for each user?¿?
View Replies !
Column Alias And HAVING Clause
I am having problems with the HAVING clause. I know it can use aliases but the alias I am using has a space in it, eg 'Device ID' My querry looks like (simplified, devID is a calculation) SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123' and it does not work but if I use SELECT devID AS 'Device_ID' FROM tblDevice HAVING Device_ID = '123' everything works. How can I use an alias that contains a space in the HAVING clause?
View Replies !
Column Alias In WHERE Clause
I wana select 2 fields of a table as a variable and then in that query use that variable like this SELECT money1+money2 AS money WHERE money > 10000 but mysql return an sysntax error Is there any way to use a varible like this in a query?
View Replies !
Using Column Alias Name In WHERE Clause
I am giving alias name to the column in SELECT clause and when I am using same alias name in WHERE cluase, it is giving error. We can use alias names in WHERE clause. Is there any solution or other way to achieve same functionality? QUERY: select concate(firstname, " ", lastname) as name from user where name = "Chetan Parekh";
View Replies !
Unknown Column In 'on Clause'
I have seen this a couple of times and what throws me for the loop is the column IS in the table. Unknown column 'cm.state_id' in 'on clause' select cm.*, o.userid,c.printable_name country_name, s.printable_name state_name, email_add as owner_email from b2gcar_mst cm, b2gcountry c, b2gowner_mst o left join b2gstate s on (cm.state_id=s.state_id) where cm.country_id=c.country_id and cm.owner_id=o.owner_id and cm.cid=Ɔ'
View Replies !
Unknown Column XXX In 'on Clause' - :(
I use mySql 5 and i get this particular error error: Unknown column 'p.ID' in 'on clause' when i'm using the below statement "select p.title, p.url, p.ID, p.category, p.submitted_user_id ,pc.cat_title ,u.username, u.ID, pc.ID as cat_id, from posts p, post_cats pc, users u left join post_votes pv on pv.post_id=p.ID left join post_comments pcom on pcom.post_id=p.ID where pc.ID=p.category and u.ID=p.submitted_user_id ";
View Replies !
WHERE Clause With Dynamic Column
query as follows: Code: SELECT DISTINCT(pr.id), IF(pr.Currency_Id LIKE 'USD',pr.Price*0.53559,IF(pr.Currency_Id LIKE 'EUR',pr.Price*0.795458,pr.Price)) as priceconverted FROM property as pr WHERE pr.Active=1 AND priceconverted >= 2000000 ORDER BY pr.price ASC I get the following error: Quote: #1054 - Unknown column 'priceconverted' in 'where clause'
View Replies !
Unknown Column 'p.insurance_id' In 'on Clause'
The other day we updated our live server with the latest stable release for fbsd, release 5.0.18, and we began to get 'unknown column in on clause' errors, error code #1054. At first I thought it was a problem with the installation, but I duplicated the exact errors on my personal windows xp pro pc by upgrading to release 5.0.18 from 5.0.11. On another query we get the following error: Line 258: Unknown column 'p.insurance_id' in 'on clause'. We tried creating a new field, called insurance_id_02, in the patient table and changing the code to look for that field instead of the p.insurance_id field in the on clauses, but mysql returned the same error. Any help would be greatly appreciated. I don't know if this is a bug in the new release or a strange quirk in our db.
View Replies !
Unknown Column 'some_value' In 'where Clause'
As I navigate through my (still built..) web site, i've created a link that directs to : /show_title?action=show&id=".title_id." that leads to a page where I run the query: $id = $_GET['id']; "SELECT title_name from titles WHERE title_id = $id" and i get: --> Unknown column 'CDA15FD' in 'where clause' although when I move the cursor on the link I get: /show_title?action=show&id=CDA15FD and CDA15FD DOES exist in my db!
View Replies !
#1054 - Unknown Column 'trxtal.groupno' In 'on Clause'
running this query iam getting the following error: select trxtal.groupno as groupn,artwork.artworkno as artworkno,count(trxtal.groupno) as grp from trxtal, mstcus LEFT JOIN artwork ON (trxtal.groupno=artwork.groupno and trxtal.classcode=artwork.classcode) where trxtal.groupno=mstcus.groupno group by mstcus.groupno having sum(artwork.artworkno)>0 #1054 - Unknown column 'trxtal.groupno' in 'on clause' This query was working fine before with my old version of mysql, when i upgarde i am getting this error....pls help MYSQL version:5.0.51b Apache version :2.2.8 PHP version: 5.2.6
View Replies !
Error 1054: Unknown Column Xxxx In WHERE Clause..
I'm trying to run this query $uneRequete = "SELECT * FROM Usager WHERE motDePasse = {$loginPassword}"; and I get this error message : Error 1054: Unknown column 'xxxx' in WHERE clause..... where 'xxxx' is the content of $loginPassword I don't know what is going on.
View Replies !
Error 1054 <42S22>: Unknown Column 'user' In 'where Clause'
am trying too delete a user from mysql database (which is used primarly for mediawiki). I typed in the following command in mysql command line client use wikidb; database changed Delete from user where User='Admin'; Too which I recieved the error Error 1054 <42S22>: Unknown column 'user' in 'where clause'
View Replies !
Error #1293: There Can Be Only One TIMESTAMP Column With CURRENT_TIMESTAMP In DEFAULT Or ON UPDATE Clause
I am using MySQL 4.0/4.1 version. And I am trying to add two timestamp columns to a single table. The columns are insert_date and updat_date to capture the date/time the record was initially inserted as well as the date/time the record was last updated respectively. When I try to set one column (insert_date) with a default value of CURRENT_TIMESTAMP and the other column (updat_date) with ON UPDATE CURRENT_TIMESTAMP, I end up getting the following error: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause Is there any way around this problem?
View Replies !
Virtual Domain
I have a dedicated server with 5 virtual domains under iis. i just installed mysql. how do i create a separate instance of mysql for each of my virtual domain so that one domain affects only tables created by that domain and not step on tables of other domain? let's say that each virtual domain is a separate web site with different fqdn
View Replies !
Virtual Fields
Is there a way to do a mysql select with - for lack of a better word - a 'virtual field'. In other words, I'd like to select on a field that is based on the contents of other fields. Like: If field A is greater than field B, select field A, otherwise select field B Specifically, I'm working with date fields. I have a table of events where there is a start date and an end date. Most events are one day only, so they have only the start date. I would like to select and sort on the latest date field - either the end date or, if there isn't one, the start date.
View Replies !
Db On A Virtual Web Server
i am running my web server with dsvr.co.uk. a uk based web server hosting company. I am trying to use a piece of software called smoothflash at the moment to show pics. dsvr have informed me that I have mysql on my server but i cannot for the life of me work out how to connect to a db, een to create a new db. www.smoothflash.com has the install manual on it and it all seems to straight forward. I know I have enabled mysql on my server, this has been varified by dsvr. but then I have no idea how I am meant to create a db, connect to it, i have mysql admin for windows but without knowing how to create a db, get a user name and password for it i have no idea how to get any further, if any of you clever people out there could help me in this matter i'd forever be in your debt.
View Replies !
Virtual Folder
in my database i have a table which stores a directory structure folderstructure table id, foldername, parentid parentid points to its parents "id" (:p ) i need to retrieve this info so that its sorted by foldername and its children are listed below also sorted by foldername; there can be a infinite amount of nested folders.... im not really sure how to go about this; i was hoping that maybe theres some magical sql statement that can do this for me i sort of dont want to put this info in a array then sort it with recursion (which i tried and failed), nor do i want loads of sql queries
View Replies !
Mysql To Virtual Domain
I have a dedicated server with 5 virtual domains under iis. i just installed mysql. how do i create a separate instance of mysql for each of my virtual domain so that one domain affects only tables created by that domain and not step on tables of other domain? let's say that each virtual domain is a separate web site with different fqdn
View Replies !
Not Working With Virtual Host
I set up a virtual host with Apache and my previously-working MySQL configuration now will not work! I did this a long time ago and can't remember how to fix the problem...Do i need to change some configuration so that MySQL runs on the virtual host and not localhost?
View Replies !
MySQL Virtual Hosting
When you create a new user, let's call him "userJohn", is it possible to set a different data foler for that user? I was thinking it would be great if you could keep user databases seperate from each other, kind of like every user has thier own folder on a virtual database folder, and access can be restricted for that user to the databases in their folder. So if user1 creates a database named "Database1", and later user2 creates a database with the same exact name, it will not give an error, and when user1 alters his "Database1," it will not effect any other users "Database1". Similair to webhosting, where every user has their own folder they keep thier data in.
View Replies !
Chew So Much Virtual Memory On Windows?
Have a fairly busy MySQL server using MyISAM and InnoDB tables on Win 2003 with plenty of available RAM (total 4GB, with about 1.5GB unused) which is using a massive amount of virtual memory despite having tweaked MySQL to use as much memory as it needs. RAM usage never gets much above 400MB, however Windows is reporting that MySQL is using a massive 1.5GB of virtual memory. Most other processes use a similar amount of RAM and virtual memory, so why would MySQL be using 4~5 times more virtual memory than physical memory? Especially when there is so much free physical memory?
View Replies !
Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000. I have need to use a reference in the outer from clause in a subquery in the select clause. Consider the following example: Select (select b.baitID from b where b.entrydate curdate()) as wantedBaitIDs from bait_tbl b; My actual need is more complex than this as part of it is a rough cross tab. If I try to define the table in the alias, not only do I lose whatever benefits there are in the particular join I would use in the outer from clause but would also require the join to be defined in each subquery, requiring it to be examined each time it is used. To be absolutely clear, in this example I want to use bait_tbl with the alias of b in the subquery. In my actual query I reference the same table twice with a different join set for each. I need to reference a particular alias as that has the join set I need.
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 !
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 Replies !
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 !
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 !
Between Clause
I have table of data as follows: Date 31-01-2007 12-31-2007 31-01-2008 31-12-2008 If i want to create an if statement that says if date falls between 31-01-2007 and 12-31-2007 put Year 1, if date falls between 31-01-2008 and 31-12-2008 put Year 2, etc. Do I use the following formula: CASE When Date >=31-01-2007 AND <=12-31-2007 THEN "Year 1,
View Replies !
SQL Clause
Is there a SQL statement that can accomplish this or is it something I will have to code to handle? I have a table, events, with a field, eventCategory. I need to pull each unique value of the field eventCategory . Otherwise, if eventCategory has the value "football" and it is repeated in > 1 records, I only need it to return it once. desired output: football mens basketball womens basketball undesired output: football mens basketball football womens basketball
View Replies !
IN Clause
When I attempt to run certain select statements, I get the following results: >select resource_id from tx_table where tx_id=5 gives back a value of 14 >select * from resource_table where resource_id in (14) gives back a value of 24 However, select * from resource_table where resource_id in (select resource_id from tx_table where tx_id=5) gives the following error: 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 'select resource_id from tx_table where tx_id=5)' at line 1
View Replies !
Using SUM In WHERE Clause?
CREATE TABLE IF NOT EXISTS `TABLE` ( `id` int(11) NOT NULL auto_increment, `username` varchar(255) NOT NULL default '', `points` decimal(3,1) NOT NULL default '0.0', PRIMARY KEY (`id`) ) ; INSERT INTO `TABLE` (`id`, `username`, `points`) VALUES (NULL, 'Player1', '1.0'), (NULL, 'Player2', '0.5'), (NULL, 'Player3', '0.0'), (NULL, 'Player4', '0.5'), (NULL, 'Player1', '1.0'), (NULL, 'Player2', '0.5'), (NULL, 'Player3', '0.0'), (NULL, 'Player4', '0.5'); Basically i only want to retrieve all the 'username' where the total points = 1 SELECT username, SUM( points ) AS pointme FROM TABLE WHERE pointme =1 GROUP BY username;
View Replies !
Where Clause...
I've a fixed query like select * from utente where nome=...; i can't change the query,but during my application i need sometime to do this query without where clause,that is i need all the utente rows without filtering on name.. Cause i can't eliminate where clause,is there in sql a key word that passed to where nome=..... means take all the names?
View Replies !
Not In Clause
I am using 5.0.24a version of MySQL. When i Fire a query on a table like this: Select count(primary key column) from table It gives a count of records present in the table approx: 43,000 When i fire a Query like this: Select count(primary key column) from table where primary key column not in (.,.,.,.,...) where ..,.,.,. contains exactly 4000 ids it gives a value of 4. To my surprise this is not the result which i expected and this should have been close to 40,000. The Query size is 17KB and my max allowed packet size too is 16 mb. I am not able to figure out where the problem is. When i fired the same query with console mode of mysql running i get a message as "1 Quit" at the end of the query processing as seen in the error log file?
View Replies !
MIN In HAVING Clause
I'm trying to run a query that will delete the record that has the lowest ID. But what I'm trying doesn't seem to have be working, and is spitting out Error: 1064. Anyone able to shed some light on the matter? Here's the query: DELETE FROM podcasts WHERE feedURL='$podcast' GROUP BY feedURL HAVING MIN(id).
View Replies !
Else-if In Where Clause?
I am having three text field and depending upon what user enters in these field we have to show result.(user can enter in any no. of fields i.e one ,two or in all if user leave any field blank then it sho not be included in search) if i have to write this code in java then i can simply use else if.... but how to write in SQL i dnt have any idea. eg: select a.id,a.name ...... from Table where (code depending upon fields x,y,z) (if any txt field is blank dn it sho not be included in search) (if user enter all field dn we have to use AND i.e x AND y AND z ) (but if x is blank then : y AND z)
View Replies !
Clause
SELECT 'Attendee Type'=ATTEND_TP 'Total Salary'=SUM(SALARY), 'Average Salary'=AVG(SALARY) FROM INDIVIDUAL WHERE SALARY > 0 GROUP BY ATTEND_TP I want to check if 'Total Salary' >100 how do i do that? Not salary. To be more precise I have column Z=a*b Now I want to check z>100. How do I refer Z in where clause?? Select a, sum(a*b) AS z from c,d WHERE Z>100 This doesn't work :P
View Replies !
Using CSV On WHERE Clause...
Been pulling my hair for the last couple of hours on these two On my form I have a number of checkboxes to choose which books you want to filter the results by. In the next page the forms gives me: $books = âÆ,201,50', the number are the IDs of each book in the database. After the WHERE, how can I make it so that: id_fk_inst_course_desc = any of the ids in my $books list, and not have to do (id_fk_inst_course_desc = 300 OR id_fk_inst_course_desc = 201 OR id_fk_inst_course_desc = 50) My other question, I have a lookup table for the books (called registry_inst) with the following fields: id_fk_registry | id_fk_inst_course_desc | date_create | create_by_id The numbers from $books correspond to the id_fk_inst_course_desc field. So in my SELECT clause I have: GROUP_CONCAT(id_fk_inst_course_desc ORDER BY id_fk_inst_course_desc ASC) AS course_taken Which puts in a list the books a specific user has taken. This returns a column named course_taken that has a list of the books: 100,500,303,etc. How can I modify it so that instead of returning a list, it returns fields (columns) named after each course number (100 | 500 | 303 | etc.), and a 1 if the user has taken it, or a 0 is the user hasn't taken the course.
View Replies !
Select Clause
We have a recordset with the following structure id (int), mls (varchar) What we need to do is within a single sql call we need to show all records where the mls = '' and all unique records where mls!='' In other words, there will be multiple records with duplicate mls entries. We do not want to show duplicate enties for where the mls has been set, only where the mls has not been set. Any suggestions?
View Replies !
No Parens In Where Clause
I cannot find now, what the precedence is in the where clause of a select statement, where "AND" and "OR" are mixed in, and there are no parentheses. For example: WHERE a=1 and b=2 or c=3 How would that be "parenthesized" internally in MySQL? What is the rule for precedence between and and or?
View Replies !
Order By :: Using IN Clause
SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the "IN" clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives).
View Replies !
More Than One Condition In A HAVING Clause
I have tried to run a query where the reuslts are restricted by a HAVING clause with 2 conditions, but the second condition seems to be ignored. for example SELECT userid, AVG(position) FROM `table1` WHERE (status='finished') GROUP BY userid HAVING (COUNT(*) >= 4 AND AVG(position) < 5.5) returns exactly the same as SELECT userid, AVG(position) FROM `table1` WHERE (status='finished') GROUP BY userid HAVING (COUNT(*) >= 4) even though there are values for AVG(position) returned that are > 6. Is it the case that I can't have more than one condition in a HAVING clause? have I got the syntax wrong? Is the behavior different in a later version? I am using "mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu (i386)"
View Replies !
Requiring Where Clause
It seems like I remember an option that when enabled, will require UPDATE and DELETE statements to have a WHERE clause, to avoid destructive oversights? Now when looking for that, I can't find it. Maybe I am thinking of a different system. If it exists, can someone point me to it? If it doesn't exist, maybe this is something that could be added in.
View Replies !
Ambiguous Clause
I am running the following query and keep getting the above error.SELECT callname, Action FROM health, hrecs WHERE health.compno = hrecs.compno AND compno = '17' I know its probably something simple though!!
View Replies !
BETWEEN Clause Not Functioning
Code: SELECT * FROM requests WHERE id != '' AND mailguide = 'yes' AND country = 'United States' OR country = '' AND submitted BETWEEN 1226469600 AND 1226728800 ORDER BY submitted, country, state, zip, postal, lastname ASC
View Replies !
Compound Where Clause
This is a silly question, and i've been pulling teeth trying to figure it out: I'm checking if a username and password match in the database, but the query i'm trying to use isn't working.. select * from userdb where username = 'a_user' AND password = 'a_password' how is this not working? I tried to google for 'compound where syntax' but to no avail.
View Replies !
|
TRACKER
