MYSQL :: IN Query Showing All Records If Used With Same Field Twice

DISTINCT Still Showing Duplicate Records
Why won't distinct work? The sql IS selecting correctly but its showling records with the same f.preplyid

CODE

SELECT DISTINCT (f.preplyid), f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.pid=f.preplyid) ORDER by f.ptime desc LIMIT 7

Only Showing Records That Do Not Exist In Another Table
How do I show a listing of all records within one table that do not exist within a second table?

For example:

Table A
----------
apple
orange
pineapple
banana

Table B
----------
orange
cherry
lime
banana

I want all records in A that do not exist within B to show. thus I should have the results of:

apple
pineapple

What would my sql statement be if my column name was "fruit"?

Select / Count Not Showing Records ... Permissions?
I imported 600,000 records from SQL Server 2005 to mySQL.

I know they are in there -- I just can't see them!

When I do a SELECT, it processes for a while, and then returns nothing.

Same with a count, it sits there processing, and then returns a '0'

However when I do the same on a table I know has no rows, it definitely
comes back right away.

I'm logged in as root. Do I need to assign myself some permissions to
see these records? And the count numbers?

Using A Query To Exclude Records From One Table, If The Field Value Exists In Another
I'm very new to SQL, and wondered if someone could help me out:

I am building a query which contains two tables. One table has details of everything everyone owns.

The other table is a list of things which people own, but that I don't want to see.

Is it possible to design the query so that I see all of what everyone owns, except the items listed in the other table?

Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows:

SELECT MIN(Freq_Min) FROM datatable;

and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....

Query Showing More Results In PHPMyAdmin Than In My Application
I am trying to run a query on my database, my database class returns 5 results, when I run it on PHPMyAdmin (direct copy), it returns 15 results. I am 100% sure I am copying the exact query. I tried my customer db class and ADODB.

I've tried many search queries, so it's not just this one.

MySQL: 4.0.27-standard
PHP: 4.47

Any ideas would be great appreciated, I am pulling my hair out......

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:

View Records In A Column/field
I used to use phpmyadmin and loved the fact i could just click on a field and hit 'browse' and it shows me all the records in it.

I just downloaded all the GUI tools for mysql 5.0 and I love them and don't think i need phpmyadmin anymore. I have problems though with one thing. How do you browse through all the records in your db fields? I am sure this is possible with one of the new GUI tools I downloaded from this site:

MySQL Migration Toolkit
MySQL Administrator
MySQL Query Browser
MySQL Workbench

I am probably just missing the obvious. If anyone knows how I can browse all of the records using one of these tools,

Move Records From 1 Table To Another And Including 1 Field
I've created a table called `temp` and I want to move all the rows in `temp` to `songs` based on:

`temp`.`title` -> `songs`.`title`
`temp`.`artist` -> `songs`.`artist`
`temp`.`catalog` -> `songs`.`catalog`
NOTHING -> `songs`.`userid`

There is no field in the temp table that has this value, i will need to insert it along with each record. How would I go about doing that, without looping through 1 query and inserting each as I go?

Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported
from Excel, which explains why it's a mess to begin with.

Within a table (or via any other means someone might be able to recommend) I
need to combine multiple records which share two like fields. (If that's not
clear, the real-world explanation is below.)

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | | | | |
| COMP1 | 123 Main St | | 1 | | | |
| COMP1 | 123 Main St | | | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | | | |
| COMP2 | 45 Oak Pl | | | 1 | | |
| COMP2 | 45 Oak Pl | | | | 1 | |
| COMP2 | 45 Oak Pl | | | | | 1 |
+----------+--------------+----+----+----+----+----+

Must be distilled to :

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | 1 | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 |
+----------+--------------+----+----+----+----+----+

So essentially I want to combine all of the 'Px' fields for each ID into one
record.

The real model : The data represents companies (identified by ID) and the
product lines they stock (P1 - P5). Right now, there's a separate rec for each
product line. I need to combine those seperate recs into one.
____________________

I suppose I could import the raw data into separate tables, each with one 'P'
field -- and then combine them into another, but I'm not sure about that
approach either.
____________________

I already tried to make Excel combine before exporting, but had no luck.

Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have
duplicates.

example;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 4 | 7 | 3 | 9 | 122.165.177.211 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |
| 6 | 24 | 3 | 17 | 122.165.177.211 |

As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

So that I would get these results;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |

Combining Multiple Records Into One Based On A Key Field
I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I
need to combine multiple records which share two like fields. (If that's not
clear, the real-world explanation is below.)

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | | | | |
| COMP1 | 123 Main St | | 1 | | | |
| COMP1 | 123 Main St | | | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | | | |
| COMP2 | 45 Oak Pl | | | 1 | | |
| COMP2 | 45 Oak Pl | | | | 1 | |
| COMP2 | 45 Oak Pl | | | | | 1 |
+----------+--------------+----+----+----+----+----+

