MYSQL :: Reference To Already Retrieved Values In Query

One More Column (country) Which Is Not Retrieved
myTable1

(id) city
(1) Moscow
(2) Lodon
(3) Seoul
(4) Denver
: :

myTable2

(id) city country
(1) Moscow Russia
(2) Lodon England
(3) Seoul Korea
(4) Denver America
: :
Let's suppose I have myTables like the above.

And I have the codes below.

code1

select city
from myTable1
where city like '%n%'

code2

select city
from myTable2
where city like '%n%'
(fact1) Code1 and code2 are same except their table names.

(fact2) They will have same results.

(fact3) MyTable1 has two columns, and myTable has three columns.

(fact4) The 3rd column country is not retrieved in code2.

If myTables have tens of thousands of records each, Is each perfomance Same? or is code1 better in perfomance than code2?.

Using Value Retrieved As Table Name/field Name
"SELECT a.name, a.type, a.color, b.* ".
"FROM db.table1 AS a LEFT JOIN ".
"db.table2 AS b ".
"ON (a.id = b.id)".
"WHERE a.qid = '$id'";

1. Ok, now for the second table, I want to use the value retrieved in table1 as the table name, like this:
"db.(a.type)AS b" but obviously this is not working.

2. And for table2, I want to select a field name based on the result in table1, like this:
"SELECT a.name, a.type, a.color, b.(a.type) " and this isn't working also.

Getting Text Retrieved From Mysql To Fit Tables Properly
I have been working on a really simple mysql/php set of pages.

Page 1: Input 3 peices of data (Title, Date and Text)

Page 2: Display all news that has been inputted

I have it working, except for one problem. My website is built with html, and tables. I want it so that when the text gets to the edge of the table, it drops down a line and starts again on the next line - so it doesn't carry on and on.

Theres no problem with title and date - they're just a few words, but the text part of the news is usually 5 or 6 lines long. This means that it wont all fit on one line without ruining the entire website display. Code:

Table Rows Inserted By Embeddded MySQL Cannot Be Retrieved By MySQL Tcp Client
I have encountered a weird behavoir of embedded MySQL. Through a
simple program I made (in Delphi 6), I can insert rows to a table
through embedded MySQL and I can retrieve the rows from my program.

But using mysql.exe which points to the same table (through 127.0.0.1,
with the same basedir and datadir), I cannot see the rows I inserted
using my embedded program. Another way around, the rows inserted by
mysql.exe can be seen in my embedded program. But after a row is
inserted by mysql.exe, the rows inserted by the embedde program are
deleted (or cannot be queried).

Can anybody explain why is this? Is there a way to fix it?

How To Sum Two Aliased Values In A Query
I need to sum two aliased values in a query in order to order the results.

The query looks like this:

Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows..
I want like distinct values from three tables with ic number.
Which is posted by user...

"SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode,
DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1,
DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate
FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'";
This query return many rows i dont know why, i want only one row which is
inserted by the user as customer id.

SUM Query And Comparing Values In WHERE Clause...
Here is my query:

Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column. The reason is because I have a column that just contains just the YEAR of a given report. I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).

NULL Values In SELECT Query
I need to know how to push null values to the bottom of a query sorted by ASC. I know if you sort by DESC the null values are placed last. How can I push the null values to the end of a query while still sorting using ASC? Anyone?

Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure.

I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs.

For example, if this is my table:

Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table.

I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded.

update boxes b inner join messages m
on b.ctnnumber = m.ctnno
set b.consigneerecv = m.CName,
b.consigneerecvdate = m.DateRcv,
b.phrecventered = "Y",
b.PhilStatus = "delivered",
b.prevreleasestatus = b.releasestatus,
b.releasestatus = "delivered",
b.PhilStatusDate = m.smsrecvdate,
b.phdelprice = "0.00",
b.phdelamt = "0.00",
b.recvrelation = m.Relation,
b.APRecventered = m.smsRecvDate
where b.consigneerecv = ''
or b.consigneerecv = 'NA'
or b.consigneerecv is null;

I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this:

Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1),
table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....

How To Generate Sequence Values During Query
I have query like below:

SELECT part_no,part_nm,qty FROM tb_stok_out ORDER BY part_no

as result:

part_no part_nm qty

aaa asdfd 3
abab sdfsdf 4
abab adfdf 5

Is it possible in mysql to generate sequence number using query, so the result will be like below :

1 aaa asdfd 3
2 abab sdfsdf 4
3 abab adfdf 5
... etc

Sql Reference
could anyone point me in the direction of a good reference list containing lots of sql commands. Im learning and i thought it would be helpful to have a list of possible commands i could use. Is there one exists thats any good? MAybe one that gives a context example for each command aswell.

