Hi
With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
return two records for the record_id 3 ? Is it possible ?
Cheers
Neil
If there are two, you will return two.
On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
Hi
With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
return two records for the record_id 3 ? Is it possible ?
Cheers
Neil
--
Bier met
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
Cheers
Neil
On Tue, Sep 21, 2010 at 4:40 PM, Johan De
I don't think that'll work, no. Why would you want to return duplicate data
? The whole point of an RDBMS is to *avoid* duplicate data :-)
On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a
table. I think in this case I need to change my design .
On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote:
I don't think that'll work, no. Why would you want to return duplicate data
? The
On 21/09/2010 16:44, Tompkins Neil wrote:
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
No, that isn't
Hi Neil, all!
Tompkins Neil wrote:
Hi
With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
return two records for the record_id 3 ? Is it possible ?
This is a case where you may safely use natural language and logic. The
command is
SELECT all fields FROM the
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Tuesday, September 21, 2010 11:48 AM
To: Johan De Meersman
Cc: [MySQL]
Subject: Re: SELECT WHERE IN help
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a
table. I think
We have a logging package that logs data into a table. Part of the data
that is logged are elements of a vector. A logging application
automatically takes out the elemets of the vector and automatically
creates column names based on it's name and the element. This data will
be mixed in with lots
11, 2007 5:00 AM
Subject: Select where the column names are not fully known
We have a logging package that logs data into a table. Part of the data
that is logged are elements of a vector. A logging application
automatically takes out the elemets of the vector and automatically
creates column
,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 11, 2007 5:01 AM
To: mysql@lists.mysql.com
Subject: Select where
Hi,
We are migrating to mysql 4.1 (from 4.0). We have lots of BIGINT
columns, and lots of application code that have queries like the
following:
select ... where bigint_col = '64-bit int value'
In 4.0, this worked fine. In 4.1 it does not work. The query only
works if you take the single
-Original Message-
From: Ephraim Dan [mailto:[EMAIL PROTECTED]
Sent: 27 February 2005 15:44
To: mysql@lists.mysql.com
Subject: select ... where bigint_col = 'big_value' doesn't work?
Hi,
We are migrating to mysql 4.1 (from 4.0). We have lots of BIGINT
columns, and lots
AM Thomas wrote:
Hi there,
I'll answer your questions below, but I should tell you that it looks
like even three or four joins seem to make this too slow.
Theoretically, the user could select up to 15 or so GoalNumber values,
12 for Grade, and about 20 possibilities for Subject - clearly it
Such bounty of comments! Thank you, Michael Stassen.
I see how my logic was faulty, and that a more correct solution would
indeed be faster. Thanks for pointing that out and not making me feel
like too much of an idiot :) I'll try the revised solution. I am
clueful about join making
I am guessing the long duration is caused by having to do complete table
scans. How big is your dataset?
What about creating another index in resource_goals that includes
GoalNumber and ResourceID? Perhaps even Subject, Grade, and
NumericGrade As I learned just a couple days ago, making
Jeremy Cole wrote:
Hi,
Thanks, but unless I'm missing something, that will return the same
as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource
records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title
FROM resources
LEFT
AM Thomas wrote:
Guarded exclamations of success!
This seems to be working for me so far - thank you! Here's an actual
example of a working query on my database; the field names are a little
different (sorry, I was trying to make my earlier example more
readable). The main change, though,
Hi there,
I'll answer your questions below, but I should tell you that it looks like
even three or four joins seem to make this too slow. Theoretically, the
user could select up to 15 or so GoalNumber values, 12 for Grade, and
about 20 possibilities for Subject - clearly it wouldn't be
In article [EMAIL PROTECTED],
AM Thomas [EMAIL PROTECTED] writes:
Now, if I understand how this is working:
SELECT r.TITLE
FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID)
WHERE g.SUBJECT = 'English'
AND (g.GRADE = 1 OR g.GRADE = 2)
GROUP BY r.ID
HAVING COUNT(*)
, February 13, 2005 7:23 AM
To: mysql@lists.mysql.com
Subject: select where multiple joined records match
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's
@lists.mysql.com
Subject: select where multiple joined records match
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I
Hi,
Thanks, but unless I'm missing something, that will return the same as
SUBJECT=English and (GRADE=1 or GRADE=2), which is resource
records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title
FROM resources
LEFT JOIN goals AS goal_a
Guarded exclamations of success!
This seems to be working for me so far - thank you! Here's an actual
example of a working query on my database; the field names are a little
different (sorry, I was trying to make my earlier example more readable).
The main change, though, is that I did
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I have two tables, resources and goals.
resources table:
ID TITLE
1
Have a look at the manual page for EXISTS, you appear to need something like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1 AND subject='English'
)
AND EXISTS (
SELECT resource_id FROM goals AS g
WHERE
AM Thomas wrote:
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I have two tables, resources and goals.
resources
Except that he/she is using 4.0, which doesn't support subqueries.
Michael
Peter Brawley wrote:
Have a look at the manual page for EXISTS, you appear to need something
like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1
Thank you kindly for your gracious help.
I am making much progress (the 'as r' and 'as g' syntax is helpful; I'd
forgotten about it since I'm doing most of this through Perl; thanks). I
played with the COUNT solution for a while and was initially encouraged,
nay, ecstatic.
However, I was
hello, I've a query that runs very slow:
select name, count(id)
where str_field = some string or (str_field string with nuber at end 1 and
str_field string with nuber at end 9)
group by name
i have about 4mil records and the query takes about 3 minutes
str_field, name are MUL indexes both
I have a quick SQL question that I hope someone can answer,
I have two tables
Events { e_id int(11), e_name varchar(200) };
Bookings { customer varchar(200), event_list varchar(200) };
Event_list is a string of comma separated values, eg 1,2,4 where the
number corresponds to Events.e_id
I would strongly recommend refactoring as the string transformation you
mention cannot be accomplished without external scripting assistance.
change your Bookings table to be (customer varchar(200), event_Id int(11))
This way, each booking becomes one row in the table. Five bookings = five
I second this. The way you are storing multiple data items in a single
column is very bad database design and fails the test for even the first
normal form. You are going to get very poor performance and usability
out of this table design. I know you said you inherited the data so its
probably
Dave,
It's best to keep threads on the list. On average, you'll get better and
faster responses when all the experts can see your question. Also, others
can benefit from the answers when they are on the list.
See my comments below.
Critters wrote:
Thanks for your response, here is some more
Hi
I have a database with just over 10,000 records. with the following structure:
id, compid, name, score, and about 10 other fields
I have indexed id, compid, score
about 10 records out of the 10,000 have a compid of 2
when i do
select * from table where compid = 2
it was slow until i
Critters wrote:
Hi
I have a database with just over 10,000 records. with the following structure:
id, compid, name, score, and about 10 other fields
I have indexed id, compid, score
about 10 records out of the 10,000 have a compid of 2
when i do
select * from table where compid = 2
it was slow
Hi,
i've a rather large table (~ 1.800.000 rows) with five CHAR columns - let's say col1,
col2, , col5. Col1has the primary key. The columns col2,col3,col4,col5 hold
strings of variable length. I need to find duplicate entries that have the same value
for col2,col3,col4 col5 but (and
I am having a problem building a SELECT statement that joins two tables
with a WHERE condition.
SELECT
trucks.*
FROM
trucks, history
WHERE
trucks.account_id = '100'
AND trucks.status = 'Active'
AND history.truck_id = trucks.id This is the
tricky bit
GROUP
You need to change your INNER JOIN to a LEFT JOIN
SELECT
trucks.id, sum(history.time_sec) as total_seconds
FROM
trucks
LEFT JOIN
history
ON trucks.id = history_truckid
GROUP BY
trucks.id
ORDER BY
total_seconds desc
One other issue ---IMHO, the
Thanks, the LEFT JOIN worked.
I do have a question though, why is it considered best practice to list
all non-aggregated columns ( I assume you mean columns from trucks.*)
in the GROUP BY statement? I ask because I am interested in fast,
secure, standards-compliant code, I'm just not always
Because not doing so violates the SQL standard. Allowing you to
included non aggregated columns in the SELECT list is a non standard
MySQL extension to the SQL language. You will get an error in other
products, such as oracle, where you will get a xxx is not a group by
expression error.
On Apr 13, 2004, at 2:01pm, Michael Stassen wrote:
You shouldn't be surprised. This is normal behavior.
interchangeStatus is a varchar, so
select fileName from outDocInterchange where interchangeStatus = 91;
requires that interchangeStatus be converted to an int for each row so
it can be
On Thu, 15 Apr 2004, Max Campos wrote:
On Apr 13, 2004, at 2:01pm, Michael Stassen wrote:
You shouldn't be surprised. This is normal behavior.
interchangeStatus is a varchar, so
select fileName from outDocInterchange where interchangeStatus = 91;
requires that interchangeStatus
Lucy, you've got some EXPLAINing to do... (sorry, couldn't resist)
A) select fileName from outDocInterchange where interchangeStatus = 91;
B) select fileName from outDocInterchange where interchangeStatus =
'91';
(A) Runs unindexed, (B) runs with the istat_date index. Can anyone
explain
You shouldn't be surprised. This is normal behavior. interchangeStatus is
a varchar, so
select fileName from outDocInterchange where interchangeStatus = 91;
requires that interchangeStatus be converted to an int for each row so it
can be compared to 91, rendering the index useless. On the
Donal
With MySQL, temp_explanations MUST exist before you can INSERT INTO it.
Based on the assumption that temp_explanations is non-existent, you will
have to CREATE TABLE temp_explanations first before running an INSERT
INTO.
Hope that helps.
Inyang
Hello,
I want to do the following query
Hello,
I want to do the following query
INSERT INTO temp_explanations(tip) SELECT tip FROM explanations WHERE
explanations.file=temp_explanations.file AND
explanations.level=temp_explanations.level;
but I'm getting an 'unknown table temp_explanations' error.
How can I get around this?
Having a bit of a sticky wicket here... I'm sure the answer is simple
but I'm just not seeing it.
Basically I want to return all the TargetRanges and ScannerIDs. You
would think it's simple, here's the snag. See how ScannerID has 0 in
some rows. Well, we used 0 to mean any scanner in our PHP
Hi,
I didn't test this.
Assuming that Swordfish.scanner.scanner_id 0 for all rows (and
that's why it doesn't appear in the results list from the first query):
SELECT InteractV2.Job_Queue.TargetRange AS TargetIP,
IFNULL(INET_NTOA(Swordfish.scanner.ipaddr),'ANY SCANNER') AS ScannerIP
FROM
I am trying to get my program to be searchable by
date, but it doesn't work. I get an error message in
perl, that follows the code I included. Any
suggestions are greatly appreciated.
PERL
--
@currenttime= localtime();
$y= $currenttime [5] + 1900;
$m= $currenttime [4];
$dom=
At 15:40 -0800 1/19/03, Melissa Stranzl wrote:
I am trying to get my program to be searchable by
date, but it doesn't work. I get an error message in
perl, that follows the code I included. Any
suggestions are greatly appreciated.
PERL
--
@currenttime= localtime();
$y= $currenttime
So...
Select n.uid, n.name from names_table n
LEFT JOIN exclude ON n.uid = exclude.n_uid
WHERE exclude.n_uid IS NULL;
OK... this works, but I thought I would go a bit
further...
I have added a field in the exclude-table, cat_id.
This shows which catalogue the names have been
excluded from.
I
So...
Select n.uid, n.name from names_table n
LEFT JOIN exclude ON n.uid = exclude.n_uid
WHERE exclude.n_uid IS NULL;
OK... this works, but I thought I would go a bit
further...
I have added a field in the exclude-table, cat_id.
This shows which catalogue the names have been
excluded from.
I
I have two tables. One with names and one for
excluding certain names. Exclude-table contains the
uid for the name excluded.
If I want to see which names has been excluded, this
query does the job:
Select n.uid, n.name from names_tables n, exclude
WHERE n.uid = exclude.n_uid
But if I want to
I have two tables. One with names and one for
excluding certain names. Exclude-table contains the
uid for the name excluded.
If I want to see which names has been excluded, this
query does the job:
Select n.uid, n.name from names_tables n, exclude
WHERE n.uid = exclude.n_uid
But if I want to
;
-Original Message-
From: Michelle de Beer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 11:22 AM
To: mysql list
Subject: Select Where !=
I have two tables. One with names and one for
excluding certain names. Exclude-table contains the
uid for the name excluded.
If I want to see which
Michelle de Beer wrote:
I have two tables. One with names and one for
excluding certain names. Exclude-table contains the
uid for the name excluded.
If I want to see which names has been excluded, this
query does the job:
Select n.uid, n.name from names_tables n, exclude
WHERE n.uid =
SELECT n.uid, n.name from names_tables n LEFT OUTER JOIN exclude
ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL
-Original Message-
From: Michelle de Beer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 3:22 PM
To: mysql list
Subject: Select Where !=
I have
Hi,
I found that mysql doesn't used indexes if there is a in the where
statement...
for example
mysql explain select * from testtable where id 5;
+-+--+---+--+-+--+---++
| table | type | possible_keys | key | key_len | ref | rows |
On Thursday, 21. February 2002 12:46, Natalino Picone wrote:
Hi,
I found that mysql doesn't used indexes if there is a in the where
statement...
for example
mysql explain select * from testtable where id 5;
It would only make sense to use an index, when you want to select the id
field
Natalino,
Thursday, February 21, 2002, 1:46:11 PM, you wrote:
NP Hi,
NP I found that mysql doesn't used indexes if there is a in the where
NP statement...
NP for example
NP mysql explain select * from testtable where id 5;
NP
hello,
I would like to ask if it is possible with mysql to do soomething like this:
SELECT * FROM [specific table name] WHERE [WHATEVER FIELD(not specified)]
LIKE '%sometext%'
Is there a variable lets say to refer to the column name?
What I actually want to do is to find all records that
Hi,
I don't think it is possible with out specifing the field names.
But we can get the soultion in the following way
If we have tabel calles 'test' with fields namely field1 and field2 then
you can get all the records that conatin 'c' with the following query
select * from
yes that gives the right results but i ask if i can do the same thing
without enumerating all fields like this:
SELECT * FROM [tablename] WHERE Field1 LIKE '%text%' OR Field2 LIKE '%text%'
OR ...
What i want is to do the same thing without knowing what are the field
names or how many fields
Hi - I've got an annoying problem here, I've checked books, web,
archives, etc, but can't find anything suitable so far.
I have 2 data tables (ignoring the other columns, not important) :
++
| user |
++
| userid |
++
++
| list |
++
| listid |
]]
Sent: Monday, September 10, 2001 5:39 PM
To: [EMAIL PROTECTED]
Subject:Select where A is not a member of B
Hi - I've got an annoying problem here, I've checked books, web,
archives, etc, but can't find anything
[EMAIL PROTECTED] writes: [snipped fscking ML bullshit]
database,sql,query,table
David Otton writes:
Users that belong to some lists, but not the one I'm interested in :
this is the one that I'm stuck on. Any thoughts? Suggestions?
SELECT DISTINCT userid FROM userlist WHERE
Hi.
On Mon, Sep 10, 2001 at 02:38:56PM -0700, [EMAIL PROTECTED] wrote:
[...]
I need to find, for each user, whether they are a member of list n :
++--+
| userid | memberoflist |
++--+
| 1 | true |
| 2 |false |
| 3 |
I am running MySQL 3.23.39.
I have a column called added of type timestamp(14)
in a table called users.
I set added to NULL on INSERTs in order to set it to
the current time.
I now want to SELECT all rows added in the last 24
hours.
SELECT * FROM users WHERE added ... ?
What is the correct
I figured out the answer. It is:
SELECT * FROM users WHERE added (CURRENT_TIMESTAMP()
- INTERVAL 24 hour);
--- Andrew Tomazos [EMAIL PROTECTED] wrote:
I am running MySQL 3.23.39.
I have a column called added of type
timestamp(14)
in a table called users.
I set added to NULL on
Weird things have just started happening in at least one table of my MySQL
3.22.32 database. The symptoms are like this:
mysql describe orders;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
On 31-Aug-2001 Jeremiah T. Folsom-Kovarik wrote:
Weird things have just started happening in at least one table of my MySQL
3.22.32 database. The symptoms are like this:
mysql describe orders;
+--+--+--+-+-+---+
| Field| Type
Thanks for your quick response! More below..
mysql select contact_info, expired from orders where order_num = '1';
+--+-+
| contact_info | expired |
+--+-+
| NULL | NULL|
+--+-+
^^
On 31-Aug-2001 Jeremiah T. Folsom-Kovarik wrote:
Thanks for your quick response! More below..
snip
Plus, all this just started happening yesterday, after everything worked
fine for many days. That leads me to believe there is a problem with a
corrupt table or something. How about
Is there a way to SELECT FROM table WHERE field_type IS (NOT) 'something'.
For example, 'something' could be TIMESTAMP, INT, FLOAT, AUTO_INCREMENT,
etc...I looked through the manual and archives to no avail.
Regards,
Brian Kaney
You have to select a certain row, there is no way to say all rows that are
NOT an int.
At 04:33 PM 1/16/2001 -0500, Brian Kaney wrote:
Is there a way to SELECT FROM table WHERE field_type IS (NOT) 'something'.
For example, 'something' could be TIMESTAMP, INT, FLOAT, AUTO_INCREMENT,
etc...I
76 matches
Mail list logo