Must be distilled to :

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | 1 | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 |
+----------+--------------+----+----+----+----+----+

So essentially I want to combine all of the 'Px' fields for each ID into one
record.

The real model : The data represents companies (identified by ID) and the
product lines they stock (P1 - P5). Right now, there's a separate rec for each
product line. I need to combine those seperate recs into one.
____________________

I suppose I could import the raw data into separate tables, each with one 'P'
field -- and then combine them into another, but I'm not sure about that
approach either.
____________________

I already tried to make Excel combine before exporting, but had no luck.

Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates.

example;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 4 | 7 | 3 | 9 | 122.165.177.211 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |
| 6 | 24 | 3 | 17 | 122.165.177.211 |

As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

So that I would get these results;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |

Deleting Records Upon Query... (!?!)
Sorry for the confusing thread title. Here's what I am trying to do...

Read the last six added records for a particular criteria, and delete the rest. Now consider I have around 1000 unique "criteria" and this query is executed a few times a day for each unique... should I delete it on each read or put it in a cron job and schedule it for every night.

Here's a sample MySQL syntax for my selectquery:

SELECT * FROM table1
WHERE field1 = sometext
ORDER BY some_time_field ASC
LIMIT 0,6
So the questions, again, are:Can I join this SELECT query with a DELETE, and delete all field1s with "sometext" which were not selected by the query?If I can't, how would I do it with a seperate DELETE query? i.e. Leave the last six records for field1 = sometext and delete the rest?Do I really need to order by some_time_field? Or does MySQL have an internal schema to sort records by their "internal" creation date?

Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f).

Now this works well, but what I want to do is say to my query is find all of these dates BUT

- As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....

Count Records Within Query
I have the following information in table 'Test1':

field1  field 2
000001  000000
000002  000001
000003  000000
000004  000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2  recno
000000    3
000001    1

can some one help with the trick to make this work?

Query Cannot Retrieves All The Records
I've got this problem, this query is used to create a combo wich display all months where at least a news is been submited (a kind of hostory menÃ¹), the fact is that it can display max the months wich correspond to the 107 record submited.

The query:

select date_format(data,"%b/%Y")as textMonth, month(data)as month,dayofmonth(data)as day,year(data)as year from archivio where category = "Rassegna Stampa" group by month

Just to be shure I've also tried the query above on mysql browser, and the result is the same. Can you tell me if there's a kind of limit?

Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages.

columns: textId, languageCode, textline

The application adds records for specific languages as:

textId 1
languageCode: EN
textline: "Username"

another record could be:
textId 1
languageCode: DE
textline "Anwendername"

Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as:

Select count(*) from texts where textline != '' and textId in (120,124,134);

But what when the records are there in ENglish and German but not in French?

Any ideas how to solve this?

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table. I not sure how to do that. Here is the query I have now....

Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name.
Eg
Total records for author Eddings = 10

So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like:

Select
sum(Eddings) $total
From books

Query Total Records In 1 DB With 5 Tables:
In PHPmyadmin there's an option that shows you the amount of records in all tables, how can I did this via a query?

MySQL Query For All Records With NULL Value
I have recently been trying to extract a few products from a database (that is quite big), so that I can find products that have a specific NULL Value, what kind of a SQL statement would allow me to do this?

Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:
1, 2, 3, 4, 5, 6, 7, 8, 9 . etc...

One of my utitities is meant to check the consistency of the table -
one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:

1, 2, 3, 4, 5, 6, 7, 8, 9 .etc...

One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

Query For All Records With Datetime In 2005?
I have a table with a datetime field. I'd like to query all records for 2005 based on that field. I'm having trouble coming up with a where clause that does this.

I'm on MySQL 4.1

Select Last N Records Matching A Query.
I am trying to select the last n records in a database which conform to criteria selected by a user. I gather that there is no "BOTTOM" equivalent to the "SELECT TOP" command. I've tried "SELECT... ORDER BY... LIMIT X,Y" but MySQL doesn't seem to take a second parameter for the LIMIT statement. Is there any way round this?

List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are.

Example:

1. Bill Jones - 55
2. Bob Smith - 56
3. Steve Gates - 25

Can I do this in mysql or should I add the code in PHP, and how do I do it?

Query To Display Records Over A Certain Number?
think my brain is now officially fried as i can not come up with this query....

i want to display records from my table where the record count is say over 5.

real simple...two columns a and b. 10 records in the table....

Counting Records In 2 Tables Using 1 Query
I have these 2 queries.

SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that

SELECT count(*) events
FROM events e
WHERE e.this and e.the other thing

is there a way to put these into one query.....