So far ive found sql fairly easy...it was harder getting mysql installed on my machine! I mean it seems, so far, that in comparison to php its very simple.....or am i just at the beginning?!?!

2 Db Reference?
Is it posible to link two tables from different dbs together? I want to reference there alike keyz. Ive read on some forums that you can accompish this using triggers but still not sure if thats the correct thing to do.

I have and account db with a accounts table and seprate dbs for each category. Assumming that each category is going to have more that 7 million records in 1 of its tables. I decided to separte them into diff dbs for performance.

I was thinging that account db could manage the different categories since the users are going to place records in each category. I was going to create the same account table in the different categories db.

Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories.

What I want the query to return is this:

Date | ShopperID | VATable Amount | Non VATable Amount|
2006-3-1 | 802135 | 146.95 | 54.00 |

Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above.

I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product:

+------------+----------+-----------------+---------------------+
| Date | ShopperID| VATable Amount | Non VATable Amount |
+------------+----------+------------+---------------+
| 2006-09-04 | 805284 | 0 | 64.00 |
| 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a
| 2006-09-04 | 805287 | 3.25 | 0 | customer has
| 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products
| 2006-09-04 | 805287 | 4.95 | 0 | - I need total
| 2006-09-04 | 805327 | 0 | 53.95 | under each
| 2006-09-04 | 805335 | 0 | 58.95 | ShopperID
| 2006-09-04 | 805414 | 0 | 64.95 |
| 2006-09-04 | 805414 | 3.25 | 0 |
| 2006-09-04 | 805414 | 0 | 64.00 |
| 2006-09-04 | 805414 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.95 |
| 2006-09-04 | 805423 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.00 |
| 2006-09-04 | 805423 | 3.25 | 0 |
+------------+-----------+----------------+--------------------+

Heres the query currently getting these results (without the GROUP BY ShopperID).

SELECT Date,orders.ShopperID,
CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag'
THEN items_ordered.price -- If it is an accessory add VAT
ELSE 0
END AS 'VATable Amount',

CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?('
THEN items_ordered.price -- If size 1 - 6 add price to other column
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?('
THEN items_ordered.price -- If size kids 12/13 add price to other column
ELSE 0
END AS 'Non VATable Amount'
FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <=

&#55614;&#57158;-9-31');

How Can I Return (x,y) Query Values To Create A Graph?
Im trying to build a graph of values from a counterTable. (Eg - get a list of how many times a page was requested in a week period with each y value being a day).

If the table looks like this...

A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help
me with a problem I'm having. I do not know whether this is possible or
not...

I have a large table of stock price data which is straight-forward
enought. I can select prices based on a ticker and date ranges.
However, what I'd like to do is to select prices only when, say 75% of
them are non-zero (with the goal of eliminating new/suspended/delisted
stocks).

Of course I could just select where price > 0, but then I might get
only a few rows where this is the case. What I would like to do is
always get the full date range of prices, but only if >75% are there.

Query To Insert 2 Foreign Key Values In Table
I want to insert 2 foreign key values along with some other value into table ....

Many To Many Cross Reference
I am looking to pick up the relationship between items in a cross-reference table. For example, searching for c OR f (bracketed below to highlight them), I want to list the other right hand side keys that are linked by the left hand side key. So in the below case, "a" is linked 3 times due to (c), b is linked twice due to (c), d is linked
twice due to (c) and (c OR f) and finally e, linked once due to (c).

1 | a
1 | b
1 | (c)
1 | d
2 | a
2 | b
2 | (c)
3 | z
4 | a
4 | (c)
5 | (c)
5 | e
6 | (f)
6 | (c)
6 | d

Just wondering what the best way of doing this is, and how the result can be ranked by the number of hits (so a,b,d,e) and how to get the number of links (3, 2, 2, 1)

Reference Integrity
I would like to check few things regarding reference integrity issue.

Let say i have set foreign key constraints on a particular table, I would like to know if the system would also check and make sure that the referenced key does exist on the Master table before committing new record insertion ?

Cross Reference
I have a cross reference table with two ids like such:

mappings
dealerId -> int
categoryId -> int

is there any way to quickly select all dealerIds that appear in all the specified categories. Something like this:

SELECT dealerId WHERE categoryId IN ( 14,64,23 ) GROUP BY dealerId

Except, above will return all dealerId's that appear in any category instead of all categories.

Reference ID During An INSERT
I'm working on someone elses PHP code which updates an existing database
- One of the programmers used to generate a unique key (a transaction
code) for every purchase using a hash - A second programmer thought the
idea of using the unique numeric ID field, prefixed with the two letters
'Tx' would be more sensible. He did this by first making an insert to
the database with whatever transaction details he had, then getting the
mysql_insert_id() and then going back and updateing the exact record he
just inserted except to modify one field/column with the value he got
from his mysql_insert_id() prefixed with the letters "Tx".

I am pretty sure I can do this all in one go and I'd appreciate if
anyone can help direct me here...

basically, I want to do something like

mysql> insert myTable set firstname="elvis",txcode='tx'+ID;

Where ID is the ID of the new/active record being inserted.

The insert works, except txcode equals zero - when the unique ID for the
row is/was 2795 - and it does not even have a 'tx' prefix which leads me
to believe its performing some maths to my request.

Can what I want to do be done in a single step, or must I go back and
update the record after the insert? .

Reference Other Tables
Is it possible to have a column in a table that can reference rows in another table? I'm trying to create a database of cookbooks and recipes in those cookbooks, so I want to have one table with the recipes and another table with the books that says recipes number x, y, and z are in this book, where x, y, and z are primary keys in the recipe table.

Showing Query Result With Default Values When No Data Available
I have a scenario that user can ask for certain data say income & expense for last 3 months on monthly basis. I have wrote following query which works well but it has an issue:

Let say I have asked for data of FEB 2007, JAN 2007, DEC 2006 using the following query it shows me correct result when all three months have some data but it doesn't show me the desired result when you don't have data in one or more months.

Any suggestion to get result when there is no data:

Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query:

SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact
FROM surfacings
INNER JOIN
(SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider)
AS tmp
WHERE surfacings.timestamp = tmp.most_recent;

to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....

Reference One Record From Another In The Same Table
I have a table containing data. When one record is marked as moved I set "moved_id" to the id of the new record. How can I read from the new record as if I were reading from the original with a single SELECT statement?

For example, when I get to record23, data is returned from record99 without having to do another SELECT statement.

How To Create A Reference Table
I have a table in the database with 4 columns. The Autonum column is the primary key. The other 3 columns are T1, T2 and T3. T1 is an auto increment column having numbers 1,2,3....

I do an insert to insert a row into this table. I want that once the insert is done, the entry into the T1 column should be old data+1 meaning if the last entry in T1 was 5 then it should be 6 for this new insert.

Someone suggested I create a Reference table--any ideas how to do tht---what are the other options to do this?

How To Have Foreign Key Reference Two Subclasses
I have been using a book as a guide to develop my database schema but have come across a problem with referencing two subclasses.

The book i use (general, not mysql explicit) advises that to declare a foreign key for two subclasses the syntax would be..

CONSTRAINT fk_name FOREIGN KEY (fk) REFERENCES table1(id) AND table2(id)

This is throwing an error on the 'AND' statement. Just wondering if anybody knew the correct procedure for referencing both subclasses with a single foreign key.

SQL Statements Reference Requirements
I am in the wee early stages of learning SQL in general, and consequentially growing very at home with MySQL.

Also being a bit of an alchemist, I have upgraded from 4.0.x to 5.0 alpha on my FreeBSD box.

This works just great with the lightweight applications I am running, but I had to tweak my SQL statements a bit. I imagine this is configurable in some way, but I don't know how or where.

Just a sample statement

Before:
UPDATE MyDatabase.MyTable
SET MyField = 1
WHERE MyField = 0;

Now I have had to fatten that up a great deal:
UPDATE MyDatabase.MyTable
SET MyDatabase.MyTable_MyField = 1
WHERE MyDatabase.MyTable_MyField = 0;

Obviously I need to make full references in all elements of the statement which is tiresome and definitely abundant. I imagine I am just overlooking something.

Good Reference Material
Ive been wading through the 1700+ page manual for mysql. Can somebody recommend any "condensed" references where I can get the basics without trying to wade through everything just to figure out how to find out the basics like what users exist and what permissions they have, are my data files full and need extending.

Viewing 5.0 Reference Manual
I downloaded the subject manual from http://dev.mysql.com/doc/ and unzipped it, but viewing it is a problem. It never seems to stop loading, the hour-glass never goes away, and it's almost impossible to select anything. This is with Firefox 1.5.0.11 on XP. Is this a known problem? The page/chapter version seems to work OK, by the way.
(I think I'll be using the online version in any case, or PDF, because there's no easy way to search the downloaded files. Unless someone can suggest a good way, that is.)

Storing A Reference To An Image's Location
If you insist on storing the actual image file in the database ( I recommend against it) then make sure you set the datatype to BLOB.

As to your earlier question it can't really be explained better with a sample script. All you need to do is insert strings (varchar datatype) into the database. The strings you store will be the file name and the path to the image ie. ( "image.jpg" and "/home/images/" ). You would store the actual image file in some directory. All you store in the database is the information necessary to find the image. Code:

Error In MySQL Online Reference?
I was browsing through the reference manual on the mysql.com web site and
found the following code examples for some date/time functions:

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'

In each example above the first line is the code the second is the result.
Do these look backwards to anyone else? I'll admit I'm a newbie that's
browsing the boards and trying to decide if I want to learn MySQL and PHP,
but to me the following makes more sense:

mysql> SELECT STR_TO_DATE('2004-04-31', '%m/%d/%Y');
-> '04/31/2004'

The online manual's example would indicate that the "format" portion of the
function (%m/%d/%Y) should be specifying the format of the INPUT string.
I'm thinking that the "format" portion of the function should be specifying
the RESULT format as in my example (based upon my experience with Oracle and
Brio).

So, do I have a lot to learn or are the first two examples incorrect?

Cannot Reference MySql Namespace In Vb.net 2002 Sp1
When I try to add a reference to MySql.Data.dll to my project in VB.Net 2002, I get a popup that says it is not a valid assembly or COM component. However, when I develop in VB.net 2005 the same .dll imports just fine. Is there a different .dll for VB.net 2002 or is MySql not compatible with the older version of .net?

How To Detect Circular Reference/relationship With SQL
What's the common SQL code used to detect circular reference/relationship.

E.g.

This simple category can add other other category as
its children or set other category instance as its
parent.if there is a select clause that can detect if descendants of a category instance
has the category instance's parent as its children.

CREATE TABLE category (
category_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
parent_category_id int(10) NOT NULL DEFAULT 0,
name VARCHAR(255) NOT NULL,
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
description VARCHAR(1000),
PRIMARY KEY(category_id),
CONSTRAINT UNIQUE(parent_category_id, name)
) TYPE=InnoDB;

Thanks,

Sam

Multi Field In Same Table Reference To Same Foreign Key
I am having trouble to come up with correct query, could someone give me a hand?

I have created a table which contains several fields (SampleUser_ID, TestingUser_ID, Report_User_ID) and all of these fields reference to same User Talbe's primary key - user-ID.

It works fine when I retrieve data only with Sample person name (as following)

SELECT Resulttable.ID, Resulttable.data, lk_operators.UserName as 'SampleName' FROM lk_operators RIGHT JOIN ResulttableON lk_operators.User_ID = Resulttable.SampleUser_Id

But I having problem to display all officer's name who work on the different task for the same sample. eg:
Result.ID, SamplePerson_name, Testing_person_name, Report_person_name, Result.data

I have try join on top of join without any luck. Can someone point me to the right direction?

Reference A Table Name In A Join (use A Table's Name In A Column)
I want to make a column while joining several tables that stores the name of the table each record came from. e.g.

lets say I have three tables dogs, cats, and mice. when I do a join, I would like to create a column on the fly labeled say, species, that simply references the table name from which the record is coming from.

Next And Prev DisplayOrder Values (was "Help With MySQL Query")
I have a table 'Album' containing columns 'PageID' and 'DisplayOrder' (both INT).
On each page that is displayed I want to have 'previous' and 'next' links to other pages, which are arranged via the DisplayOrder column.

At the moment I'm doing this in 2 separate queries:

SELECT DisplayOrder FROM Album WHERE PageID = $currentPage
$currentNo = [result of last query]
SELECT PageID FROM Album WHERE DisplayOrder IN ($currentNo-1, $currentNo+1)
Highly simplified but you get the idea. Would it be possible to combine this into one query?

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.

Reference A Table To Another Table On A Different Database
i have two apps (app1 and app2) which each use a different database (db1 and db2), all of which resides on the same server.

each app utilizes the same login credentials (i.e. one would log into app1 with the same info used to log in to app2).

so, what i would like to do, instead of having two separate userinfo tables on each database, is to have one userinfo table which would hold all usernames and passwords on db3 and then have a table on each of db1 and db2 referencing the db3 table.

does this make sense? and is it possible? i am just trying to avoid having multiple tables to update whenever any user info is changed. if you need me to explain any more let me know.

Updating Table With Reference To Another Table
I am trying to update two columns of a table (date and location) so that they are equal to the date and location of another table on a lastname join. Here's is the code I have right now:

update tblA
set tblA.date = tblB.date, tblA.location = tblB.location
where tblA.lastname = tblB.lastname;

I am getting a syntax error with the above code. Does anyone know how to do this?

Help Me About Error "MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: A Errors"
As I was uploading my file to the data base, thi meaasge shows "MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: A Errors",

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.