MYSQL :: Select Only Rows Of 2 Fields That Does Not Repeat

Union Repeat
which I want to be able to insert into a different database table:

The results are in these three variables which ar being looped:

myString / typenumber / codenumber

How can I insert these loop results into another database table? row by row?

Like this:

auto_id column1 column2 column3
1 value1(myString) value1(typenumber) value1(codenr
2 value2(mystring) value2(typenumber) value2(codenr)

etc....

If I use this: one row is being inserted. Someone said I have to use this but then use the UNION REPEAT.....

<CFQUERY NAME="update" DATASOURCE="">
INSERT INTO dev (dev_volgnr,dev_typenr, dev_nr)
SELECT '#myString#','#typenumber#','#codenumber#'

</CFQUERY>

How To Repeat A Result Set From A Query?
I need to repeat any times a result from an query, like this:

select name, price, code
from `acme`.`table`

results:

chave de fenda 1pol 10.50 048929
chave de fenda 2pol 10.90 048927

Instead of this, I need 7 times the first product and 5 times the second one, like that:

chave de fenda 1pol 10.50 048929
chave de fenda 1pol 10.50 048929
chave de fenda 1pol 10.50 048929
chave de fenda 1pol 10.50 048929
chave de fenda 1pol 10.50 048929
chave de fenda 1pol 10.50 048929
chave de fenda 1pol 10.50 048929
chave de fenda 2pol 10.90 048927
chave de fenda 2pol 10.90 048927
chave de fenda 2pol 10.90 048927
chave de fenda 2pol 10.90 048927
chave de fenda 2pol 10.90 048927

Any idea?

How To Avoid Repeat Typing
I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma. I use windows 98.

Avoid Repeat Typing
I am a newbie in MySQL. I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma.

Combine Fields From Many Rows
I have a table (see below) and I want to combine all the like named items with thier respective fruit and output that to a new table (1 name many fruit). I seem to be having problems getting the syntax down:

---------------------------------------------------------------------
ID | Name | Fruit
---------------------------------------------------------------------
1 | A | Mango
2 | B | Apple
3 | A | Pineapple
4 | B | Banana
5 | C | Pear
--------------------------------------------------------------------

I created another table called "myFruit" that have 2 field : Name, TheFruitILike

I want to update this table with the info from table "Fruit" . It should look like this:
-----------------------------------------------------------------
Name | TheFruitILike
------------------------------------------------------------------
A | Mango, Pinapple
B | Apple, Banana
C | Pear
----------------------------------------------------------------

What kind of command should I use?

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Swap Two Fields Bertween Two Rows
I have a table in which a field (ordine) is used for the orderer output of data.

This is not an index, since data are subdivided in different categories and therefore the value is not unique for the whole table.

Sometimes I need to change the display order and I was used to use a query like this one:

UPDATE globalmoneta as mon1,globalmoneta as mon2 SET mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";

probably not very efficient, but acceptable since this operation is not often performed.

Unfortunately, after a server upgrade this query is no more working. I suspect this is due to a different mysql version but I can not check it.

Any idea on how to obtain the same task with a different query?

Show Rows With No Matching Fields
here is my query which probably needs some more advanced join statements:

select * from orders o, customers c, orders_styles os, styles s where o.customers_id = c.customers_id and os.orders_id = o.orders_id and os.styles_id = s.styles_id group by o.orders_id

the problem is that sometimes, because of user input, there will be no matching fields in the tables orders_styles or styles (no rows with matching orders_id exists in table orders_styles to table orders). I still want to show these rows however. Right now, as you can see it only displays rows that have matching rows in orders_styles and styles.

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...

SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using

SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

Select WHERE Between Two Fields
I am trying to see if something like this is possible... I want to find all orders that have not been paid for.

SELECT SUM(payments.paid) AS paid, orders.amount AS ordered FROM orders AS orders LEFT JOIN payments AS payments ON orders.ID=payments.orderID WHERE ordered>paid.

Select Fields
Just want to make sure I understand this. If I need all the fields in a record I could use SELECT * ... but if I do that I can't use any of the MySQL functions on the data, right?I mean, I have some DATETIME fields I needs to format, so I assume the only way to do this is to use SELECT col-1, col-2...DATE_FORMAT col-n FROM table WHERE id=whatever. (I understand there is more to DATE_FORMAT.)

Is there any other way to use the MySQL functions (I am using MySQL with PHP.)

Select All Fields
I have two tables, "literature" and "publishers". The "literature" table has a field for "publisher_name", the "publishers" table has a field for "name".

Now, I want to produce an output of all "publisher_name" fields in the "literature" table that don't have a matching entry in the table "publishers". In other words, to give our editors info of missing entries.

Is there a "clean" way to accomplish this? That is, anything else except producing a humungously long "OR OR OR OR OR OR OR" query? That wouldn't be pretty at all...

Select Fields
just curious how something like this would work. I've got a last modified field which uses unix timestamps. do I just figure out what the integer would be, subtract it from NOW, then select fields with integers above that? or is there some shorthand?

Select Fields
i have:table:
id, data

sample data:
1, bob
1, daniel
2, john
2, david
3, samuel
3, nathaniel

i was wondering if i could get output like:
bob, daniel
john, david
samuel, nathaniel

i'd like to be able to ORDER BY either of those fields

Select Distinct From 2 Fields?
I have a table with 2 similar fields containing state names (e.g. primary_state and secondary_state). How can I find distinct states from both fields?

Using CASE To Select Different Fields
I have a table with 5 fields:

a
b
c
d
e

Based on the value in A, I want a SELECT statement to return 2 fields

If a = 0, return b, c
If a = 1, return c, d
If a = 2, return d, e

It works with a UNION...

SELECT a, b, c FROM abcde WHERE a=0
UNION
SELECT a, c, d FROM abcde WHERE a=1
UNION
SELECT a, d, e FROM abcde WHERE a=2
;

I thought of using a CASE statement, but I can't figure out how to make it work.

This case statement works...

select a, case a when 0 then b
when 1 then c
when 2 then d
end from abcde;

This one doesn't...

select a, case a when 0 then b, c
when 1 then c, d
when 2 then d, e
end from abcde;

Any ideas what I should be doing?

Here's what I used to create the table -
CREATE TABLE abcde (a int, b char(2), c char(2), d char(2), e char(2));

INSERT INTO abcde (a, b, c, d, e) VALUES
(0,'b1','c1','d1','e1'), (0,'b2','c2','d2','e2'), (0,'b3','c3','d3','e3'),
(1,'b4','c4','d4','e4'), (1,'b5','c5','d5','e5'), (1,'b6','c6','d6','e6'),
(2,'b7','c7','d7','e7'), (2,'b8','c8','d8','e8'), (2,'b9','c9','d9','e9');

Select Range Between Two Fields
Is there an SQL way of selecting all the values between and including two integer fields that are in a single given record? For example, if one is 1957 and the other is 1965, I need all years between and including those two. I know I can do it using PHP or other programming but I am trying to come up with an SQL way, if one exists. Code:

Select Fields Between 2 Dates
I am trying to develop a booking system in PHP with MySQL but I am having trouble writing some code that checks to see if a new booking clashes with another. e.g. in the database I have a booking that runs from 2008-03-10 to 2008-03-20. When I add a booking I want to check to see if a booking already exists between these dates. Is this possible?

I am not an expert with MySQL but the closest I got is to carry out 2 query's:

SELECT propertyid, arrival_date, departure_date FROM bookings WHERE arrival_date BETWEEN '$arrival_date' AND '$departure_date'

SELECT propertyid, arrival_date, departure_date FROM bookings WHERE departure_date BETWEEN '$arrival_date' AND '$departure_date'

but this obviously will not check if a booking is made in between these dates, e.g. if I try and add a booking from 2008-03-13 to 2008-03-18 it will not think that there is a collision of booking.

Select Two Fields With Distinct
Here is my query:
CODESELECT DISTINCT(LOCATION) AS LOC FROM tblNetDesc
LEFT JOIN tblNetClass ON tblNetClass.NetClassID=tblNetDesc.NetClassID
LEFT JOIN tblLocation ON tblLocation.LocationID=tblNetClass.LocationID;

Select Only Specific Data In The Fields How?
Hi MySQL gurus

I have a table called cclp_players
now that table has 3 fields for instance and has data see below,
-------------------------------
| player_id |team_id|division_id |
-------------------------------
| 001 | hp | golem |
| 002 | ibm | cestino |
| 003 | sun | mac os |
| 004 | del | bim os |
| 005 | acer | ms os |

Now i want to SELECT the table either by team_id or by division_id.
And let say i wanted to SELECT team_id by sun.

What is the right expression or combination of SELECT command for this?

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

Select Only Specific Data In The Fields How?
I have a table called cclp_players
now that table has 3 fields for instance and has data see below,
-------------------------------
| player_id |team_id|division_id |
-------------------------------
| 001 | hp | golem |
| 002 | ibm | cestino |
| 003 | sun | mac os |
| 004 | del | bim os |
| 005 | acer | ms os |

Now i want to SELECT the table either by team_id or by division_id.
And let say i wanted to SELECT team_id by sun.

What is the right expression or combination of SELECT command for this?

How Does One Add Or Subtract Two Fields In A Select Statement
I would like to subtract tow fields in table:

for example Table1 has fields A, B, C

I wrote the statement as follows

Select A, B, (A-B) as diff from Table1.

MySQL is complaining that there is a syntax error near '-'

Difference In SELECT WHERE IN With Datetime Fields
Anyone can explain the following?

Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

How Can I Join Two Fields In A SELECT Statement
How can I join two fileds in a SELECT statement, such as...

SELECT FirstName & ' ' & LastName AS Name FROM....

I know I was able to do that in Access, but how does it work in mySQL?

How To Select All The Fields In Aparticular Row That IS NULL
I want to select fields which has null values in a particular row.

if there is one field i can give as

Select a,b,c from tablename where a IS NULL;

But my problem is

I want to get results from a,b and c not only from a.

Difference In SELECT WHERE IN With Datetime Fields
Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

Append 2 Or More Fields In A Select Statement
How can I append 2 or more fields in a select statement.

ex:
CODESELECT *, `RESTPHONE1`+`RESTPHONE2`+`RESTPHONE3` AS NUMBER FROM RESTAURANTS

SELECT DISTINCT Then Additional Fields
I am trying to do the equivalent of :

Code:

SELECT DISTINCT email, * FROM student_bookings

I know this will never work but think it explains best what I am looking for - I want to select additional fields from each DISTINCT email.

I know I could use a subquery but are there any other ways of doing this? (unfortunatly I can't use subqueries as we don't have the correct version of mySQL for that ).

Select Fields Accros Multiple Dbs
I am trying to use the SELECT DISTINCT statement to create one list of email address I have which are in two different databases, I have tried the below syntax, but does not seem to work...can anyone please tell me where I am going wrong!

SELECT DISTINCT m2e_db.email, events_bookings.email FROM m2e_db, events_bookings ----Ment2Excel (M2E)----
--LEARN BY EXAMPLE--

How To Select Fields That Have Reserverd Names In MySQL
I have a stored procedure that does this select:

SELECT Blob, BytesCount, FileName
FROM ProblemReportAttachments
WHERE AttachmentID = a_AttachmentID;

But the column called 'Blob' causes a MySQL syntax error because Blob is a reserved word in the MySQL syntax (for the BLOB datatype). How to get around this? Please, no replies saying to rename the database table column to something else since this is not an option.

Select All Fields Of Specific Row In Multirow Result
I'm making a query that returns multiple rows, each with multiple fields. I need to be able to select individual rows (including all of their fields) without using a while loop. so, my query:

$fullquery = "SELECT claimid, title, claim, dateSubmitted, totalVotes FROM claims ORDER BY overallrating DESC LIMIT 6";
$result = mysql_query($fullquery, $dbc);

I need to select row 1, use its data in a specific way, then select row 2, etc. I know how to do this in a while loop, and how to remove particular fields, but not how to access all fields for just 1 row.

Query To Select All Fields Using AUTO_INCREMENT, And Then Delete All Data With A Value Of 0?
Got a database with lots of tables using AUTO_INCREMENT quite a bit. Around a week ago a restore was done on this database from a backup made with compatibility mode turned on which removed the AUTO_INCREMENT value from the SQL. Problem I have now is that for all data being submitted to tables with fields using the AUTO_INCREMENT function are being submitted with a value of 0.

I have now fixed my database structure so all the fields that need AUTO_INCREMENT are set to use it, but now I have the problem that most of these fields have data with a value of 0 which is invalid. What I need help with is to write a query that finds all the fields in all tables in the database using AUTO_INCREMENT, and then delete any values of 0. Does anyone know how I can do this, and if so could you show me?

Select Fields On Selection Box(html) By Database Values
I have a form that I have put together to update, and add new fields. I have no problem with connecting to the database, updating fields, and adding new fields. My form autofills with current database information if the user wants to update certain fields.

What is the easiest way to make these fields selected, if the database has the entries already? Thereâ€™s about 30 fields, so I would like to do it dynamically. The fields in the database(in one single variable) are comma delimited...

"value1,value2,value3"

Select Only The First X Rows
Is i posible to select only the first X rows? Or row X to X?

ID want help me, it will be diffrent from one day to another...

Now it might be ID 1 to 3 but later it is ID 6 to 3 on one set and 0 to 3 on another

Select Rows
I had 2 tables here, tbl1 and tbl2. Now we got 6 items in tbl1, and tbl2 store the color for each item (maximum 3 colors for each item).

tbl1
----
id(PK)   name
1        Jack
2        Amy
3        Bill
4        Ken
5        Rye
6        Icy

tbl2
-----
id(PK)  t1_id(FK)   color
1        1          green
2        1          red
3        1          blue
4        2          red
5        2          yellow
6        2          blue
7        3          purple
8        3          white
9        4          blue
10       5          black
11       6          red

What I need is a query to select item from tbl1 WHERE none of its color is 'green' in tbl2. Suppose item 'Jack' is not qualified but the query below will include 'Jack' in the result.

SELECT tbl1.id, name, color FROM `tbl1` left join tbl2 on tbl1.id = tbl2.t1_id where color != 'green'

I got no idea how to write a query to exclude 'Jack' since one of its color is green.

SELECT With 1,000,000 ROWS
I have aproximately 1,000,000 rows and I would like to do some query. The
first one is to get the number of row so I do :

mysql> SELECT COUNT(*) FROM `Log`;
+----------+
| COUNT(*) |
+----------+
| 969129 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
+--------+
| Nb |
+--------+
| 969129 |
+--------+
1 row in set (1 min 20.99 sec)

But like you can see it, it take a long with the WHERE clause. I use Pentium
III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the
computer but I'm not shure (I need more RAM ?).

SELECT With 1,000,000 ROWS
I have aproximately 1,000,000 rows.The
first one is to get the number of row so I do :

mysql> SELECT COUNT(*) FROM `Log`;
+----------+
| COUNT(*) |
+----------+
| 969129 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
+--------+
| Nb |
+--------+
| 969129 |
+--------+
1 row in set (1 min 20.99 sec)

But like you can see it, it take a long with the WHERE clause. I use Pentium
III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the
computer but I'm not shure (I need more RAM ?).

Select Rows With Max Value
Given a table like this:

CREATE TABLE IF NOT EXISTS `people` (
`name` varchar(10) NOT NULL,
`age` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `people`
--

INSERT INTO `people` (`name`, `age`) VALUES
('bob', 13),
('john', 25),
('mike', 25),
('steve', 8),
('sue', 13);

How would I select only the rows with the highest 'age' value?
In other words, because "john" and "mike" are the oldest, I want only their rows.

Select Rows
I am trying to create a table with certain select rows from another table. One column in the original table is called ID. I want only those rows with certain ID values (say 56 , 78, 89, 90) etc to be in the new table.

Select Rows
i want to select records between 11 dec 2004 to 25 dec 2004 plz tell me qry.

Select All Rows
I'm trying to figure out how to select all rows created during the current month. So, for instance, now I would want it to return ALL rows created during May. Next month, it should show every row created in June 2005. In my database, I have the field "date," where a date is stored in mm/dd/yy syntax, and I also have a "timestamp" field, which is created using PHP's time() function.

Select All Rows
I need to look in table 1 and pick all those records not in table in 2.
I know that table 2 has 51000 records and table 1 has 65000. I want to see the 14000 in table 1 that doesn't have a match in table 2

Num Of Rows In SELECT
I am just wondering if it is possible in one query to select some rows using WHERE and LIMIT clausule, and get the number of rows I would get when i dont use LIMIT.

if I have
SELECT * FROM table WHERE column1='something' LIMIT 10, 15;
i know it is 15 rows selected. But i need to know how many rows are in:
SELECT * FROM table WHERE column1='something';

I know, there is a way.. just to do both SELECTs.. and retrieve data from the first one, and num of rows from the another one..
But I just dont want to make two SELECTs if there is maybe some solution how can i do it in one query.it for page with forum where i want to show users how many threads are in database, but at one page i need to show just 15 threads...