SELECT count(g.*) gifts, count(e.*)
FROM gift g, event e
WHERE . . . .

so far nothing seems to be working .....

Flag Records Not To Be Reused After Query
I'm a MySQL novice, and I don't even know if there is a way to do this.

I have a simple table containing just 2 fields and about 15,000 entries...one contains an ID (which is just an incremental number) and the other a phrase.

I will be doing queries selecting 50 random entries from the table. Somehow, once those 50 are selected and returned, I would like to flag them so that they cannot be selected again UNTIL I exhaust the entire list. So, I'd like to get 50 random at a time, until all 15,000 are gone, and then start over.

If that isn't possible, I suppose I can just select the first 50 every time and delete the first 50 when I am done.

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

Query To Get Matched And Mismatched Records
this is my first time on the forums and i need help with my dbase. i need to match fullnames from Table1 with the fullnames in a QryCustomer. Table1 contains only the fullname(one field) whereas QryCustomer contains a list of fullname with their respective Account# and Customer#..i need to match the fullnames and a separate query to get the mismatched names.

Update Query On 14000 Records
Haing moved a large site from asp and access to php and mysql, I would like to change the way of my fields is delimited.
Currently, the field contains user selection seperated by commas
e.g. selection1, selection2, selection3
I would like to update this field on every row in the table and change all commas to pipes
e.g. selection1|selection2|selection3
The table contains over 14000 records, and the field in question can contain anything up to 30 selections.
Is this possible to do?

Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?

How To Specific Number Of Records To Retreive In A Query?
My item file contains too many records to display. I want to limit the display to only 20 records at a time.

In the Select statement, how do I specify the the first 20 records, 2nd 20, etc.?

Selecting Records Younger Than 24 Hours Using SQL Query?
I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like:

SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY)

for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.

How To Show All Records In MYSQL Query Using Default Value
I'm working on somesite and i face this problem:

i want to send a parameter entered by a user from text box that called (txt1) to the query below ....

Query By Dates To Find History Records
what is the correct way to query on a date field in a table to pull a value that is older than 3 days?

Date field format is 2007-04-04

I only want records where the date is 2007-04-01 or earlier.

Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.

Maybe a better explanation is given by example. Think of having a table of employees, with one of the columns being their bosses email address. Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)

I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.

Showing Last 10
ok, i know this probably can be answered easily but in new to the mysql game. i run a movie website and recently created a database with phpmyadmin so i can shuffle and sort movie info easy.i want to show the last 10 entries to my database on one page.
i saw the last insert id code, the one in the sticky post i mean, tried it but cant figure out how to set it up for my needs, can anyone help me out with this or modify that code to fit my situation?

Query To Result Out The Newly Inserted Records To Txt File
Can any one help me with a query to result out the newly inserted records from a MYSQL table to a text file on a hourly basis.

Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries

however if I add a LIMIT 500,50

It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records.

Is there something limiting the limit? Is there an option in mysql that can increase this limit?

Showing Text Instead Of Img
just set up a new table with some imgs in it and when i go to display them, it just shows the img name and not the img.

my database field is blob BINARY, or should it be unsigned, unsigned zerofill?
whats the diffrence?

Showing The Last Five Items
I have a comment system where I want to show the last five comments.

So say I have 20 comments. I would like to display comments 15-20

I have tried "ORDER BY id DESC LIMIT 5" but it shows the last comment at the top of the page and comment 15 at the bottom. I would like comment 15 at the t

Showing Changes To Tables
I'm working on an application that someone else wrote (using PHP and MySQL).

There are LOTS of queries that make changes to different tables with joins and it's really hard to track down what's been done to the data after running the code.

I've been trying to check some of the changes with mysql_affected_rows (for updates) and I've tried that function every way I can think of and I can't get that function to work.

I resorted to using a counter and just counting the number of times that a while loop runs to sort of get an idea of how many records _might_ have been touched.

That has (obviously) turned out to be unreliable.

Does anyone know of a tool (or method) to show changes to a mysql DB after running a query? I looked at mysql diff, but it just looks at structure changes. I need to know how many records were touched when this code was run and I've been working on this for quite sometime and I'm not getting anywhere.

Showing First 10 Results
I feel I should be able to work this out, but I'm drawing a blank.
How would I retrieve, for example, the top 10 orders for each day for a period, where I order by orderTotal?I can't seem to find any way to limit the results for each day, rather than the results as a whole.

Results Not Showing
i am wanting to get the highest number in a field where a members id is 1
below is what i have been messing around with so far but can not seem to get the highest figure into the $num var,
i place the query in sql and it shows the row with the highest number is as follows..

itemid
58

58 being the highest number the results show correctly, but not in the query results in php below (line 2)

please can someone help me out and correct what i might be doing wrong with these stamtments