Re: query problem with null

[Johan De Meersman]

- Original Message -
> From: "David Lerer" 
> 
> Have you tried to set city = null   (i.e. without the quotes)?

Spot on, I'd think.

NULL values are not a string with "NULL" in it - that's only what it looks like 
in query results :-) An empty string ('') is to strings what 0 (zero) is for 
integers: it says "the value of this field is nothing". NULL, on the other 
hand, means "the value of this field is a total unknown", which is useful, for 
example, in a field 'quantity': zero is still a valid, meaningful quantity; 
whereas you would use NULL to indicate that you simply do not know the quantity.

It's a bit of a peculiar concept, but as David indicated, IS NULL will not 
match fields set to the string "NULL" - as that is a string, not an unknown.

Another funny attribute of NULL is that NULL != NULL. There simply *is* nothing 
to compare, so you cannot ever say it's equal.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: query problem with null

[Richard Reina]

Ahhh... Thank you, that was exactly what the problem was. I will fix the
code that is setting the value of these new records to 'NULL'.

Thank you.

2012/3/9 David Lerer 

> Have you tried to set city = null   (i.e. without the quotes)? David.
>
>
>
> -Original Message-
> From: Richard Reina [mailto:gatorre...@gmail.com]
> Sent: Friday, March 09, 2012 4:24 PM
> To: mysql@lists.mysql.com
> Subject: query problem with null
>
>  When I do the following query:
>
> SELECT * FROM geo_trivia WHERE city IS NULL;
>
> certain columns that DO have 'NULL' value for city and not a '' (blank)
> value do not show up.
> I have even gone to the extent of reseting these records value as ='NULL'
> with UPDATE and they are still are not selected when I run the above
> query.  Can anyone help?
>
> The information contained in this e-mail and any attached
> documents may be privileged, confidential and protected from
> disclosure.  If you are not the intended recipient you may not
> read, copy, distribute or use this information.  If you have
> received this communication in error, please notify the sender
> immediately by replying to this message and then delete it
> from your system.
>

RE: query problem with null

[David Lerer]

Have you tried to set city = null   (i.e. without the quotes)? David.



-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com] 
Sent: Friday, March 09, 2012 4:24 PM
To: mysql@lists.mysql.com
Subject: query problem with null

 When I do the following query:

SELECT * FROM geo_trivia WHERE city IS NULL;

certain columns that DO have 'NULL' value for city and not a '' (blank)
value do not show up.
I have even gone to the extent of reseting these records value as ='NULL'
with UPDATE and they are still are not selected when I run the above
query.  Can anyone help?

The information contained in this e-mail and any attached
documents may be privileged, confidential and protected from
disclosure.  If you are not the intended recipient you may not
read, copy, distribute or use this information.  If you have
received this communication in error, please notify the sender
immediately by replying to this message and then delete it
from your system.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query problem

[Daniel Brown]

On Wed, Apr 16, 2008 at 4:35 AM, sivasakthi <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>  Iam  having the one table name called AccessDetails and data inside that
>  tables is following,
>
[snip=schema]
>
>  In that , I need to calculate the number of total sites , number of
>  total Accessed Sites,number of total Denied Sites and  number of total
>  Overriden Sites based on the particular Virus_Category,UserName,Date
>
>
>  How can form the query to achieve that??
>
>  I have used the following query but the total site is not correctly
>  displayed..

You may want to look into the ROLLUP modifier.  Here's the manual entry:
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

-- 

Ask me about:
Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo.,
and shared hosting starting @ $2.50/mo.
Unmanaged, managed, and fully-managed!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Martijn Tonies]

> how do I return a single row per property even if it has 3 or 4 images
> attached to it.

Please reply to the list instead of directly to me.

You could do a:

select p.from properties p where exists (select i.* from images i
where i.property_id = p.property_id)



> > >I have a table of properties that is linked to a table  f images with a
> > >one
> > property to many images relationship. I >have manged this with nested
> > queries but want to try and do it on one line. My current query
> >>
> >>$query = "SELECT * FROM images, properties WHERE images.property_id =
> > properties.property_id";
> >>
> >>As you can see from the query this returns a row for every image so if a
> > property has 3 images associated with it >it will be returned 3 times.
> >
> > What exactly is your question?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[ross]

No I want all the properties only one regardless of how many images are 
attached to them. Think I need a distinct in there somewhere,
- Original Message - 
From: "Jon Ribbens" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, May 09, 2007 6:56 PM
Subject: Re: Query problem



On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote:
>I have a table of properties that is linked to a table  f images with a 
>one

property to many images relationship. I >have manged this with nested
queries but want to try and do it on one line. My current query
>
>$query = "SELECT * FROM images, properties WHERE images.property_id =
properties.property_id";
>
>As you can see from the query this returns a row for every image so if a
property has 3 images associated with it >it will be returned 3 times.

What exactly is your question?


I think he somehow wants to return each property once only but still
have every image returned in the result.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Jon Ribbens]

On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote:
> >I have a table of properties that is linked to a table  f images with a one
> property to many images relationship. I >have manged this with nested
> queries but want to try and do it on one line. My current query
> >
> >$query = "SELECT * FROM images, properties WHERE images.property_id =
> properties.property_id";
> >
> >As you can see from the query this returns a row for every image so if a
> property has 3 images associated with it >it will be returned 3 times.
> 
> What exactly is your question?

I think he somehow wants to return each property once only but still
have every image returned in the result.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Martijn Tonies]

>I have a table of properties that is linked to a table  f images with a one
property to many images relationship. I >have manged this with nested
queries but want to try and do it on one line. My current query
>
>$query = "SELECT * FROM images, properties WHERE images.property_id =
properties.property_id";
>
>As you can see from the query this returns a row for every image so if a
property has 3 images associated with it >it will be returned 3 times.

What exactly is your question?


Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[obed]

On 8/3/06, André Hänsel <[EMAIL PROTECTED]> wrote:

Hi Dan, hi Obed,

of course I have no specific username, I want the last 5 downloads of each
distinct username in the table. :)



i was thinking a lot... and i can't find the solution but maybe yo
can do somthing like this

select user,download from table where user in (select distinct user
from tabla) order by time desc;

and in your front-end just display 5 for each user   xD

i you find the solution please let us know !

good luck


--

http://www.obed.org.mx ---> blog

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Miles Thompson]

At 03:08 PM 8/3/2006, André Hänsel wrote:


Hi,

I have a table logging downloads (time, username, download).

Now I'd like to have the last 5 downloads per user.

Can someone tell me a solution (or what to search for)?

Regards,
André


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


Correlated subquery, which can be the devil to debug, and don't run really 
quickly.

I have no idea if this will work:

SELECT t1.time, t1.username, t1.download FROM downloads AS t1
WHERE t1.username = ANY
(SELECT t2.username FROM downloads AS t2 WHERE t2.username = 
t1.username)

ORDER BY t1.time DESC
LIMIT 5

Hmmm, that's just going to return 5 records; you need 5 or fewer for each 
username.


That's almost like creating a view of users, then stepping through the 
view, selecting * limit 5 where username = view.username. See where that's 
headed? You may need a temporary table.


Sorry I've not been more help.

Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Dan Buettner]

For a specific username:

SELECT username, time, download
FROM table
WHERE username = 'someusername'
ORDER BY time DESC
LIMIT 5

Dan

On 8/3/06, André Hänsel <[EMAIL PROTECTED]> wrote:

Hi,

I have a table logging downloads (time, username, download).

Now I'd like to have the last 5 downloads per user.

Can someone tell me a solution (or what to search for)?

Regards,
André


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[obed]

On 8/3/06, André Hänsel <[EMAIL PROTECTED]> wrote:

Hi,

I have a table logging downloads (time, username, download).

Now I'd like to have the last 5 downloads per user.

Can someone tell me a solution (or what to search for)?




SELECT download FROM table WHERE username='user' ORDER BY time DESC LIMIT 5;

--

http://www.obed.org.mx ---> blog

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[John Meyer]

Rhino wrote:


- Original Message - From: "John Meyer" <[EMAIL PROTECTED]>
To: "List: MySQL" 
Sent: Tuesday, May 30, 2006 5:09 PM
Subject: Query problem



Setup

TITLES:
TITLE_ID


AUTHORS:
AUTHOR_ID


TITLE_AUTHOR:
(TITLE_ID,AUTHOR_ID)


Problem:
Given a title, I need to find all the authors who aren't connected 
with that particular book.




That's a pretty odd requirement, I must say. If your database has 
thousands or millions of books, you'd have to assume that virtually ALL 
of the authors in the database are NOT connected with a particular book.


Actually, this is more along the lines of a PHP form where I am adding 
an author to a title when the title is in the database.  I want to 
create a select list where the current authors in the database are 
shown, but not the ones already associated with that title.
Oh yeah, and one other thing, title and book are not synonymous in this 
one.  I'll give you the breakdown:


TITLES:
TITLE_ID

AUTHORS:
AUTHOR_ID

TITLE_AUTHOR:
(TITLE_ID,AUTHOR_ID)

BOOKS:
BOOK_ID

TITLE_BOOK:
(BOOK_ID,TITLE_ID)

EDITORS:
(BOOK_ID,AUTHOR_ID)

Complex enough for you? ;-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Rhino]

- Original Message - 
From: "John Meyer" <[EMAIL PROTECTED]>

To: "List: MySQL" 
Sent: Tuesday, May 30, 2006 5:09 PM
Subject: Query problem



Setup

TITLES:
TITLE_ID


AUTHORS:
AUTHOR_ID


TITLE_AUTHOR:
(TITLE_ID,AUTHOR_ID)


Problem:
Given a title, I need to find all the authors who aren't connected with 
that particular book.




That's a pretty odd requirement, I must say. If your database has thousands 
or millions of books, you'd have to assume that virtually ALL of the authors 
in the database are NOT connected with a particular book.


I'm trying to think of a situation where that list of people who were not 
connected with the book was actually useful Okay, maybe if you were 
looking for authors who could review the book, the query you want could be 
useful for identifying potential reviewers. Even if that was thousands of 
authors, it's still a smaller list than the list of all human beings on 
Earth :-)


In any case, the query is pretty simple assuming you are using a version of 
MySQL which supports  subqueries:


select AUTHOR_ID
from AUTHORS
where AUTHOR_ID not in
   (select AUTHOR_ID
   from TITLE_AUTHOR
   where TITLE_ID = 123)

You simply plug in the title_id of the book in question in place of 123 and 
you're all set.


If I were creating the database, I would have an id _and_ an author name in 
the AUTHORS table and I'd have an id _and_ a title in the TITLES table. Then 
I'd modify the query above to do joins so that the result showed me the 
author names and searched so that I was looking for the book title, not the 
book id. But I'm guessing that you already have that in mind and just 
simplified the question to get the bare essence of it. Or maybe you only 
have a few dozen books and will quickly memorize the author names and titles 
that go with each author id and title id.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 29/05/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem: UNION in subquery

[Luke]

A big "Thank you" goes to you! That was it!

Looks like I tried with too many parentheses i.e.

/this is wrong/

SELECT   FROM ...

(
(SELECT   FROM ...)

UNION

(SELECT   FROM ...)

) AS abc


Regards,
Luke


- Original Message - 
From: "Neeraj" <[EMAIL PROTECTED]>

To: "'Luke'" <[EMAIL PROTECTED]>; 
Sent: Wednesday, May 24, 2006 2:16 AM
Subject: RE: Query problem: UNION in subquery




Hi Luke..


Try this

SELECT ObjectId FROM


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')


UNION


SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID  AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc


Cheers :)


Neeraj Black Bits

-Original Message-
From: Luke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery

Hello!

I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.

The question is about the right syntax.

1.
This works fine /UNION/


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))


UNION


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID  AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))


2.
This works fine too /subquery/:


SELECT ObjectId FROM


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;


3.
But when I run 1&2 combined I get in troubles. This is a query draft,
can't come up with the right syntax:


SELECT ObjectId FROM


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))


UNION


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID  AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))


I tried many combinations and got various syntax errors. Any ideas?


Thanks,
Luke



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem: UNION in subquery

[Neeraj]

Hi Luke..


Try this

SELECT ObjectId FROM 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6') 


UNION 


SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc


Cheers :)


Neeraj Black Bits

-Original Message-
From: Luke [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery

Hello! 

I have a problem using UNIONs inside subqueries. I have simplified my 
query to make it more readable/understandable. 

The question is about the right syntax. 

1. 
This works fine /UNION/ 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) 


UNION 


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6')) 


2. 
This works fine too /subquery/: 


SELECT ObjectId FROM 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS 
SubTable1; 


3. 
But when I run 1&2 combined I get in troubles. This is a query draft, 
can't come up with the right syntax: 


SELECT ObjectId FROM 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) 


UNION 


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6')) 


I tried many combinations and got various syntax errors. Any ideas? 


Thanks, 
Luke 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[Don Read]

On Wed, 8 Mar 2006 10:12:22 - <[EMAIL PROTECTED]> wrote:



> but I have two other filters which may or may not be chosen. (area, and 
> interest).
> 
>  $query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND 
> area='area' AND interest='interest' ORDER BY fname $type";
> 
> but what if nether is chosen, or only one? Is there an AND/OR operator or 
> similar in mysql?

Your app needs to build the query.

In my libsql.php file I have:

function andclause($qry, $fld, $val, $op='=') {
$fmt = " %s %s $op '%s'";

$qry .= sprintf($fmt,
  ( preg_match('!\bWHERE\b!mi', $qry) ? 'AND' : 'WHERE'), $fld, $val);
return $qry;
}

With this, you can construct your initial query:
$qry = "SELECT * FROM foo WHERE blah LIKE '$baz%'";

// then test, case by case, to see if you need more selection clauses:

if (! empty($area))
$qry = andclause($qry, 'area', $area);
if (! empty($interest))
$qry = andclause($qry, 'interest', $interest);

echo '', $qry, '';
$res = SQLQuery($qry);
 ...

 
Have fun.
-- 
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[Peter Brawley]

[EMAIL PROTECTED] wrote:
I am fairly new to sql and am now getting into the area of slightly 
more complex queries.


At present my query is

$query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' 
ORDER BY fname $type";


but I have two other filters which may or may not be chosen. (area, 
and interest).


$query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' 
AND area='area' AND interest='interest' ORDER BY fname $type";


but what if nether is chosen, or only one? Is there an AND/OR operator 
or similar in mysql?
No AND/OR. It's the job of the front-end, ie your app, to assemble to 
correct number of Where clauses.


PB






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[Daniel da Veiga]

On 3/8/06, Adrian Bruce <[EMAIL PROTECTED]> wrote:
> one solution (may not be the best but would work) would be to use 'like'
> instead of '=' and then put wildcards %%$var % around the variable so
> that if it is not there then it wount effect the query.
>

Yeah, I use this kind of "trick" for SELECTs based on user submited
forms. So your query would be:

SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area
LIKE '%$area%' AND interest LIKE '%$interest%' ORDER BY fname $type;

> Ade
>
> [EMAIL PROTECTED] wrote:
>
> >I am fairly new to sql and am now getting into the area of slightly more
> >complex queries.
> >
> >At present my query is
> >
> > $query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%'
> >ORDER BY fname $type";
> >
> >but I have two other filters which may or may not be chosen. (area, and
> >interest).
> >
> > $query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND
> >area='area' AND interest='interest' ORDER BY fname $type";
> >
> >but what if nether is chosen, or only one? Is there an AND/OR operator or
> >similar in mysql?
> >
> >
> >Thanks,
> >
> >Ross
> >
> >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[Adrian Bruce]

one solution (may not be the best but would work) would be to use 'like' 
instead of '=' and then put wildcards %%$var % around the variable so 
that if it is not there then it wount effect the query.


Ade

[EMAIL PROTECTED] wrote:

I am fairly new to sql and am now getting into the area of slightly more 
complex queries.


At present my query is

$query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' 
ORDER BY fname $type";


but I have two other filters which may or may not be chosen. (area, and 
interest).


$query = "SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND 
area='area' AND interest='interest' ORDER BY fname $type";


but what if nether is chosen, or only one? Is there an AND/OR operator or 
similar in mysql?



Thanks,

Ross 



 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[Conor McTernan]

Sheeri,

Thanks for the help. I tried your sample queries, but they dont really
return what I'm looking for. I think I've found a solution though.

Given the contents of a case, I'm looking for a unique case id, basicially I
want to search for a case if it exists once I've decided the configuration I
want.

What I'm doing now is I alias the table I'm searching on for each Size, Qty
combination, and create a self join on this and the Case_Type_idCaseType id.


Here's an example of what I'm doing, I know CaseType_idCaseType '1' exists,
and that it's make up is:

+-+---+--+
| CaseType_idCaseType | Sizes_idsizes | qty  |
+-+---+--+
|   1 |10 |1 |
|   1 |11 |1 |
|   1 |12 |1 |
|   1 |13 |1 |
|   1 |14 |1 |
|   1 |15 |1 |
+-+---+--+

Taking these Sizes_idSizes and qty combinations I want to see if I can find
the CaseType_idCaseType by itself.

SELECT c10.CaseType_idCaseType AS case_id
FROM CaseType_has_Sizes AS c10,
CaseType_has_Sizes AS c11,
CaseType_has_Sizes AS c12,
CaseType_has_Sizes AS c13,
CaseType_has_Sizes AS c14,
CaseType_has_Sizes AS c15
WHERE c10.Sizes_idsizes = 10 AND c10.qty = 1
AND c11.Sizes_idsizes = 11 AND c11.qty = 1
AND c12.Sizes_idsizes = 12 AND c12.qty = 1
AND c13.Sizes_idsizes = 13 AND c13.qty = 1
AND c14.Sizes_idsizes = 14 AND c14.qty = 1
AND c15.Sizes_idsizes = 15 AND c15.qty = 1
AND c10.CaseType_idCaseType = c11.CaseType_idCaseType
AND c11.CaseType_idCaseType = c12.CaseType_idCaseType
AND c12.CaseType_idCaseType = c13.CaseType_idCaseType
AND c13.CaseType_idCaseType = c14.CaseType_idCaseType
AND c14.CaseType_idCaseType = c15.CaseType_idCaseType;

It's a hell of a query, and I'm sure there's a better way to do it, but it
will give me what I'm looking for.

+-+
| case_id |
+-+
|   1 |
|  19 |
|  37 |
|  42 |
|  44 |
|  76 |
| 110 |
| 157 |
| 552 |
+-+

It's returned 9 cases on this query, but from testing it out, the first
case_id returned is the one I'm looking for, all the other cases are ones
that are larger than the one i searched on that include the size, qty
combinations I specified in the query.

If I add a 'LIMIT 1' at the end it will return only the case_id I'm looking
for.

As I said, I'm sure there's a better way to do this, and if anyone has any
suggestions I'd only be happy to listen.


Conor


On 2/10/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
>
> You originally mention your UNION "doesn't work" but you did not
> specify the query.  This is a simple or query, or union.  You can do
> either:
>
> select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes
> where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4);
>
> or
>
> select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes
> where qty=1 and Sizes_idsizes=2 UNION  select
> CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where
> qty=1 and Sizes_idsizes=4;
>
> Please let me know if it's more complex than that, but when I ran it
> on my test data I got
>
> +-+---+--+
> | CaseType_idCaseType | Sizes_idsizes | qty  |
> +-+---+--+
> |  60 | 2 |1 |
> |  60 | 4 |1 |
> |  61 | 2 |1 |
> |  61 | 4 |1 |
> +-+---+--+
>
> for both results.
>
> -Sheeri
> On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote:
> > Sheeri,
> >
> > The table I'm searching on has a composite primary key since it's
> mapping an
> > N:M relationship between Cases and Sizes.
> >
> > Here's the create statement for the table I'm searching on:
> >
> > DROP TABLE IF EXISTS `CaseType_has_Sizes`;
> > CREATE TABLE `CaseType_has_Sizes` (
> >   `CaseType_idCaseType` int(10) unsigned NOT NULL,
> >   `Sizes_idsizes` int(10) unsigned NOT NULL,
> >   `qty` int(10) unsigned default NULL,
> >   PRIMARY KEY  (`CaseType_idCaseType`,`Sizes_idsizes`),
> >   KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`),
> >   KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`),
> >   CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY
> (`CaseType_idCaseType`)
> > REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO
> > ACTION,
> >   CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`)
> > REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION
> > ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> >
> >
> > Here's the Cases table:
> >
> >
> > DROP TABLE IF EXISTS `CaseType`;
> > CREATE TABLE `CaseType` (
> >   `idCaseType` int(10) unsigned NOT NULL auto_increment,
> >   `caseName` char(32) default NULL,
> >   PRIMARY KEY

Re: query problem

[sheeri kritzer]

You originally mention your UNION "doesn't work" but you did not
specify the query.  This is a simple or query, or union.  You can do
either:

select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes
where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4);

or

select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes
where qty=1 and Sizes_idsizes=2 UNION  select
CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where
qty=1 and Sizes_idsizes=4;

Please let me know if it's more complex than that, but when I ran it
on my test data I got

+-+---+--+
| CaseType_idCaseType | Sizes_idsizes | qty  |
+-+---+--+
|  60 | 2 |1 |
|  60 | 4 |1 |
|  61 | 2 |1 |
|  61 | 4 |1 |
+-+---+--+

for both results.

-Sheeri
On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote:
> Sheeri,
>
> The table I'm searching on has a composite primary key since it's mapping an
> N:M relationship between Cases and Sizes.
>
> Here's the create statement for the table I'm searching on:
>
> DROP TABLE IF EXISTS `CaseType_has_Sizes`;
> CREATE TABLE `CaseType_has_Sizes` (
>   `CaseType_idCaseType` int(10) unsigned NOT NULL,
>   `Sizes_idsizes` int(10) unsigned NOT NULL,
>   `qty` int(10) unsigned default NULL,
>   PRIMARY KEY  (`CaseType_idCaseType`,`Sizes_idsizes`),
>   KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`),
>   KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`),
>   CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`)
> REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO
> ACTION,
>   CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`)
> REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
> Here's the Cases table:
>
>
> DROP TABLE IF EXISTS `CaseType`;
> CREATE TABLE `CaseType` (
>   `idCaseType` int(10) unsigned NOT NULL auto_increment,
>   `caseName` char(32) default NULL,
>   PRIMARY KEY  (`idCaseType`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> and here's the Sizes case:
>
> DROP TABLE IF EXISTS `Sizes`;
> CREATE TABLE `Sizes` (
>   `idsizes` int(10) unsigned NOT NULL auto_increment,
>   `size` char(4) default NULL,
>   `jpSize` char(4) default NULL,
>   PRIMARY KEY  (`idsizes`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
>
> On 2/9/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
> >
> > Hi Conor,
> >
> > The table you showed us has 2 primary keys, which is not possible.
> > Can you do a SHOW CREATE TABLE on *each* table?
> >
> > -Sheeri
> >
> > On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote:
> > > Hello, I'm having a hell of a time figuring this query out, maybe
> > someone
> > > can point me in the right direction.
> > >
> > > I have a table which lists the case configurations for cases of items.
> > Each
> > > case will have a different combination of sizes e.g. Case 002 has 2 size
> > 5,
> > > 2 size 7 and 2 size 8 items.
> > >
> > > What I'm trying to do is, given a group of sizes and quantities how can
> > I
> > > find the corresponding ID number for a case.
> > >
> > > Here's the description of the table.
> > >
> > +-+--+--+-+-+---+
> > > | Field   | Type | Null | Key | Default | Extra
> > |
> > >
> > +-+--+--+-+-+---+
> > > | CaseType_idCaseType | int(10) unsigned | NO   | PRI | |
> > |
> > > | Sizes_idsizes   | int(10) unsigned | NO   | PRI | |
> > |
> > > | qty | int(10) unsigned | YES  | | NULL|
> > |
> > >
> > +-+--+--+-+-+---+
> > >
> > >
> > > Here's what I see if I do a select on a CaseType id.
> > >
> > >  select * from CaseType_has_Sizes where CaseType_idCaseType = 61;
> > >
> > > +-+---+--+
> > > | CaseType_idCaseType | Sizes_idsizes | qty  |
> > > +-+---+--+
> > > |  61 | 2 |1 |
> > > |  61 | 4 |1 |
> > > |  61 | 6 |1 |
> > > |  61 | 8 |1 |
> > > |  61 |24 |1 |
> > > |  61 |26 |1 |
> > > +-+---+--+
> > >
> > > I'm essentially trying to get the same resuts as this select, but in
> > > reverse
> > >
> > > I've tried using UNION but it doesnt seem to get what I'm trying for.
> > >
> > > Any help appreciated.
> > >
> > > Conor
> > >
> > >
> >
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTE

Re: query problem

[Conor McTernan]

Sheeri,

The table I'm searching on has a composite primary key since it's mapping an
N:M relationship between Cases and Sizes.

Here's the create statement for the table I'm searching on:

DROP TABLE IF EXISTS `CaseType_has_Sizes`;
CREATE TABLE `CaseType_has_Sizes` (
  `CaseType_idCaseType` int(10) unsigned NOT NULL,
  `Sizes_idsizes` int(10) unsigned NOT NULL,
  `qty` int(10) unsigned default NULL,
  PRIMARY KEY  (`CaseType_idCaseType`,`Sizes_idsizes`),
  KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`),
  KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`),
  CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`)
REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
  CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`)
REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Here's the Cases table:


DROP TABLE IF EXISTS `CaseType`;
CREATE TABLE `CaseType` (
  `idCaseType` int(10) unsigned NOT NULL auto_increment,
  `caseName` char(32) default NULL,
  PRIMARY KEY  (`idCaseType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and here's the Sizes case:

DROP TABLE IF EXISTS `Sizes`;
CREATE TABLE `Sizes` (
  `idsizes` int(10) unsigned NOT NULL auto_increment,
  `size` char(4) default NULL,
  `jpSize` char(4) default NULL,
  PRIMARY KEY  (`idsizes`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



On 2/9/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
>
> Hi Conor,
>
> The table you showed us has 2 primary keys, which is not possible.
> Can you do a SHOW CREATE TABLE on *each* table?
>
> -Sheeri
>
> On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote:
> > Hello, I'm having a hell of a time figuring this query out, maybe
> someone
> > can point me in the right direction.
> >
> > I have a table which lists the case configurations for cases of items.
> Each
> > case will have a different combination of sizes e.g. Case 002 has 2 size
> 5,
> > 2 size 7 and 2 size 8 items.
> >
> > What I'm trying to do is, given a group of sizes and quantities how can
> I
> > find the corresponding ID number for a case.
> >
> > Here's the description of the table.
> >
> +-+--+--+-+-+---+
> > | Field   | Type | Null | Key | Default | Extra
> |
> >
> +-+--+--+-+-+---+
> > | CaseType_idCaseType | int(10) unsigned | NO   | PRI | |
> |
> > | Sizes_idsizes   | int(10) unsigned | NO   | PRI | |
> |
> > | qty | int(10) unsigned | YES  | | NULL|
> |
> >
> +-+--+--+-+-+---+
> >
> >
> > Here's what I see if I do a select on a CaseType id.
> >
> >  select * from CaseType_has_Sizes where CaseType_idCaseType = 61;
> >
> > +-+---+--+
> > | CaseType_idCaseType | Sizes_idsizes | qty  |
> > +-+---+--+
> > |  61 | 2 |1 |
> > |  61 | 4 |1 |
> > |  61 | 6 |1 |
> > |  61 | 8 |1 |
> > |  61 |24 |1 |
> > |  61 |26 |1 |
> > +-+---+--+
> >
> > I'm essentially trying to get the same resuts as this select, but in
> > reverse
> >
> > I've tried using UNION but it doesnt seem to get what I'm trying for.
> >
> > Any help appreciated.
> >
> > Conor
> >
> >
>

Re: query problem

[sheeri kritzer]

Hi Conor,

The table you showed us has 2 primary keys, which is not possible. 
Can you do a SHOW CREATE TABLE on *each* table?

-Sheeri

On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote:
> Hello, I'm having a hell of a time figuring this query out, maybe someone
> can point me in the right direction.
>
> I have a table which lists the case configurations for cases of items. Each
> case will have a different combination of sizes e.g. Case 002 has 2 size 5,
> 2 size 7 and 2 size 8 items.
>
> What I'm trying to do is, given a group of sizes and quantities how can I
> find the corresponding ID number for a case.
>
> Here's the description of the table.
> +-+--+--+-+-+---+
> | Field   | Type | Null | Key | Default | Extra |
> +-+--+--+-+-+---+
> | CaseType_idCaseType | int(10) unsigned | NO   | PRI | |   |
> | Sizes_idsizes   | int(10) unsigned | NO   | PRI | |   |
> | qty | int(10) unsigned | YES  | | NULL|   |
> +-+--+--+-+-+---+
>
>
> Here's what I see if I do a select on a CaseType id.
>
>  select * from CaseType_has_Sizes where CaseType_idCaseType = 61;
>
> +-+---+--+
> | CaseType_idCaseType | Sizes_idsizes | qty  |
> +-+---+--+
> |  61 | 2 |1 |
> |  61 | 4 |1 |
> |  61 | 6 |1 |
> |  61 | 8 |1 |
> |  61 |24 |1 |
> |  61 |26 |1 |
> +-+---+--+
>
> I'm essentially trying to get the same resuts as this select, but in
> reverse
>
> I've tried using UNION but it doesnt seem to get what I'm trying for.
>
> Any help appreciated.
>
> Conor
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Problem

[Dto. Sistemas de Unitel]

Ok, Thanks for all Roger.

-Mensaje original-
De: Roger Baklund [mailto:[EMAIL PROTECTED] 
Enviado el: viernes, 22 de abril de 2005 4:06
Para: Dto. Sistemas de Unitel
CC: mysql@lists.mysql.com
Asunto: Re: Query Problem

Dto. Sistemas de Unitel wrote:
> You don't understand me, I refer that if in a table I use
productos.prod_id
> and in other table indexes.id if I can use this two fields like the same
> index, because when I named the two equal, the index start to work fine.

There should be no problem with joining two tables based on columns with 
different names. "productos.prod_id=indexes.id" should work. Both 
columns could be indexed, (in two separate indexes, of course, as they 
are in two separate tables), but only one index will be used, depending 
on the join order. It does not matter if you write 
"productos.prod_id=indexes.id" or "indexes.id=productos.prod_id", and it 
does not matter if you write "FROM productos,indexes" or "FROM 
indexes,productos" (unless STRAIGHT_JOIN is used).

In this case (se earlier posts in this thread) the table named indexes 
should be read first, then productos. That means an index on 
productos.prod_id will be used, if available. The name of the column in 
the productos table or the name of the related column in the indexes 
table does not matter. The "=" character in the ON clause or in the 
WHERE clause dictates which columns are related, not the name of the 
columns.

I don't know why your index did not work at first.

-- 
Roger



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Roger Baklund]

Dto. Sistemas de Unitel wrote:
You don't understand me, I refer that if in a table I use productos.prod_id
and in other table indexes.id if I can use this two fields like the same
index, because when I named the two equal, the index start to work fine.
There should be no problem with joining two tables based on columns with 
different names. "productos.prod_id=indexes.id" should work. Both 
columns could be indexed, (in two separate indexes, of course, as they 
are in two separate tables), but only one index will be used, depending 
on the join order. It does not matter if you write 
"productos.prod_id=indexes.id" or "indexes.id=productos.prod_id", and it 
does not matter if you write "FROM productos,indexes" or "FROM 
indexes,productos" (unless STRAIGHT_JOIN is used).

In this case (se earlier posts in this thread) the table named indexes 
should be read first, then productos. That means an index on 
productos.prod_id will be used, if available. The name of the column in 
the productos table or the name of the related column in the indexes 
table does not matter. The "=" character in the ON clause or in the 
WHERE clause dictates which columns are related, not the name of the 
columns.

I don't know why your index did not work at first.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Problem

[Dto. Sistemas de Unitel]

You don't understand me, I refer that if in a table I use productos.prod_id
and in other table indexes.id if I can use this two fields like the same
index, because when I named the two equal, the index start to work fine.

Thanks

-Mensaje original-
De: Roger Baklund [mailto:[EMAIL PROTECTED] 
Enviado el: jueves, 21 de abril de 2005 18:17
Para: mysql@lists.mysql.com
CC: Dto. Sistemas de Unitel
Asunto: Re: Query Problem

Dto. Sistemas de Unitel wrote:
> Hi Roger,
> 
> You are ok, there was an index problem in one table, they name of the rows
> wasn't equal and MySQL didn't recognize they as the same index. I have
> changed the row name and now is working fine, but I have a little
question,
> How can I use indexes with different names in it's  tables?

I'm not sure if I understand the question, but in general the names of 
the columns and indexes are not case sensitive, which in your case means 
prod_id and PROD_ID should be treated equal. Table names and database 
names are different, it depends on the filesystem the server is using:

http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html >

-- 
Roger



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Roger Baklund]

Dto. Sistemas de Unitel wrote:
Hi Roger,
You are ok, there was an index problem in one table, they name of the rows
wasn't equal and MySQL didn't recognize they as the same index. I have
changed the row name and now is working fine, but I have a little question,
How can I use indexes with different names in it's  tables?
I'm not sure if I understand the question, but in general the names of 
the columns and indexes are not case sensitive, which in your case means 
prod_id and PROD_ID should be treated equal. Table names and database 
names are different, it depends on the filesystem the server is using:

http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Problem

[Dto. Sistemas de Unitel]

Hi Roger,

You are ok, there was an index problem in one table, they name of the rows
wasn't equal and MySQL didn't recognize they as the same index. I have
changed the row name and now is working fine, but I have a little question,
How can I use indexes with different names in it's  tables?

Thanks for your help, you have been very helpful for me.
Roberto
-Mensaje original-
De: Roger Baklund [mailto:[EMAIL PROTECTED] 
Enviado el: miércoles, 20 de abril de 2005 18:30
Para: mysql@lists.mysql.com
CC: Dto. Sistemas de Unitel
Asunto: Re: Query Problem

Dto. Sistemas de Unitel wrote:
> Hi Roger,
> That was just I need. The order isn’t like you say:
> 
>
++-+---++---+-+-
>
++---+-+
> | id | select_type | table | type   | possible_keys | key |
key_len
> | ref| rows  | Extra
|
>
++-+---++---+-+-
>
++---+-+
> |  1 | SIMPLE  | t1| const  | PRIMARY,uniq  | uniq|
250
> | const  | 1 | Using temporary; Using filesort
|
> |  1 | SIMPLE  | t2| const  | PRIMARY,uniq  | uniq|
250
> | const  | 1 |
|
> |  1 | SIMPLE  | productos | ALL| PRIMARY,dupli | [NULL]  |
[NULL]
> | [NULL] | 16153 |
|
> |  1 | SIMPLE  | i2| eq_ref | PRIMARY,uniq  | PRIMARY |
16
> | unitel.productos.PROD_ID,const | 1 | Using where
|
> |  1 | SIMPLE  | i1| eq_ref | PRIMARY,uniq  | PRIMARY |
16
> | unitel.productos.PROD_ID,const | 1 | Using where
|
>
++-+---++---+-+-
>
++---+-+
>  
> 
> Productos is executed in the middle of the other two ones, the time of the
> query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a
> little more should be perfect.

It seems as there is no index on productos.prod_id?

-- 
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Roger Baklund]

Dto. Sistemas de Unitel wrote:
Hi Roger,
That was just I need. The order isn’t like you say:
++-+---++---+-+-
++---+-+
| id | select_type | table | type   | possible_keys | key | key_len
| ref| rows  | Extra   |
++-+---++---+-+-
++---+-+
|  1 | SIMPLE  | t1| const  | PRIMARY,uniq  | uniq| 250
| const  | 1 | Using temporary; Using filesort |
|  1 | SIMPLE  | t2| const  | PRIMARY,uniq  | uniq| 250
| const  | 1 | |
|  1 | SIMPLE  | productos | ALL| PRIMARY,dupli | [NULL]  |  [NULL]
| [NULL] | 16153 | |
|  1 | SIMPLE  | i2| eq_ref | PRIMARY,uniq  | PRIMARY |  16
| unitel.productos.PROD_ID,const | 1 | Using where |
|  1 | SIMPLE  | i1| eq_ref | PRIMARY,uniq  | PRIMARY |  16
| unitel.productos.PROD_ID,const | 1 | Using where |
++-+---++---+-+-
++---+-+
 

Productos is executed in the middle of the other two ones, the time of the
query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a
little more should be perfect.
It seems as there is no index on productos.prod_id?
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Dto. Sistemas de Unitel]

Hi Roger,
That was just I need. The order isn’t like you say:

++-+---++---+-+-
++---+-+
| id | select_type | table | type   | possible_keys | key | key_len
| ref| rows  | Extra   |
++-+---++---+-+-
++---+-+
|  1 | SIMPLE  | t1| const  | PRIMARY,uniq  | uniq| 250
| const  | 1 | Using temporary; Using filesort |
|  1 | SIMPLE  | t2| const  | PRIMARY,uniq  | uniq| 250
| const  | 1 | |
|  1 | SIMPLE  | productos | ALL| PRIMARY,dupli | [NULL]  |  [NULL]
| [NULL] | 16153 | |
|  1 | SIMPLE  | i2| eq_ref | PRIMARY,uniq  | PRIMARY |  16
| unitel.productos.PROD_ID,const | 1 | Using where |
|  1 | SIMPLE  | i1| eq_ref | PRIMARY,uniq  | PRIMARY |  16
| unitel.productos.PROD_ID,const | 1 | Using where |
++-+---++---+-+-
++---+-+
 

Productos is executed in the middle of the other two ones, the time of the
query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a
little more should be perfect.

Thanks another time, your help is very good for me.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Roger Baklund]

Dto. Sistemas de Unitel wrote:
[...]
something like 
/indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino 
and termino="computer" and termino=”intel”/ I know that is impossible, 
but maybe there is another way to make that).
Yes, there is another way. You _can_ join the same table multiple times, 
but you need to use table aliases. In this case you need to join two 
tables two times, both the terminos table and the indexes table.

SELECT STRAIGHT_JOIN
Why do you use the STRAIGHT_JOIN? It is often best to let MySQL optimize 
how to solve the query (order of joins), STRAIGHT_JOIN prevents this.

terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino,
sum(indexes.rank) as ordenate,productos.prod_unitel_id FROM 
terminos,indexes,productos where indexes.id=productos.prod_id and 
indexes.id_termino=terminos.id_termino and termino="computer" or 
indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino 
and termino="intel" group by (indexes.id) order by ordenate desc
In general, when you use both AND and OR in an expression, you should 
use parantheses to make it clear what happens. I have no idea how MySQL 
resolves the above WHERE clause. The parantheses on the GROUP BY clause 
are not needed, they are ignored.

Try something like this:
SELECT productos.prod_descripcion,
  sum(i1.rank+i2.rank) as ordenate,
  productos.prod_unitel_id
FROM
  terminos t1,terminos t2,indexes i1,indexes i2,productos
WHERE
  i1.id=productos.prod_id and
  i1.id_termino=t1.id_termino and
  t1.termino="computer" AND
  i2.id=productos.prod_id and
  i2.id_termino=t2.id_termino and
  t2.termino="intel"
GROUP BY
  productos.prod_descripcion,
  productos.prod_unitel_id
order by ordenate desc
This is almost the same statment, but your OR is replaced with an AND, 
it should result in a faster query.

In general, for a GROUP BY query, you should not select columns you are 
not using in the GROUP BY clause, except for when aggregate functions 
(like SUM(),AVG(),MIN(),MAX()...) are used on the column(s).

http://dev.mysql.com/doc/mysql/en/group-by-functions.html >
http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html >
Use EXPLAIN to check what join order the MySQL optmizer chooses for you, 
post the result if it is still too slow. If everything is indexed 
correctly, it should read t1/t2 first, then i1/i2 and finally productos.

http://dev.mysql.com/doc/mysql/en/explain.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Michael Stassen]

You may be able to get something like what you describe using 
GROUP_CONCAT(), if you have mysql 4.1, though it would be easier to get a 
comma separated list of authors than separate lines.  See the manual for 
details <http://dev.mysql.com/doc/mysql/en/group-by-functions.html>.

In my opinion, however, you are confusing presentation of data with how it 
is accessed.  Usually, presentation is the job of your code, not SQL.  If I 
were doing this, I would

  SELECT book.title, author.name
  FROM book
  JOIN authorbook ON book.bookid = authorbook.bookid
  JOIN author ON authorbook.authorid = author.authorid
  ORDER BY book.title, author.name;
to get the data.  (Notice I left out DISTINCT.  There's something wrong with 
the data in your tables if DISTINCT is needed here.)  To get the format you 
desire when printing the results, my code would only print the value of 
book.title if it is different from the value of book.title I previously 
printed.  Something like (pseudo code):

  last_title = ''
  for each result_row
  {
get title and author from result_row
if title != last_title
{ # first row of a new book, so print the tile
  print title
  last_title = title
}
else
{ # another author for the same book
  print blank space
}
print author
  }
That's pretty easy to translate into real code in every language I know.
Michael
Xristos Karvouneas wrote:
The code for generating the tables is shown below:
create table book
(
bookid char(12) not null,
dimensions char(15),
availability char(30),
booktype char(20),
publisher char(20),
isbn char(20),
itemsinstock int(3),
price float(5,2),
title char(50),
toc char(24),
picture char(36),
primary key (bookid));
create table author
(
authorid char(12) not null,
name char(24),
primary key (authorid));
create table authorbook
(
bookid char(12) not null references book,
authorid char(12) not null references author,
primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e. 
if the title is the same, I do not want it to be printed again...

Any ideas?
From: sol beach <[EMAIL PROTECTED]>
Reply-To: sol beach <[EMAIL PROTECTED]>
To: Xristos Karvouneas <[EMAIL PROTECTED]>
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 12:33:24 -0800
http://www.catb.org/~esr/faqs/smart-questions.html
It would help a lot to get answers if you shared the description of
all three tables.
On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas
<[EMAIL PROTECTED]> wrote:
> Dear All,
>
> I am faced with the following problem: I have got three tables - 
book,author
> and authorbook - containing information about books and authors 
(some books
> have multiple authors). I want to do a query that would print 
information
> like:
>
> Title 1 Author 1
>   Author 2
>
> Title 2Author 3
>  Author 4
>
> I have written the following:
>
> select distinct title, name
> from authorbook,book,author where
> authorbook.authorid=author.authorid and
> book.bookid=authorbook.bookid;
>
> hoping that it will do what I want, but I am only getting the first 
author
> for each book (probably because of the distinct keyword).
>
> Is there any way I can modify the query so that it does what I want 
it to
> do?
>
> I look forward to hearing from you soon.
>
> Thanks in advance.
>
> George
>
> _
> Express yourself instantly with MSN Messenger! Download today it's 
FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>

_
FREE pop-up blocking with the new MSN Toolbar - get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[SGreen]

You are confusing data retrieval with data presentation. Sure, SQL can do 
many things to format data but some data formats are better achieved 
through the programming language you are using to present this data for 
viewing. In my opinion, the type of formatting you want to do is one of 
those tasks. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Xristos Karvouneas" <[EMAIL PROTECTED]> wrote on 03/16/2005 09:03:25 
AM:

> The code for generating the tables is shown below:
> 
> create table book
> (
> bookid char(12) not null,
> dimensions char(15),
> availability char(30),
> booktype char(20),
> publisher char(20),
> isbn char(20),
> itemsinstock int(3),
> price float(5,2),
> title char(50),
> toc char(24),
> picture char(36),
> primary key (bookid));
> 
> 
> create table author
> (
> authorid char(12) not null,
> name char(24),
> primary key (authorid));
> 
> create table authorbook
> (
> bookid char(12) not null references book,
> authorid char(12) not null references author,
> primary key(bookid,authorid));
> 
> 
> Basically, I want to get it in the format specified in the message, i.e. 
if 
> the title is the same, I do not want it to be printed again...
> 
> Any ideas?
> 
> >From: gerald_clark <[EMAIL PROTECTED]>
> >To: Xristos Karvouneas <[EMAIL PROTECTED]>
> >CC: mysql@lists.mysql.com
> >Subject: Re: Query Problem
> >Date: Tue, 15 Mar 2005 14:49:45 -0600
> >
> >Xristos Karvouneas wrote:
> >
> >>Dear All,
> >>
> >>I am faced with the following problem: I have got three tables - 
> >>book,author and authorbook - containing information about books and 
> >>authors (some books have multiple authors). I want to do a query that 
> >>would print information like:
> >>
> >>Title 1 Author 1
> >>  Author 2
> >>
> >>Title 2Author 3
> >> Author 4
> >>
> >>I have written the following:
> >>
> >>select distinct title, name
> >>from authorbook,book,author where
> >>authorbook.authorid=author.authorid and
> >>book.bookid=authorbook.bookid;
> >
> >You want to add:
> >ORDER BY title,name;
> >
> >You probably are getting them all, but not in the order you expect.
> >
> >>
> >>hoping that it will do what I want, but I am only getting the first 
author 
> >>for each book (probably because of the distinct keyword).
> >>
> >>Is there any way I can modify the query so that it does what I want it 
to 
> >>do?
> >>
> >>I look forward to hearing from you soon.
> >>
> >>Thanks in advance.
> >>
> >>George
> >>
> >>_
> >>Express yourself instantly with MSN Messenger! Download today it's 
FREE! 
> >>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >>
> >>
> >
> 
> _
> Express yourself instantly with MSN Messenger! Download today it's FREE! 

> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Query Problem

[Xristos Karvouneas]

The code for generating the tables is shown below:
create table book
(
bookid char(12) not null,
dimensions char(15),
availability char(30),
booktype char(20),
publisher char(20),
isbn char(20),
itemsinstock int(3),
price float(5,2),
title char(50),
toc char(24),
picture char(36),
primary key (bookid));
create table author
(
authorid char(12) not null,
name char(24),
primary key (authorid));
create table authorbook
(
bookid char(12) not null references book,
authorid char(12) not null references author,
primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e. if 
the title is the same, I do not want it to be printed again...

Any ideas?
From: gerald_clark <[EMAIL PROTECTED]>
To: Xristos Karvouneas <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 14:49:45 -0600
Xristos Karvouneas wrote:
Dear All,
I am faced with the following problem: I have got three tables - 
book,author and authorbook - containing information about books and 
authors (some books have multiple authors). I want to do a query that 
would print information like:

Title 1 Author 1
 Author 2
Title 2Author 3
Author 4
I have written the following:
select distinct title, name
from authorbook,book,author where
authorbook.authorid=author.authorid and
book.bookid=authorbook.bookid;
You want to add:
ORDER BY title,name;
You probably are getting them all, but not in the order you expect.
hoping that it will do what I want, but I am only getting the first author 
for each book (probably because of the distinct keyword).

Is there any way I can modify the query so that it does what I want it to 
do?

I look forward to hearing from you soon.
Thanks in advance.
George
_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/



_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Xristos Karvouneas]

The code for generating the tables is shown below:
create table book
(
bookid char(12) not null,
dimensions char(15),
availability char(30),
booktype char(20),
publisher char(20),
isbn char(20),
itemsinstock int(3),
price float(5,2),
title char(50),
toc char(24),
picture char(36),
primary key (bookid));
create table author
(
authorid char(12) not null,
name char(24),
primary key (authorid));
create table authorbook
(
bookid char(12) not null references book,
authorid char(12) not null references author,
primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e. if 
the title is the same, I do not want it to be printed again...

Any ideas?
From: sol beach <[EMAIL PROTECTED]>
Reply-To: sol beach <[EMAIL PROTECTED]>
To: Xristos Karvouneas <[EMAIL PROTECTED]>
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 12:33:24 -0800
http://www.catb.org/~esr/faqs/smart-questions.html
It would help a lot to get answers if you shared the description of
all three tables.
On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas
<[EMAIL PROTECTED]> wrote:
> Dear All,
>
> I am faced with the following problem: I have got three tables - 
book,author
> and authorbook - containing information about books and authors (some 
books
> have multiple authors). I want to do a query that would print 
information
> like:
>
> Title 1 Author 1
>   Author 2
>
> Title 2Author 3
>  Author 4
>
> I have written the following:
>
> select distinct title, name
> from authorbook,book,author where
> authorbook.authorid=author.authorid and
> book.bookid=authorbook.bookid;
>
> hoping that it will do what I want, but I am only getting the first 
author
> for each book (probably because of the distinct keyword).
>
> Is there any way I can modify the query so that it does what I want it 
to
> do?
>
> I look forward to hearing from you soon.
>
> Thanks in advance.
>
> George
>
> _
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
_
FREE pop-up blocking with the new MSN Toolbar - get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[gerald_clark]

Xristos Karvouneas wrote:
Dear All,
I am faced with the following problem: I have got three tables - 
book,author and authorbook - containing information about books and 
authors (some books have multiple authors). I want to do a query that 
would print information like:

Title 1 Author 1
 Author 2
Title 2Author 3
Author 4
I have written the following:
select distinct title, name
from authorbook,book,author where
authorbook.authorid=author.authorid and
book.bookid=authorbook.bookid;
You want to add:
ORDER BY title,name;
You probably are getting them all, but not in the order you expect.
hoping that it will do what I want, but I am only getting the first 
author for each book (probably because of the distinct keyword).

Is there any way I can modify the query so that it does what I want it 
to do?

I look forward to hearing from you soon.
Thanks in advance.
George
_
Express yourself instantly with MSN Messenger! Download today it's 
FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem

[Gordon]

Did you want 
WHERE  Name LIKE 'sandy' 
   OR (main_data.Display_In_Search = 1 
   AND main_data.Expiry_Date >= CurDate())


OR 


WHERE  main_data.Expiry_Date >= CurDate()
   AND (Name LIKE 'sandy' 
OR main_data.Display_In_Search = 1 )
   

-Original Message-
From: Richard Duke [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 17, 2005 4:03 PM
To: mysql@lists.mysql.com
Subject: Query problem

Hi

I have a problem with a query that has many joined tables. The query brings 
back 80 records instead of just one. Any suggestions on how I can overcome 
this?

Many thanks

Richard

Query below:-

SELECT *
FROM (main_data INNER JOIN main_data_facilities ON
main_data_facilities.RecNo = main_data.RecNo) INNER JOIN main_data_meals ON
main_data_meals.RecNo = main_data.RecNo) INNER JOIN main_data_non_smoking ON
main_data_non_smoking.RecNo = main_data.RecNo) INNER JOIN 
main_data_payment_types ON
main_data_payment_types.RecNo = main_data.RecNo) INNER JOIN 
main_data_pets_welcome ON
main_data_pets_welcome.RecNo = main_data.RecNo) INNER JOIN 
main_data_special_dietary_requirements ON
main_data_special_dietary_requirements.RecNo = main_data.RecNo) INNER JOIN 
counties ON
counties.ID = main_data.County) INNER JOIN countries ON
countries.ID = main_data.Country) INNER JOIN facilities ON
facilities.ID = main_data_facilities.ID) INNER JOIN meals ON
meals.ID = main_data_meals.ID) INNER JOIN non_smoking ON
non_smoking.ID = main_data_non_smoking.ID) INNER JOIN payment_types ON
payment_types.ID = main_data_payment_types.ID) INNER JOIN pets_welcome ON
pets_welcome.ID = main_data_pets_welcome.ID) INNER JOIN 
special_dietary_requirements ON
special_dietary_requirements.ID = main_data_special_dietary_requirements.ID 
AND main_data_meals.RecNo = main_data_facilities.RecNo
WHERE Name LIKE 'sandy'
OR ( main_data.Display_In_Search = 1 )
AND ( main_data.Expiry_Date >= CurDate() )


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[DanielWalker]

On Tuesday 08 February 2005 17:03, Coz Web wrote:
>
> This will (as I believe Daniel suggested) keep things relatively
> simple, avoiding an overly complex query that you cannot maintain in
> the future.

Well, my solution was, as you'll have observed, vastly over-complicated. I 
think I'd misunderstood the nature of the problem (or I'm just too bunged up 
with cold, at the moment, to think straight :-/ - whichever)...

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem in num_row

[Ligaya Turmelle]

Your not getting a valid result from your query. Add
if (!$result) { echo 'Bad query - message: ' . mysql_error();}
I think it will give you a syntax error on your query.  MySQL syntax for
the LIMIT clause is:
[quote]
The LIMIT clause can be used to constrain the number of rows returned by 
the SELECT statement. LIMIT takes one or two numeric arguments, which 
must be integer constants.

With two arguments, the first argument specifies the offset of the first 
row to return, and the second specifies the maximum number of rows to 
return. The offset of the initial row is 0 (not 1):

mysql> SELECT * FROM table LIMIT 5,10;  # Retrieve rows 6-15
[/quote]
Respectfully,
Ligaya Turmelle
Aji Andri wrote:
here a syntax
$query=&$conn->Execute("select * from itemlocation
where id limit 1 - 50");
$result=mysql_query($query);
$num_result=mysql_num_rows($result);
and error message are syntax error in
$num_result=mysql_num_rows($result); object unknown 
can someone please give a direction

all I want to do is comparing data in itemlocation
with a  value and if value in item location is smaller
then the value I make then it will appear in a message
Aji
		
__ 
Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 
http://my.yahoo.com 
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Coz Web]

On Tue, 08 Feb 2005 15:52:02 +0100, Joachim Klöfers
<[EMAIL PROTECTED]> wrote:
> Oh, oh, Joachim,
> not able to calculate, but asking questions about queries.
> 
> Coz , of course its supposed to be 1955  (id 13 & 47)
> 
> Joachim
> 
> 
> Coz Web schrieb: 
> Just to confirm, is amount2 for region 1 supposed to be 1630 (id 47)
or 1955
> (ids 13 & 47)?

Thought I'd check I hadn't missed something.

Do the results *have* to be from a single query? I would suggest using
something like the previous query for amount1 and this for amount2 and
then combining them in your application.

SELECT region,  SUM(amount2)
FROM t1  LEFT JOIN  t3 USING(id)
GROUP BY region

This will (as I believe Daniel suggested) keep things relatively
simple, avoiding an overly complex query that you cannot maintain in
the future. That is, unless someone on this list provides a simple
single-query solution, which they very well may do.

HTH

Coz

-- 
CozWeb Solutions Ltd
http://www.cozweb.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Coz Web]

On Tue, 8 Feb 2005 11:37:20 +, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote:
> > Hi, all
> >
> > I hope somebody can help me.
> 
> 
> >
> > Many thanks in advance
> > Joachim
> 
> That is a very thorny problem. May I observe that you will find things much
> easier, if you add unique primary keys to tables 2 & 3, thus:
> 
> ALTER TABLE  ADD pri_key MEDIUMINT
> AUTO_INCREMENT PRIMARY KEY FIRST;
> 
> You can then left join the second table to itself and get along on the first
> stage of solving the wider problem. Assuming the second table is called
> table2, for instance, you could do something like
> 
> SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2
> AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id
> AND t2a.current=t2b.current GROUP BY a.id;
> (Note, this only solves the 'sum if current' part of the problem, but this is
> certainly one of the harder aspects that needs solving, anyway)
> 
> Even that is probably far more tortuous than it needs to be, but I can't think
> of anything better, at present. Anyway, as I say, I personally don't see how
> you will solve the wider problem without some means of distinguishing unique
> records from one another in tables 2 & 3.
> 
> I'd also ask you to consider whether it was even wise to attempt to solve this
> type of thing with a single query, if it means that anyone trying to modify
> it at a future date is going to have to gaze at it for several hours before
> they could begin work on the alterations :).
> 
> Hope that helps to some extent.
> 
> Dan
> 
Hmm..

What's wrong (for the first part) in simply doing :

SELECT region, SUM(amount1)
FROM t1 , t2
WHERE t1.id=t2.id AND current='J'
GROUP BY region

works for me. 

Now to see about the sum of amount2

-- 
CozWeb Solutions Ltd
http://www.cozweb.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Coz Web]

Just to confirm, is amount2 for region 1 supposed to be 1630 (id 47)
or 1955 (ids 13 & 47)?

Coz

 

> What I would like to have is a result like this:
> ++---+--+
> | region | sum(amount1) if current=J | sum(amount2) |
> ++---+--+
> |   1| 54800 | 1630 |
> |   2| 14075 |  640 |
> ++---+--+
> 
> Many thanks in advance
> Joachim
> 

-- 
CozWeb Solutions Ltd
http://www.cozweb.net

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[daniel]

On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote:
> Hi, all
>
> I hope somebody can help me.


>
> Many thanks in advance
> Joachim

That is a very thorny problem. May I observe that you will find things much 
easier, if you add unique primary keys to tables 2 & 3, thus:

ALTER TABLE  ADD pri_key MEDIUMINT 
AUTO_INCREMENT PRIMARY KEY FIRST;

You can then left join the second table to itself and get along on the first 
stage of solving the wider problem. Assuming the second table is called 
table2, for instance, you could do something like

SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2 
AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id 
AND t2a.current=t2b.current GROUP BY a.id;
(Note, this only solves the 'sum if current' part of the problem, but this is 
certainly one of the harder aspects that needs solving, anyway)

Even that is probably far more tortuous than it needs to be, but I can't think 
of anything better, at present. Anyway, as I say, I personally don't see how 
you will solve the wider problem without some means of distinguishing unique 
records from one another in tables 2 & 3.

I'd also ask you to consider whether it was even wise to attempt to solve this 
type of thing with a single query, if it means that anyone trying to modify 
it at a future date is going to have to gaze at it for several hours before 
they could begin work on the alterations :).

Hope that helps to some extent.

Dan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[gerald_clark]

Schalk Neethling wrote:
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 
'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'

SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND ( jstate = 
'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California' )

For some reason even when doctype is not equal to Motion it still 
returns these documents. Can someone please let me know where I am 
going wrong?
The most important thing about the query is that first only documents 
that match the doctype should be returned so I suppose before even 
bothering to check the rest of the query only those documents should 
be found. How do I go about ensuring that only documents that matches 
the doctype is returned and no other documents.

Thanks for any help on this.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem

[Schalk Neethling]

Hey there
I have the following table structure:
CREATE TABLE documents (
 id int(11) NOT NULL auto_increment,
 user varchar(50) NOT NULL default '',
 olduser varchar(50) NOT NULL default '',
 username varchar(100) NOT NULL default '',
 uploaddate timestamp(14) NOT NULL,
 docdate varchar(100) NOT NULL default '',
 docno varchar(255) NOT NULL default '',
 title varchar(150) NOT NULL default '',
 summary varchar(255) NOT NULL default '',
 content text NOT NULL,
 doctype varchar(80) NOT NULL default '',
 docuri varchar(255) NOT NULL default '',
 vjudge varchar(100) NOT NULL default '',
 vexpert varchar(100) NOT NULL default '',
 vspeciality varchar(150) NOT NULL default '',
 didiversity varchar(100) NOT NULL default '',
 dicity varchar(80) NOT NULL default '',
 didiversitybar varchar(80) NOT NULL default '',
 dilawfirm varchar(200) NOT NULL default '',
 jstate varchar(100) NOT NULL default '',
 jdistrict varchar(100) NOT NULL default '',
 jappellate varchar(100) NOT NULL default '',
 keywords varchar(255) NOT NULL default '',
 PRIMARY KEY  (id),
 FULLTEXT KEY content (content)
) TYPE=MyISAM;
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
For some reason even when doctype is not equal to Motion it still 
returns these documents. Can someone please let me know where I am going 
wrong?
The most important thing about the query is that first only documents 
that match the doctype should be returned so I suppose before even 
bothering to check the rest of the query only those documents should be 
found. How do I go about ensuring that only documents that matches the 
doctype is returned and no other documents.

Thanks for any help on this.
--
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
This message contains information that is considered to be sensitive or confidential 
and may not be forwarded or disclosed to any other party without the permission of the 
sender. If you received this message in error, please notify me immediately so that I 
can correct and delete the original email. Thank you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[SGreen]

What is wrong with me today?!?! I explained myself incorrectly:

a) information from outbound_fax_info where the barcodes DO match.

Sorry all!!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


   

  [EMAIL PROTECTED]

   To:   auslander <[EMAIL PROTECTED]> 

  06/30/2004 02:22 cc:   [EMAIL PROTECTED] 

  PM   Fax to: 

   Subject:  Re: query problem 

   






Eliminate the rows from outbound_fax_info where the barcode is blank. The
result of the JOIN will be all of the rows of inbound_fax_info matched up
to:
  a) information form outbound_fax_info except where the barcodes match
  b) blank columns where the barcodes didn't match.

Use the COALESCE() function (it returns the first non-null value from a
list of values) and you gain the ability to replace missing values with
something else.

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  COALESCE(b.customer_name,'no customer')
  COALESCE(b.customer_fax,'no customer fax')
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b
  on ucase(a.barcode) = ucase(b.barcode)
  AND b.barcode > ''

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




  auslander

  <[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
  ay.rr.com>   cc:

   Fax to:

  06/30/2004 01:50 Subject:  query problem

  PM







using mysql 4.0.x
please review the following sql then see below for the problem:

DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);

DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  customer_name varchar(100) default NULL,
  customer_fax varchar(100) default NULL,
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob
Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);

DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned NOT NULL default '0',
  department_id int(10) unsigned NOT NULL default '0',
  customer_name varchar(100) NOT NULL default '',
  customer_fax varchar(100) NOT NULL default '',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  b

Re: query problem

[auslander]

Actually, i figured it out.  don't know why it was so hard to see it.  
all i did was change:

LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode)
to:
LEFT JOIN outbound_fax_info b on (b.barcode != '' and ucase(a.barcode = 
b.barcode).

if barcode was blank in outbound (b) then the data was skipped and only 
the data from inbound (a) was inserted into the new table.

thanks for your assistance tho. much appreciated.
[EMAIL PROTECTED] wrote:
Eliminate the rows from outbound_fax_info where the barcode is blank. The
result of the JOIN will be all of the rows of inbound_fax_info matched up
to:
 a) information form outbound_fax_info except where the barcodes match
 b) blank columns where the barcodes didn't match.
Use the COALESCE() function (it returns the first non-null value from a
list of values) and you gain the ability to replace missing values with
something else.
INSERT INTO inbound_fax_info_tmp (
 fax_id,
 barcode,
 document_id,
 department_id,
 customer_name,
 customer_fax)
SELECT
 a.fax_id,
 a.barcode,
 b.document_id,
 a.department_id,
 COALESCE(b.customer_name,'no customer')
 COALESCE(b.customer_fax,'no customer fax')
FROM
 inbound_fax_info a
LEFT JOIN outbound_fax_info b
 on ucase(a.barcode) = ucase(b.barcode)
 AND b.barcode > ''
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

  
 auslander
 <[EMAIL PROTECTED]To:   [EMAIL PROTECTED] 
 ay.rr.com>   cc: 
  Fax to: 
 06/30/2004 01:50 Subject:  query problem 
 PM   
  
  


using mysql 4.0.x
please review the following sql then see below for the problem:
DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);
DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 customer_name varchar(100) default NULL,
 customer_fax varchar(100) default NULL,
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob
Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);
DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned NOT NULL default '0',
 department_id int(10) unsigned NOT NULL default '0',
 customer_name varchar(100) NOT NULL default '',
 customer_fax varchar(100) NOT NULL default '',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info_tmp (
 fax_id,
 barcode,
 document_id,
 department_id,
 customer_name,
 customer_fax)
SELECT
 a.fax_id,
 a.barcode,
 b.document_id,
 a.department_id,
 b.customer_name,
 b.customer_fax
FROM
 inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);
what i am attempting to do is create a 3rd table that contains all the
info from inbound_fax_info plus a couple columns from outbound_fax_info
based on the barcode column they both contain.  problem comes when the
barcode i

Re: query problem

[SGreen]

Eliminate the rows from outbound_fax_info where the barcode is blank. The
result of the JOIN will be all of the rows of inbound_fax_info matched up
to:
  a) information form outbound_fax_info except where the barcodes match
  b) blank columns where the barcodes didn't match.

Use the COALESCE() function (it returns the first non-null value from a
list of values) and you gain the ability to replace missing values with
something else.

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  COALESCE(b.customer_name,'no customer')
  COALESCE(b.customer_fax,'no customer fax')
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b
  on ucase(a.barcode) = ucase(b.barcode)
  AND b.barcode > ''

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  auslander

  <[EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  ay.rr.com>   cc: 

   Fax to: 

  06/30/2004 01:50 Subject:  query problem 

  PM   

   

   





using mysql 4.0.x
please review the following sql then see below for the problem:

DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);

DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  customer_name varchar(100) default NULL,
  customer_fax varchar(100) default NULL,
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob
Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);

DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned NOT NULL default '0',
  department_id int(10) unsigned NOT NULL default '0',
  customer_name varchar(100) NOT NULL default '',
  customer_fax varchar(100) NOT NULL default '',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  b.customer_name,
  b.customer_fax
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);


what i am attempting to do is create a 3rd table that contains all the
info from inbound_fax_info plus a couple columns from outbound_fax_info
based on the barcode column they both contain.  problem comes when the
barcode is '' (blank).  barcodes can be blank in outbound and inbound
(for specific reasons).  is there a way to write this INSERT INTO query
where it will select the appropriate data from inbound and outbound if a
barcode exists and insert into tmp inbound, otherwise just insert a new
row with inbound data and defaults only in tmp inbound?

any help would be appreciated

Chris

--
MyS

Re: Query problem

[Eric Bergen]

Post the table structure, what that query it returning and what you
think it should return.

-Eric

On Sun, 27 Jun 2004 23:33:55 +0200, Schalk <[EMAIL PROTECTED]> wrote:
> 
> Why is the following query retuning doctype's different to what is asked
> for?
> SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype
> = 'Verdict'
>  
> Any ideas? As far as I can see it should only return a document if it is a
> Verdict and matches the state California: State Court.
> 
> Kind Regards
> Schalk Neethling
> Web Developer.Designer.Programmer.President
> Volume4.Development.Multimedia.Branding
> emotionalize.conceptualize.visualize.realize
> Tel: +27125468436
> Fax: +27125468436
> email:[EMAIL PROTECTED]
> web: www.volume4.co.za
>  
> This message contains information that is considered to be sensitive or
> confidential and may not be forwarded or disclosed to any other party
> without the permission of the sender. If you received this message in error,
> please notify me immediately so that I can correct and delete the original
> email. Thank you.
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem

[Schalk]

Why is the following query retuning doctype’s different to what is asked
for?
SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype
= 'Verdict'
 
Any ideas? As far as I can see it should only return a document if it is a
Verdict and matches the state California: State Court.


Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
 
This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem with Lists

[Eric Scuccimarra]

I understand why we would want these to be in relational forms but in this 
situation it isn't practical for a number of reasons. Normally that would 
be what I would do.

However in this case the nature of the application is such that doing this 
would cause an enormous load on the system as we would regularly have to 
delete and recreate this entire table and that would require looping 
through millions of rows on the application side.

So we decided that keeping lists would be easier and more efficient than 
forcing the application and MySQL to go through enormous amounts of data 
which would require the potential execution of over a million individual 
queries anytime someone changes anything.

BTW - FIND_IN_SET works for the original issue.
At 12:53 PM 6/24/2004 -0400, [EMAIL PROTECTED] wrote:
I understand how these lists come into existence (trust me I have had to
deal with enough of them). However, it is standard practice when working
with _relational_ databases to split those lists of numbers into unique
record pairs in a separate table. Your original source data was not
relational, was it... ;-)
To illustrate I will have to make up a scenario:
Lets say you have a table of "employees" with columns like ID, Name, etc.
Imagine this table has a field that holds a list of numbers that represents
everyone that reports to the employee (their subordinates). In order to see
if an employee is a subordinate of another employee, you would have to do a
query like the one you are asking about.
Here's the problem, in most languages you leave off the last separator so
the list looks like "3,4,12,20,22". Doing a substring search in that list
is not easy. Say you want to see if employee 2 is in the list for employee
1, you would have to search employee 1's list for "2," , ",2," , and
",2" just to make sure you _didn't_  match on 12, 20 or 22. (in this
example I used  and  to represent the "beginning of string" and
the "end of string". Those symbols don't actually work in MySQL but you
_can_ do those searches at least a couple of different ways). That is a
very slow search as you cannot use any indices and you will have to perform
at least 3 substring comparisons PER ROW of your data. Or, you could try
the FIND_IN_SET() function
(http://dev.mysql.com/doc/mysql/en/String_functions.html). Once again, you
cannot use an index and you will have to execute the function on EVERY ROW
of data in your table, even if you only find 1 row of data.
One way out of this mess is to create another table like
CREATE TABLE employee_subordinate (
  employee_ID int,
  subordinate_ID int,
  PRIMARY KEY (employee_ID, subordinate_ID),
  Key (subordinate_ID)
)
Then you would need to insert just one row for each subordinate in the list
like
INSERT employee_subordinate VALUES (1,3), (1,4), (1,12), (1,20), (1,22)
There are MANY advantages to this style of design: Searching the
employee_subordinate table will be lightning quick as all values are no
longer strings but integers. The indexes will be smaller so you will be
able to fit them into memory (also faster). And, you could use other
relational techniques like FOREIGN KEYS (if you are using InnoDB) to ensure
that only valid IDs are entered into the table.
If you have to make it seem as though the data is stored as a list, MySQL
has some functions to convert a rowset of values into separated lists and
back. In this example if you wanted to present all of the subordinates to
employee 1 as a list you could write the following query
SELECT employee_ID, GROUP_CONCAT(subordinate_ID) as subordinates
FROM employee_subordinate
WHERE employee_ID = 1
GROUP BY employee_ID
and that would return:
+-+--+
| employee_ID | subordinates |
+-+--+
|   1 | 3,4,12,20,22 |
+-+--+
1 row in set (0.00 sec)
I know this may not match directly to your data situation but you offered
few specifics. Hope it helps.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


  Eric 
Scuccimarra 

  <[EMAIL PROTECTED]>To: 
[EMAIL PROTECTED]
   cc: 

  06/24/2004 11:11 Fax 
to:
  AM   Subject:  Query Problem 
with Lists




I have a table where one field is a long list of numbers in comma-delimited
format.
I need to do a query like:
SELECT *
FROM Table
WHERE[number] IN list
If I cut and paste the actual list in it works fine but when I use the
column-name containing the list it returns nothing. I've been searching the
MySQL docs for hours and haven't turned up anything. Apparently I can use
set functions but the lists seem to be too big to store as sets.
Any help is appreciated.
Eric

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQ

Re: Query Problem with Lists

[SGreen]

I understand how these lists come into existence (trust me I have had to
deal with enough of them). However, it is standard practice when working
with _relational_ databases to split those lists of numbers into unique
record pairs in a separate table. Your original source data was not
relational, was it... ;-)

To illustrate I will have to make up a scenario:

Lets say you have a table of "employees" with columns like ID, Name, etc.
Imagine this table has a field that holds a list of numbers that represents
everyone that reports to the employee (their subordinates). In order to see
if an employee is a subordinate of another employee, you would have to do a
query like the one you are asking about.

Here's the problem, in most languages you leave off the last separator so
the list looks like "3,4,12,20,22". Doing a substring search in that list
is not easy. Say you want to see if employee 2 is in the list for employee
1, you would have to search employee 1's list for "2," , ",2," , and
",2" just to make sure you _didn't_  match on 12, 20 or 22. (in this
example I used  and  to represent the "beginning of string" and
the "end of string". Those symbols don't actually work in MySQL but you
_can_ do those searches at least a couple of different ways). That is a
very slow search as you cannot use any indices and you will have to perform
at least 3 substring comparisons PER ROW of your data. Or, you could try
the FIND_IN_SET() function
(http://dev.mysql.com/doc/mysql/en/String_functions.html). Once again, you
cannot use an index and you will have to execute the function on EVERY ROW
of data in your table, even if you only find 1 row of data.

One way out of this mess is to create another table like

CREATE TABLE employee_subordinate (
  employee_ID int,
  subordinate_ID int,
  PRIMARY KEY (employee_ID, subordinate_ID),
  Key (subordinate_ID)
)

Then you would need to insert just one row for each subordinate in the list
like

INSERT employee_subordinate VALUES (1,3), (1,4), (1,12), (1,20), (1,22)

There are MANY advantages to this style of design: Searching the
employee_subordinate table will be lightning quick as all values are no
longer strings but integers. The indexes will be smaller so you will be
able to fit them into memory (also faster). And, you could use other
relational techniques like FOREIGN KEYS (if you are using InnoDB) to ensure
that only valid IDs are entered into the table.

If you have to make it seem as though the data is stored as a list, MySQL
has some functions to convert a rowset of values into separated lists and
back. In this example if you wanted to present all of the subordinates to
employee 1 as a list you could write the following query

SELECT employee_ID, GROUP_CONCAT(subordinate_ID) as subordinates
FROM employee_subordinate
WHERE employee_ID = 1
GROUP BY employee_ID

and that would return:
+-+--+
| employee_ID | subordinates |
+-+--+
|   1 | 3,4,12,20,22 |
+-+--+
1 row in set (0.00 sec)

I know this may not match directly to your data situation but you offered
few specifics. Hope it helps.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  Eric Scuccimarra 

  <[EMAIL PROTECTED]>To:   [EMAIL PROTECTED]   
  
   cc: 

  06/24/2004 11:11 Fax to: 

  AM   Subject:  Query Problem with Lists  

   

   





I have a table where one field is a long list of numbers in comma-delimited

format.

I need to do a query like:

SELECT *
FROM Table
WHERE[number] IN list

If I cut and paste the actual list in it works fine but when I use the
column-name containing the list it returns nothing. I've been searching the

MySQL docs for hours and haven't turned up anything. Apparently I can use
set functions but the lists seem to be too big to store as sets.

Any help is appreciated.

Eric



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL

Re: Query Problem with Lists

[Eamon Daly]

I believe you could do:

SELECT *
FROM Table
WHERE FIND_IN_SET(number, comma_delimited_field)

but this will be /very/ slow. This query is forced to
examine each and every row to determine whether or not your
number is in the field.

The better solution is to break up that field, which is
generally easier than you'd think. If your table looks like
this:

foobar
1001,2,3
1012,3
1024,5,6

you can easily convert it to one that looks like:

foobar
1001
1002
1003
1012
1013
1024
1025
1026

This moves you from varchars to ints, variable-length rows
to fixed, and you'll now be able to index bar properly.
You'll see a dramatic performance improvement.


Eamon Daly



- Original Message - 
From: "Eric Scuccimarra" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, June 24, 2004 10:11 AM
Subject: Query Problem with Lists


> I have a table where one field is a long list of numbers in
comma-delimited
> format.
>
> I need to do a query like:
>
> SELECT *
> FROM Table
> WHERE [number] IN list
>
> If I cut and paste the actual list in it works fine but when I use the
> column-name containing the list it returns nothing. I've been searching
the
> MySQL docs for hours and haven't turned up anything. Apparently I can use
> set functions but the lists seem to be too big to store as sets.
>
> Any help is appreciated.
>
> Eric


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem with Lists

[Brent Baisley]

You probably shouldn't have setup your database structure like that. 
You should always break out multiple values into a separate table, each 
value being stored in one record, then "link" them through a common 
record id. A one to many relation.
As far as the database is concerned, those aren't delimited numbers, 
they're just a long text string that happens to not contain any 
letters. You could try setting up a full text index on that field and 
use the match/against search structure. Although you should read up on 
full text searches since there are a lot of options that will affect 
your search. I've never tried full text searching on numbers, I don't 
know if MySQL would consider them words or not.

The other alternative, is a contained in search which is what you are 
trying to do, but you have the syntax wrong. If you want to search for 
a string contained within a field, you need to use the LIKE and wild 
card symbold: %

SELECT * FROM table WHERE field LIKE '%[number]%'
But that search is going to be slow since it can't use any indexes. You 
also have the problem of substring matching. For instance, searching on 
'%9%' would find records containing 9, or 99, or 911, or 19, or 293, 
etc. The only way around that would be to pad you field with a comma 
(,) at the beginning and end so you could search on '%,9,%'.


On Jun 24, 2004, at 11:11 AM, Eric Scuccimarra wrote:
I have a table where one field is a long list of numbers in 
comma-delimited format.

I need to do a query like:
SELECT  *
FROMTable
WHERE   [number] IN list
If I cut and paste the actual list in it works fine but when I use the 
column-name containing the list it returns nothing. I've been 
searching the MySQL docs for hours and haven't turned up anything. 
Apparently I can use set functions but the lists seem to be too big to 
store as sets.

Any help is appreciated.
Eric
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem

[Andrew Braithwaite]

Hi,

You need:

select job,avg(sal) from emp group by 1 order by 2 limit 1;

Cheers,

Andrew

-Original Message-
From: Edouard Lauer [mailto:[EMAIL PROTECTED] 
Sent: Saturday 31 January 2004 19:23
To: [EMAIL PROTECTED]
Subject: Query problem


Hello,

I would like to query the littlest average salary. I have a table with
employees and their salary like that:

+---+--+
| job   | sal  |
+---+--+
| CLERK |  800 |
| SALESMAN  | 1600 |
| SALESMAN  | 1250 |
| MANAGER   | 2975 |
| SALESMAN  | 1250 |
| MANAGER   | 2850 |
| MANAGER   | 2450 |
| ANALYST   | 3000 |
| PRESIDENT | 5000 |
| SALESMAN  | 1500 |
| CLERK | 1100 |
| CLERK |  950 |
| ANALYST   | 3000 |
| CLERK | 1300 |
+---+--+

Now this query returns the average salary per job:

select job,avg(sal) from emp group by job -->

+---+---+
| job   | avg(sal)  |
+---+---+
| ANALYST   | 3000. |
| CLERK | 1037.5000 |
| MANAGER   | 2758. |
| PRESIDENT | 5000. |
| SALESMAN  | 1400. |
+---+---+

The final result should be:

+---+---+
| job   | avg(sal)  |
+---+---+
| CLERK | 1037.5000 |
+---+---+

In ORACLE I can do it like this:
select job,avg(sal) from emp group by job having avg(sal)=(select
min(avg(sal)) from emp group by job);

but this doesn't work in MYSQL. Does somebody know how it can be done in
MySQL???

Regards,
Edi



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Roger Baklund]

* Edouard Lauer 
> I would like to query the littlest average salary. I have a table with
> employees and their salary like that:
> 
> +---+--+
> | job   | sal  |
> +---+--+
> | CLERK |  800 |
> | SALESMAN  | 1600 |
> | SALESMAN  | 1250 |
> | MANAGER   | 2975 |
> | SALESMAN  | 1250 |
> | MANAGER   | 2850 |
> | MANAGER   | 2450 |
> | ANALYST   | 3000 |
> | PRESIDENT | 5000 |
> | SALESMAN  | 1500 |
> | CLERK | 1100 |
> | CLERK |  950 |
> | ANALYST   | 3000 |
> | CLERK | 1300 |
> +---+--+
> 
> Now this query returns the average salary per job:
> 
> select job,avg(sal) from emp group by job -->
> 
> +---+---+
> | job   | avg(sal)  |
> +---+---+
> | ANALYST   | 3000. |
> | CLERK | 1037.5000 |
> | MANAGER   | 2758. |
> | PRESIDENT | 5000. |
> | SALESMAN  | 1400. |
> +---+---+
> 
> The final result should be:
> 
> +---+---+
> | job   | avg(sal)  |
> +---+---+
> | CLERK | 1037.5000 |
> +---+---+
> 
> In ORACLE I can do it like this:
> select job,avg(sal) from emp group by job having avg(sal)=(select
> min(avg(sal)) from emp group by job);
> 
> but this doesn't work in MYSQL. Does somebody know how it can be done in
> MySQL???

Yes, you can add an alias, an ORDER BY clause and LIMIT 1 to your query:

SELECT job,AVG(sal) AS avg_sal 
  FROM emp 
  GROUP BY job 
  ORDER BY avg_sal
  LIMIT 1

http://www.mysql.com/doc/en/SELECT.html >

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem, Confused by Left Join.

[Cybot]

John Wards wrote:

I have this query:

SELECT *
FROM news_category
LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id
WHERE (
news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL
) 

Which gives me this output:

id  title perm show news_id cat_id
1  About Us  1  1NULL  NULL   
2  Learn About Your Hair  1  1  NULL  NULL   
3  Press Room  0  0  9  3   
4  Research News  0  0  9  4

Its Padding out with NULLs fine for the first 2 but missing out a few other 
records from news_category.

What I want the query to do is display all the news_categorys if they are 
mentioned in news_x_cat or not and if they don't have any data with in 
news_x_cat I need this bit padded out with NULLs.

Any ideas where I am going wrong?
yes, you use a WHERE

if you want all, dont use this WHERE!

with your WEHRE you get only this news_category which have a news with 
the id 9 or no news at all

--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem, Confused by Left Join.

[gerald_clark]

You have not shown us anything that would indicate that your output is 
not correct.
If you think something is missing you have to show us what is missing, 
and why you think
it should not be.

John Wards wrote:

I have this query:

SELECT *
FROM news_category
LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id
WHERE (
news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL
) 

Which gives me this output:

id  title perm show news_id cat_id
1  About Us  1  1NULL  NULL   
2  Learn About Your Hair  1  1  NULL  NULL   
3  Press Room  0  0  9  3   
4  Research News  0  0  9  4

Its Padding out with NULLs fine for the first 2 but missing out a few other 
records from news_category.

What I want the query to do is display all the news_categorys if they are 
mentioned in news_x_cat or not and if they don't have any data with in 
news_x_cat I need this bit padded out with NULLs.

Any ideas where I am going wrong?

Cheers
John Wards
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Nils Valentin]

OOh thats fine then isnt it ? ;-)

Best regards

Nils Valentin


2003年 6月 2日 月曜日 22:47、Sparky Kopetzky さんは書きました:
> No, not swearing - just As Soon As Financially Possible!!!
>
> Robin
>
> *** Still need an answer if someone can help *
>
>
> - Original Message -
> From: "Nils Valentin" <[EMAIL PROTECTED]>
> To: "Sparky Kopetzky" <[EMAIL PROTECTED]>; "My Sql List"
> <[EMAIL PROTECTED]>
> Sent: Sunday, June 01, 2003 19:52
> Subject: Re: Query problem
>
> > Hi everybody,
> >
> >
> > I hope ASAFP stands for something like AS Soon AS Feasable Possible ;-)
> >
> > We are not going to swear are we ?
> >
> > Best regards
> >
> > Nils Valentin
> >
> > 2003年 6月 2日 月曜日 07:30、Sparky Kopetzky さんは書きました:
> > > I have a view I need to create from several tables where I'm looking up
>
> one
>
> > > value from a table and need to add it to the Select stmt like this:
> > >
> > > SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id =
> > > bite.bite_animal_sex_id),
> > >type (SELECT type_meaning FROM type WHERE type_id =
> > > bite.bite_type_id), etc...
> > >
> > > However, I haven't found an example of how to do this and I need this
> > > ASAFP!!
> > >
> > > Apreciate any help.
> > >
> > > Robin E. Kopetzky
> > > Black Mesa Computers/Internet Services
> > > www.blackmesa-isp.net
> >
> > --
> > 
> > Valentin Nils
> > Internet Technology
> >
> >  E-Mail: [EMAIL PROTECTED]
> >  URL: http://www.knowd.co.jp
> > 

-- 

Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Sparky Kopetzky]

No, not swearing - just As Soon As Financially Possible!!!

Robin

*** Still need an answer if someone can help *


- Original Message -
From: "Nils Valentin" <[EMAIL PROTECTED]>
To: "Sparky Kopetzky" <[EMAIL PROTECTED]>; "My Sql List"
<[EMAIL PROTECTED]>
Sent: Sunday, June 01, 2003 19:52
Subject: Re: Query problem


> Hi everybody,
>
>
> I hope ASAFP stands for something like AS Soon AS Feasable Possible ;-)
>
> We are not going to swear are we ?
>
> Best regards
>
> Nils Valentin
>
> 2003年 6月 2日 月曜日 07:30、Sparky Kopetzky さんは書きました:
> > I have a view I need to create from several tables where I'm looking up
one
> > value from a table and need to add it to the Select stmt like this:
> >
> > SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id =
> > bite.bite_animal_sex_id),
> >type (SELECT type_meaning FROM type WHERE type_id =
> > bite.bite_type_id), etc...
> >
> > However, I haven't found an example of how to do this and I need this
> > ASAFP!!
> >
> > Apreciate any help.
> >
> > Robin E. Kopetzky
> > Black Mesa Computers/Internet Services
> > www.blackmesa-isp.net
>
> --
> 
> Valentin Nils
> Internet Technology
>
>  E-Mail: [EMAIL PROTECTED]
>  URL: http://www.knowd.co.jp
> 
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Nils Valentin]

Hi everybody,


I hope ASAFP stands for something like AS Soon AS Feasable Possible ;-)

We are not going to swear are we ?

Best regards

Nils Valentin

2003年 6月 2日 月曜日 07:30、Sparky Kopetzky さんは書きました:
> I have a view I need to create from several tables where I'm looking up one
> value from a table and need to add it to the Select stmt like this:
>
> SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id =
> bite.bite_animal_sex_id),
>type (SELECT type_meaning FROM type WHERE type_id =
> bite.bite_type_id), etc...
>
> However, I haven't found an example of how to do this and I need this
> ASAFP!!
>
> Apreciate any help.
>
> Robin E. Kopetzky
> Black Mesa Computers/Internet Services
> www.blackmesa-isp.net

-- 

Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem

[Gürhan Özen]

Hi,
Sounds like you will need to use subqueries which is available at MySQL
4.1 and above ..
See: http://www.mysql.com/doc/en/Nutshell_4.1_features.html
Hope this helps.
Gurhan

On Sun, 2003-06-01 at 18:30, Sparky Kopetzky wrote:
> I have a view I need to create from several tables where I'm looking up one
> value from a table and need to add it to the Select stmt like this:
> 
> SELECT sex (SELECT yes_no_meaning FROM yes_no WHERE yes_no_id =
> bite.bite_animal_sex_id),
>type (SELECT type_meaning FROM type WHERE type_id =
> bite.bite_type_id), etc...
> 
> However, I haven't found an example of how to do this and I need this
> ASAFP!!
> 
> Apreciate any help.
> 
> Robin E. Kopetzky
> Black Mesa Computers/Internet Services
> www.blackmesa-isp.net
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[Stefan Hinz, iConnect \(Berlin\)]

Jon,

> Where can I look to see the Warnings?

MySQL 4.1 will come with an enhanced error/warnings reporting system
where you can use SHOW ERRORS and SHOW WARNINGS (see
http://www.mysql.com/doc/en/SHOW_WARNINGS.html for details).

If you don't have 4.1 (as most of us) you can tell the server to log
warnings, too, by starting it with the --warnings option (or write
"warnings" without quotes in the mysqld section of your my.cnf / my.ini
and restart the server).

You can see warnings in the MySQL error file. Under Unix, this file is
called hostname.err (where "hostname" is the name of your machine),
under Windows it's mysql.err.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Jon Miller" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 20, 2003 12:49 PM
Subject: query problem


I ran a query as follows:
Insert Into db_test.tbl_dbaddr (Client, Contact, Addr, OfcPhone) SELECT
Client AS Client, Business AS Contact, Addr AS Addr, OfcPhone AS
OfcPhone From AddressBook.tbl_addr;

I got the following Records: 8989 Duplicates: 16 Warnings:394

Where can I look to see the Warnings?
Also the information in the Contact and Addr field is wrong.  How do I
delete the entire table.  I believe I cannot use Drop.  Using Select *
delete from tbl_dbaddr yields a syntax error.

Thanks

Jon L. Miller, MCNE, CNS
Director/Sr Systems Consultant
MMT Networks Pty Ltd
http://www.mmtnetworks.com.au

"I don't know the key to success, but the key to failure
 is trying to please everybody." -Bill Cosby





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: query problem

[Adriaan . Putter]

> -Original Message-
> From: Jon Miller [mailto:[EMAIL PROTECTED]]
> Sent: Monday, January 20, 2003 1:49 PM
> To: [EMAIL PROTECTED]
> Subject: query problem
> 
> 
> I ran a query as follows:
> Insert Into db_test.tbl_dbaddr (Client, Contact, Addr, 
> OfcPhone) SELECT Client AS Client, Business AS Contact, Addr 
> AS Addr, OfcPhone AS OfcPhone From AddressBook.tbl_addr;
> 
> I got the following Records: 8989 Duplicates: 16 Warnings:394
> 
> Where can I look to see the Warnings? 
> Also the information in the Contact and Addr field is wrong.  
> How do I delete the entire table.  I believe I cannot use 
> Drop.  Using Select * delete from tbl_dbaddr yields a syntax error.
> 

correct syntax for delete is:
delete from  

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: query problem

[Jamie Beu]

You cna fix this problem with the "HAVING" keyword in the WHERE clause.

HAVING is like WHERE, but on group-by functions, like COUNT(*).

Change the query to:
SELECT   id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
HAVING count(correct)=4

This should work (*although I have not tried it yet*).

Jamie Beu
Lockheed-Martin Information Systems
Software Engineer
CTIA
(407) 306-2484

"The true soldier fights not because he hates what is in
 front of him, but because he loves what is behind him."
G. K. Chesterton - Illustrated London News, 1/14/11


-Original Message-
From: Tom Emerson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 05, 2002 11:43 AM
To: [EMAIL PROTECTED]
Subject: RE: query problem


I am by no means an "SQL-expert", but I'll give this a shot... :)

> -Original Message-
> From: Richard Brenner [mailto:[EMAIL PROTECTED]]
> Subject: query problem
>
> I have two tables with the following structure:
>
> Users:
> | Field| Type
> | id   | int(10) unsigned
> | name | blob
[etc]
> +--+--
> answers:
> +--+-+
> | id   | int(10) unsigned|
> | userid   | int(10) unsigned|
> | question | int(10) unsigned|
> | correct  | set('true','false') |
> | date | date|
> +--+-+
> This is for a quiz. Every user has to answer 4 questions ...
> I want to print out all users, that
> have answered all 4 questions correct.
> Can I do this with one query?

I built a couple of tables similar to what you have [trimmed to the
"essentials"], populated with some data, and ran the following:

SELECT   id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id

which created:
++-++-++
| id | name| userid | correct | numcorrect |
++-++-++
|  1 | alfred  |  1 | true|  3 |
|  2 | bobby   |  2 | true|  4 |
|  3 | carol   |  3 | true|  2 |
|  4 | diane   |  4 | true|  3 |
|  5 | edward  |  5 | true|  3 |
|  6 | frank   |  6 | true|  3 |
|  7 | george  |  7 | true|  1 |
|  8 | harry   |  8 | true|  3 |
|  9 | larry   |  9 | true|  1 |
| 10 | mark| 10 | true|  3 |
| 11 | nancy   | 11 | true|  4 |
| 12 | oliver  | 12 | true|  3 |
| 13 | paul| 13 | true|  2 |
| 14 | quentin | 14 | true|  4 |
| 15 | ralph   | 15 | true|  3 |
| 16 | samuel  | 16 | true|  3 |
| 17 | thomas  | 17 | true|  1 |
| 18 | ursula  | 18 | true|  4 |
| 19 | victor  | 19 | true|  2 |
| 20 | walter  | 20 | true|  3 |
| 21 | xavier  | 21 | true|  4 |
| 22 | yvonne  | 22 | true|  3 |
| 23 | zack| 23 | true|  2 |
| 24 | igor| 24 | true|  3 |
| 25 | jack| 25 | true|  4 |
| 26 | kristen | 26 | true|  4 |
++-++-++

unfortunately, adding "and numcorrect=4" to the WHERE clause caused an error
(numcorrect undefined), but if this were put into a temporary table, it is
then trivial to

   SELECT * FROM results WHERE numcorrect=4;


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: query problem

[Tom Emerson]

I am by no means an "SQL-expert", but I'll give this a shot... :)

> -Original Message-
> From: Richard Brenner [mailto:[EMAIL PROTECTED]]
> Subject: query problem
>
> I have two tables with the following structure:
>
> Users:
> | Field| Type
> | id   | int(10) unsigned
> | name | blob
[etc]
> +--+--
> answers:
> +--+-+
> | id   | int(10) unsigned|
> | userid   | int(10) unsigned|
> | question | int(10) unsigned|
> | correct  | set('true','false') |
> | date | date|
> +--+-+
> This is for a quiz. Every user has to answer 4 questions ...
> I want to print out all users, that
> have answered all 4 questions correct.
> Can I do this with one query?

I built a couple of tables similar to what you have [trimmed to the
"essentials"], populated with some data, and ran the following:

SELECT   id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id

which created:
++-++-++
| id | name| userid | correct | numcorrect |
++-++-++
|  1 | alfred  |  1 | true|  3 |
|  2 | bobby   |  2 | true|  4 |
|  3 | carol   |  3 | true|  2 |
|  4 | diane   |  4 | true|  3 |
|  5 | edward  |  5 | true|  3 |
|  6 | frank   |  6 | true|  3 |
|  7 | george  |  7 | true|  1 |
|  8 | harry   |  8 | true|  3 |
|  9 | larry   |  9 | true|  1 |
| 10 | mark| 10 | true|  3 |
| 11 | nancy   | 11 | true|  4 |
| 12 | oliver  | 12 | true|  3 |
| 13 | paul| 13 | true|  2 |
| 14 | quentin | 14 | true|  4 |
| 15 | ralph   | 15 | true|  3 |
| 16 | samuel  | 16 | true|  3 |
| 17 | thomas  | 17 | true|  1 |
| 18 | ursula  | 18 | true|  4 |
| 19 | victor  | 19 | true|  2 |
| 20 | walter  | 20 | true|  3 |
| 21 | xavier  | 21 | true|  4 |
| 22 | yvonne  | 22 | true|  3 |
| 23 | zack| 23 | true|  2 |
| 24 | igor| 24 | true|  3 |
| 25 | jack| 25 | true|  4 |
| 26 | kristen | 26 | true|  4 |
++-++-++

unfortunately, adding "and numcorrect=4" to the WHERE clause caused an error
(numcorrect undefined), but if this were put into a temporary table, it is
then trivial to

   SELECT * FROM results WHERE numcorrect=4;


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem in Perl

[Ralf Narozny]

Hello!

Almar van Pel wrote:

>Hello,
>
>I was trying to create a simple perl program, where my domains where listed
>followed by there DNS records.
>But It loops once, and then ends with error DBD::mysql::st fetch failed:
>fetch() without execute() at test.cgi line 61.
>
>I thougt this was the easyest way to do so. But no.. Does anyone have any
>experience with these kind of sub-statements?
>
>$dbh = DBI->connect("DBI:mysql:database=$db_database;host=$db_host",
>$db_user, $db_pw) ||
>&db_error("Databaseverbinding niet gemaakt: $DBI::errstr");
>
>$sql = "select domain from bind_dns_header";
>
>   $sth = $dbh->prepare($sql)|| &error("Kan het statement niet voorbereiden:
>$dbh->errstr");
>   $sth->execute || &error("Fout bij het communiceren met de database:
>$DBI::errstr");
>$sth->bind_columns(\$domain);
>
>   while ($sth->fetch()) {   # line 61
>  
>

*look at the name of the statement handle above*

>   print "$domain with the following records \n";
>
>  $sql2 = "select dnsrecord_id from bind_dns_item where domain =
>'$domain'";
>
>
>   $sth = $dbh->prepare($sql2)|| &error("Kan het statement niet voorbereiden:
>$dbh->errstr");
>

Here is your problem! You overwrite the $sth which you want to use in 
the outer while. Use a different variable for the inner handle. In the 
inner while you already fetched all rows for sth returning to the next 
outer loop will fail.

>   $sth->execute || &error("Fout bij het communiceren met de database:
>$DBI::errstr");
>$sth->bind_columns(\$dnsrecord_id);
>   while ($sth->fetch()) {
>
>   print "Record: $dnsrecord_id \n";
>
>   }
>
>}
>
>   $sth->finish();
>  
>


Greetings
 Ralf

-- 
Ralf Narozny

Besuchen Sie uns auf der DMS-Expo. SAP, Dokumenten-
management oder das komplette Office ins Portal einbinden?
Wir zeigen es Ihnen - vom 3. bis 5.9. auf der Messe Essen
Halle 3, Stand 3255

SPLENDID Internet GmbH & Co KG
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem in Perl

[Martin Waite]

On Mon, 2002-09-02 at 11:37, Harald Fuchs wrote:
> In article <1030961610.8175.5.camel@pascal>,
> Martin Waite <[EMAIL PROTECTED]> writes:
> 
> > Hi,
> > You need to use two separate database and statement handles - you can 
> > only have one active query per handle.
> 
> > eg.
> 
> > $dbh1 = DBI->connect(...);
> > $dbh2 = DBI->connect(...);
> 
> > $sth1 = $dbh1->prepare( ... );
> > $sth1->execute(...);
> 
> > while ( $sth1->fetch() ) {
> > $sth2 = $dbh2->prepare( ... );
> > $sth2->execute(...);
> > while ( $sth2->fetch() ) {
> > }
> > }
> 
> Nope.  You can have multiple active statement handles per database handle.
> 

Harald is correct (- thanks), but you still need a separate statement
handle for the query inside the loop:

$dbh1 = DBI->connect(...);

$sth1 = $dbh1->prepare( ... );
$sth1->execute(...);

while ( $sth1->fetch() ) {
$sth2 = $dbh1->prepare( ... );
$sth2->execute(...);
while ( $sth2->fetch() ) {
}
}



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem in Perl

[Martin Waite]

Hi,

You need to use two separate database and statement handles - you can 
only have one active query per handle.

eg.

$dbh1 = DBI->connect(...);
$dbh2 = DBI->connect(...);

$sth1 = $dbh1->prepare( ... );
$sth1->execute(...);

while ( $sth1->fetch() ) {
$sth2 = $dbh2->prepare( ... );
$sth2->execute(...);
while ( $sth2->fetch() ) {
}
}

On Sat, 2002-08-31 at 20:23, Almar van Pel wrote:
> Hello,
> 
> I was trying to create a simple perl program, where my domains where listed
> followed by there DNS records.
> But It loops once, and then ends with error DBD::mysql::st fetch failed:
> fetch() without execute() at test.cgi line 61.
> 
> I thougt this was the easyest way to do so. But no.. Does anyone have any
> experience with these kind of sub-statements?
> 
> $dbh = DBI->connect("DBI:mysql:database=$db_database;host=$db_host",
> $db_user, $db_pw) ||
> &db_error("Databaseverbinding niet gemaakt: $DBI::errstr");
> 
> $sql = "select domain from bind_dns_header";
> 
>   $sth = $dbh->prepare($sql)|| &error("Kan het statement niet voorbereiden:
> $dbh->errstr");
>   $sth->execute || &error("Fout bij het communiceren met de database:
> $DBI::errstr");
> $sth->bind_columns(\$domain);
> 
>   while ($sth->fetch()) {   # line 61
> 
>   print "$domain with the following records \n";
> 
>   $sql2 = "select dnsrecord_id from bind_dns_item where domain =
> '$domain'";
> 
> 
>   $sth = $dbh->prepare($sql2)|| &error("Kan het statement niet voorbereiden:
> $dbh->errstr");
>   $sth->execute || &error("Fout bij het communiceren met de database:
> $DBI::errstr");
> $sth->bind_columns(\$dnsrecord_id);
>   while ($sth->fetch()) {
> 
>   print "Record: $dnsrecord_id \n";
> 
>   }
> 
> }
> 
>   $sth->finish();
> 
> Regards,
> 
> Almar
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem

[Francisco]

Change your query for:

select red,green,blue from colorchanger INNER JOIN
jobattributekit ON colorcode = value where
attributekit = "Color" and jobnumber =
"28200124RB4-001" and scenario="JN75K";

Also remember that MySQL does not allow sub-queries.


--- Amit Lonkar <[EMAIL PROTECTED]> wrote:
> Hi all
> Does mysql support simple joins?
> 
> I am trying to run the following query:-
> 
> select red,green,blue from colorchanger where
> colorcode = select value from jobattributekit where
> attributekit = "Color" and jobnumber =
> "28200124RB4-001" and scenario="JN75K";
> 
> ColorChanger Table:-
> ColorCode  ColorRedGreenBlue
> STBBLUE  58 110  165
> 
> JOBATTRIBUTEKIT Table:-
> Jobnumber AttributekitValueScenario
> 28200124RB4-001 Color  STB   JN75K 
> 
> 
> The error that it gives is :-
> ERROR 1064: You have an error in your SQL syntax
> near
> 'select value from jobattr
> ibutekit where attributekit = "Color" and jobnumber
> =
> "' at line 1
> 
> Thanks
> Amit
> 
> 
>   
> 
> 
> 
> 
> 
> __
> Do You Yahoo!?
> HotJobs - Search Thousands of New Jobs
> http://www.hotjobs.com
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 


__
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem/mysql

[Dan Nelson]

In the last episode (Aug 12), duo fu said:
> I have just created a small web site with php/mysql. I do a query
> into one of my tables whose ".MYI=338K " and ".MYD=7.6M". I could
> only retrieve some part of the data and then the browser just died
> there. My query is " select * from Forces". The primary key is
> combination of several fields. Table Force has 132 fields. Its size
> reaches 20 MB. How can I solve the problem? Will adding an index
> help? I am just intending to print out the whole table.

"The browser just died there"?  My guess is it crashed trying to
display the ~60MB html page required to display 20MB worth of data. 
How much memory do you have in the PC you're running the browser on?

-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query problem

[Roger Baklund]

* saraswathy saras
> i have a problem with query.I have no idea about it,whether it
> can be done
> or not.i want to query out name order by alphabet,The range is
> provided like
> Aa - Ad.
> so the result should be like this:-
>
> Browse Aa to Ac
>
> Name
> 
> A

...hm... 'A' is not in the range 'Aa' - 'Ad'.

> Aa
> aa
> Aabenraa

SELECT name FROM table WHERE name BETWEEN 'Aa' AND 'Ad';

BETWEEN is described in the manual:

http://www.mysql.com/doc/C/o/Comparison_Operators.html >

This can also be done using 'normal' operators:

SELECT name FROM table WHERE
  name >= 'Aa' AND
  name < 'Ad';

Note that BETWEEN is including, 'Ad' will be included, but not 'Ada'. This
can be solved by using something like 'Aczzz' or 'Ac~'.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem

[Diana Soares]

Forget everything i said, now i realize that i didn't understand the
problem very well.
Francisco Reinaldo is right, use LEFT JOIN.
Sorry for having replied that!

On Thu, 2002-07-25 at 20:40, Kevin wrote:
> Hello,
> 
> I have a query that runs perfectly until one of the items has no value or is
> set to 0:
> 
> SELECT
> item.*, color.Name AS COLOR,
> shapecode.Shape AS SHAPE,
> clarity.Name AS CLARITY
> FROM item, color, shapecode, clarity
> WHERE
> clarity.ID = item.CLARITY_ID
> AND shapecode.ID = item.SHAPE_ID
> AND item.COLOR_ID = color.ID
> AND ITEM_ID='MA603'
> 
> If the item.CLARITY_ID has no value or a value of 0 (which there is no
> defined value for in the clarity table), then the query fails.
> 
> Can anything be done without adding more login in the code?
> 
> Thanks
-- 
Diana Soares


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem

[Diana Soares]

Hi,

On Thu, 2002-07-25 at 20:40, Kevin wrote:
> Hello,
> 
> I have a query that runs perfectly until one of the items has no value or is
> set to 0:
> 
> SELECT
> item.*, color.Name AS COLOR,
> shapecode.Shape AS SHAPE,
> clarity.Name AS CLARITY
> FROM item, color, shapecode, clarity
> WHERE
> clarity.ID = item.CLARITY_ID
> AND shapecode.ID = item.SHAPE_ID
> AND item.COLOR_ID = color.ID
> AND ITEM_ID='MA603'
> 
> If the item.CLARITY_ID has no value or a value of 0 (which there is no
> defined value for in the clarity table), then the query fails.
> 
> Can anything be done without adding more login in the code?

Hi, 
I don't know if CLARITY_ID can be NUll, so this will be just a guess.
Try to use the "NULL safe equal" operator '<=>' between clarity.ID and
item.CLARITY_ID like 
...
WHERE
clarity.ID <=> item.CLARITY_ID
...

Info:
6.3.1.2 Comparison Operators
http://www.mysql.com/doc/C/o/Comparison_Operators.html

---



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem

[Francisco Reinaldo]

Hi,

Use LEFT JOIN instead of =. In a 1:m relationship,
only records that match in both tables are selected.
With L.J., the master is always selected even if the
record is not present in the child.

Master
Id
1
2
3
4
5

Children
FK_id Value
1 V1
1 V2
2 V3

Select id, value from Master, Children Where Master.Id
= Children.FK_id

1, V1
1, V2
2, V3

Select id, value from Master Left Join Children On
Master.Id = Children.FK_id

1, V1
1, V2
2, V3
4, NULL
5, NULL

This query is very useful to find records without
children

Select id, value from Master Left Join Children On
Master.Id = Children.FK_id Where value is null

Bye and Good Luck.



--- Kevin <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I have a query that runs perfectly until one of the
> items has no value or is
> set to 0:
> 
> SELECT
> item.*, color.Name AS COLOR,
> shapecode.Shape AS SHAPE,
> clarity.Name AS CLARITY
> FROM item, color, shapecode, clarity
> WHERE
> clarity.ID = item.CLARITY_ID
> AND shapecode.ID = item.SHAPE_ID
> AND item.COLOR_ID = color.ID
> AND ITEM_ID='MA603'
> 
> If the item.CLARITY_ID has no value or a value of 0
> (which there is no
> defined value for in the clarity table), then the
> query fails.
> 
> Can anything be done without adding more login in
> the code?
> 
> Thanks
> 
> --Kevin
> 
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 


__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem..

[Diana Soares]

Hi,
If you want the last date, isn't it just:

SELECT clientid,max(datedone) 
FROM table_name 
WHERE done = 'true' and x = '' 
GROUP BY clientid;  

? Hope it helps,

On Fri, 2002-07-26 at 08:27, Nixarlidis Aris wrote:
> Hi,
> I face the following situation.I have a number of clients-each with a 
> unique clientid.I have a table with some records -one after 
> the other in time order(datedone field) with actions checked as 'done' or 
> not 'done', 
> in the 'done' field which is either true or false.What I want to to, is to 
> specify in my query the last record-date that has an action marked as 
> done at the same time when the value x='' for every clientid.I have 
> tried what you see but didin't help.
> 
> SELECT clientid FROM table_name WHERE datedone = 'max(datedone)' and done 
> = 'true' and x = '' group by clientid;  
> 
> I am new to all this, so any help would be usefull
> Thanks

-- 
Diana Soares



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query problem

[Dillon, John]

Is it possible to do two temporary tables for authors and publishers and
then do a join on the title?

-Original Message-
From: Jed Verity [mailto:[EMAIL PROTECTED]]
Sent: 05 July 2002 21:01
To: '[EMAIL PROTECTED]'
Subject: Re: R: Query problem


I see. Sorry I misunderstood you. You're stuck with a similar problem here,
though. That is, you want to dynamically create columns (fields). Which can
be done, of course, as long as you have the privileges and you're willing to
divide up your query into at least 3. It sounds like you don't have
privileges, though, right?

In which case, you're stuck with the best possible scenario being:
+-+-+
| book| description |
+-+-+
| Title 1 | author  |
| Title 1 | publisher   |
| Title 2 | author  |
| Title 2 | publisher   |
+-+-+

And so on...

Needless to say, it's a shame that you don't have access to table
administration. Those publishers and authors should be in different tables!

Sorry I can't help more. Maybe someone with a bigger brain can help...
Jed

I liked it when Gigi Di Leo wrote this to me:

> I apologize to everybody, I wasn't very clear submitting my problem -
Italian
> -> English :-)
> 
> I try to give you an example:
> 
> table BOOKS
> +-+++
> | author_code | publisher_code | book_title |
> +-+++
> | 1   |   2| Title 01   |
> | 3   |   4| Title 02   |
> | 5   |   6| Title 03   |
> | 7   |   8| Title 04   |
> | 9   |  10| Title 05   |
> |11   |  12| Title 06   |
> +-+++
> 
> table BASIC_DATA (ordered by record_type): contains Authors and Publishers
> +--+---+-+
> | code | description   | record_type |
> +--+---+-+
> |  1   | gigi  |  1  |
> |  3   | andrea|  1  |
> |  5   | marcello  |  1  |
> |  7   | antonio   |  1  |
> |  9   | rosa  |  1  |
> | 11   | angelo|  1  |
> |  2   | mc graw hill  |  2  |
> |  4   | jsoft |  2  |
> |  6   | apogeo|  2  |
> |  8   | microsoft press   |  2  |
> | 10   | mondadori |  2  |
> | 12   | acme publisher|  2  |
> +--+---+-+
> 
> In BASIC_DATA records with record_type = 1 are about Authors, records with
> record_type = 2 are about Publishers.
> 
> The query I am looking for should return:
> 
> +-++--+
> | book_title  | author | publisher|
> +-++--+
> | Title 01| gigi   | mc graw hill |
> | Title 02| andrea | jsoft|
> | Title 03| marcello   | apogeo   |
> | Title 04| antonio| microsoft press  |
> | Title 05| rosa   | mondadori|
> | Title 06| angelo | acme publisher   |
> +-++--+
> 
> I hope this helps you to better understand my problem.
> If I could touch the database I would split BASIC_DATA into two different
> tables AUTHORS and PUBLISHERS. But I cannot touch the DB structure.
> 
> Gigi
> 
> -Messaggio originale-
> Da: Jed Verity [mailto:[EMAIL PROTECTED]]
> Inviato: venerdì 5 luglio 2002 20.16
> A: Gigi Di Leo; '[EMAIL PROTECTED]'
> Oggetto: Re: Query problem
> 
> 
> Hello, Gigi,
> 
> By "decoded", do you mean that you want the words "Author" and "Publisher"
> to appear in your table instead of "1" and "2"? And you can't create or
> modify tables? Short of replacing the codes in the columns, it seems to me
> that you'd need to have a decode table. Something like:
> 
> +--+---+
> | code | type  |
> +--+---+
> | 1| Author|
> +--+---+
> | 2| Publisher |
> +--+---+
> 
> Right? Without this, or without inserting the actual values, or without
> running conditionals in PHP or whatever environment you're accessing the
> data in, you might be stuck. Maybe someone else has an idea...
> 
> HTH,
> Jed
> 
> I liked it when Gigi Di Leo wrote this to me:
> 
>> Hello list.
&g

Re: Query problem

[Jed Verity]

Hello, Gigi,

By "decoded", do you mean that you want the words "Author" and "Publisher"
to appear in your table instead of "1" and "2"? And you can't create or
modify tables? Short of replacing the codes in the columns, it seems to me
that you'd need to have a decode table. Something like:

+--+---+
| code | type  |
+--+---+
| 1| Author|
+--+---+
| 2| Publisher |
+--+---+

Right? Without this, or without inserting the actual values, or without
running conditionals in PHP or whatever environment you're accessing the
data in, you might be stuck. Maybe someone else has an idea...

HTH,
Jed

I liked it when Gigi Di Leo wrote this to me:

> Hello list.
> 
> I have to interface a database, which I cannot modify because it is maintained
> somewher else, where there are two tables:
> 
> BOOKS
> author_code
> publisher_code
> book_title
> 
> BASIC_DATA
> code
> description
> record_type
> 
> In the table BASIC_DATA records are classified on the flag value:
> flag=1 -> record is about Authors
> flag=2 -> record is about Publishers
> 
> Could you please suggest me the best query syntax to get BOOKS' data with
> Authors and Publishers decoded ?
> 
> Thank you very much for your help.
> 
> Gigi Di Leo
> 
> 
> -
> Before posting, please check:
>  http://www.mysql.com/manual.php   (the manual)
>  http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query problem

[Claudio Carbone]

Now it has transformed into this:





");
 }
 else{
  print( "Non riesco a connettermi al server.");
  exit;
 }
 $user=$_POST['user'];
 $password=$_POST['password'];
 $query=(mysql_query("select * from admin where User='%$user%'",$link));
 $row = mysql_fetch_array($query);
 if($row["user"]==$user){
  $dati=mysql_query("select * from admin where (User='%$user%' AND
Ppassword='%$password%');",$link);
  $row2 = mysql_fetch_array($dati);
  if(($row2["user"]==$user)&&($row2["password"]==$password))
   echo"Autenticazione riuscita.Bentornato utente ".$user.".";
  else
   print("Autenticazione non riuscita.Password non corretta.");
 }
 else
  echo"Utente ".$user." inesistente.";
}
else{ ?>

Inserire UserId

Inserire Password







Now nothing is validated.
Everything is non valid.
I still can't understand what i miss
It seems something in the main queries... but what?
Claudio


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query problem with GROUP BY and ORDER BY

[Philip Spradling]

On Sat, 01 Jun 2002 11:58:38 +0200
Claire Forchheimer <[EMAIL PROTECTED]> wrote:

I know the answer to the first part at least.  I think you want to use two columns in 
the order by clause, and leave out the group by clause.  As in:

select * from tbl order by apt, name;

I'm afraid its too late at night for me to be able to think about the rest.  I'm sure 
it can be done though.

Philip


> Hi all,
> 
> I have a table including two colums: names and apartment numbers:
> 
> apt #| name
> --
> 1 | Smith Joe
> 1 | Smith Anne
> 2 | Doe Richard
> 3 | Svensen Mike
> 3 | Brant Liza
> 
> I need to get a list in alphabetical order, but with people in the same
> apt keept together:
> 
> Brant, Svensen
> Doe
> Smith, Smith
> 
> The difficulty is to get people living in the same apt grouped together.
> 
> SELECT * FROM tbl GROUP BY apt ORDER BY name  - will drop the second
> name
> found in each apt!
> 
> (The result would be this list:
> Brant
> Doe
> Smith)
> 
> I've been banging my head black and blue over this query! Can anyone
> help??!?
> 
> 
> Two more issues in addition to this query is:
> 
> I'm using a PHP statement to find only the lastname in the name field,
> can
> this be done already in the query??
> 
> A nice feature would be to only list one name if both lastnames are the
> same in one apt. In other words the list would then look like:
> 
> Brant, Svensen
> Doe
> Smith
> 
> can  this be done???


-- 
Philip Spradling
Unemployed C/C++/Java/SQL programmer
(Who do you know who could use an experienced, talented but overeducated programmer?)
[EMAIL PROTECTED]  
http:[EMAIL PROTECTED]/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem

[Denis Mcmahon]

[EMAIL PROTECTED] ("Daren Cotter") wrote:

>I am having major troubles creating this query...ok here's some
>background info:
>
>I have three tables: members, which contains info about the member, such
>as city, state, zip, marital status, etc; interests, which stores just
>an interest_id and name; and member_interests, which stores just
>member_id and interest_id.
>
>What I need to do is create a query that selects certain members from
>the DB depending on certain demographics. For example, the query might
>need to select all members from the DB where their country is USA,
>Marital Status is Single, and have "Golf" selected as an interest. The
>first two are no problem...but, is there any way to create ONE query
>that would validate all the info stored in members table, as well as the
>member_interests table? And if not, can someone provide a sample
>solution method?

Yes, double inner join .

Assuming people is the first table, links sits in the middle, and
categories is a list of interests ..

SELECT People.FirstName, People.LastName, People.MaritalStatus,
People.MobilePhone, Categories.CategoryName
FROM (People INNER JOIN links ON People.AddressID = links.persid)
INNER JOIN Categories ON links.catid = Categories.CategoryID;

Rgds
Denis
-- 
Denis McMahon / +44 7802 468949 / [EMAIL PROTECTED]
sulfnbk is not a virus, see the symantec virus encyclopaedia!
Now restocking killfile, new entrants welcome: trolls, spam, 
xpost cascades, OT ads, top posters & terminally clueless!

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query Problem

[Jason Soza]

Never mind... Spotted this after I sent the post!

This:
SELECT dates.date, tracks.track, albums.album, artists.artist
FROM dates, tracks, albums, artists, albums_tracks, tracks_artists,
ex_playlist_tb
WHERE example_playlist.id = dates.id
AND example_playlist.track_id = tracks.id
AND tracks.id = albums_tracks.track_id
AND albums_tracks.track_id = tracks_artists.artist_id
AND albums.id = albums_tracks.album_id
AND artists.id = tracks_artists.artist_id
ORDER BY date

Should have 'AND albums_tracks.track_id = tracks_artists.track_id' as the
third AND instead of what's there now. Ooops! It works now, though! Yay!
This is pretty cool. I'll have to let you know when I finish all of this -
I'll be using PHP to display all this info via HTML. That'll be another fun
project...

Thanks again for your help!

Jason

-Original Message-
From: Jason Soza [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 10:31 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Query Problem


Okay, I think I'm -almost- there:
I have these tables:
tracks
albums
artists

Each with an id MEDIUMINT NOT NULL PRIMARY KEY and a track, album, or artist
column.

Then I have 2 linking tables, albums<=>tracks, tracks<=>artists, these are:
albums_tracks
tracks_artists

Each of these has all MEDIUMINT NOT NULL columns for album_id, track_id,
artist_id, and an id MEDIUMINT NOT NULL PRIMARY KEY in the center. So
albums_tracks has:
album_id   id   track_id

I created a dates table, this contains 2 columns, id MEDIUMINT NOT NULL
PRIMARY KEY, and dates DATE. This has every date I've ever played a show in
it, with a unique id for each date.

Then I created a 'sample' playlist file, just trying to figure out how to do
it. So I made it with 2 columns, id and track_id, both MEDIUMINT NOT NULL
PRIMARY KEY. I figured the first id column could be matched with date.id to
get the date of the show, and the track_id could be matched back to
tracks.id and so on to get the rest of the data. So the data looks something
like:
id track_id
1  45
1  275
1  24
2  35
2  611
2  326
...

The first stab at a query to create a playlist ended up with 27,000 returns.
That ain't right! So I used this query:

SELECT dates.date, tracks.track, albums.album, artists.artist
FROM dates, tracks, albums, artists, albums_tracks, tracks_artists,
ex_playlist_tb
WHERE example_playlist.id = dates.id
AND example_playlist.track_id = tracks.id
AND tracks.id = albums_tracks.track_id
AND albums_tracks.track_id = tracks_artists.artist_id
AND albums.id = albums_tracks.album_id
AND artists.id = tracks_artists.artist_id
ORDER BY date

And got 90 returns, exactly double the amount of records in the dates table.
The dates match the first track, but the first track repeats for all
occurences of the date. The album is correctly matched with all occurrences
of the track, but the artist is not correct.

Is there something simple in my query I'm missing?

Thanks for your help on this.

Jason

-Original Message-
From: Opus [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 9:34 PM
To: Jason Soza; [EMAIL PROTECTED]
Subject: RE: Query Problem


Other than it is uglier than a Hut?

The higest ideals of a database is to separate data into tables that keep
track
of data for the same objects.  Some databases break these rules (oh and how)
for simpler queries, and sometimes for faster queries.

It is actually better to break the relationship info into separate tables.
Makes it easier to track at some point.

Something to add to the Albums_Tracks table would be index.   IE
Album_ID, Index, Track_ID
1, 1, 1
1, 2, 5
1, 3, 4
With this, you can keep trock of which order the tracks appear on albums.

As for the Playlists.  This is simular to album data.  IE, an ordered
collection of tracks with a title, and a date.

So, expanding Albums (possible rename to Collections) we have:
Collection_ID, Title, Date, Play_List
1, Oxygene, 1976 (use any date here, the year is normally all you get), N
2, Club Six Groove, 6/23/2002, Y

Note that you can put any date in for the 'album' and ignore everything
other
than the year, or you can research that data.

Now, when you create a new album or playlist (collection of tarcks) you
enter a
new row in the Collections table, and build the Albums_Tracks
(Collections_Tracks) table.

Advantages here.  With a specific Track (say #50), we can find out what
collections it was used for in the following way:
SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT
WHERE C.Collection_ID = CT.Collection_ID
AND CT.Track_ID = 50;

You could also find when it was played (include the Date, and filter for
Play_List), when it appeared in the performance, etc.

To recreate the playlist, you would sort on the index.

If you create new tables for each playlist, then you can't do any data
crunching, or comparisons to see what songs you use most,

RE: Query Problem

[Jason Soza]

Okay, I think I'm -almost- there:
I have these tables:
tracks
albums
artists

Each with an id MEDIUMINT NOT NULL PRIMARY KEY and a track, album, or artist
column.

Then I have 2 linking tables, albums<=>tracks, tracks<=>artists, these are:
albums_tracks
tracks_artists

Each of these has all MEDIUMINT NOT NULL columns for album_id, track_id,
artist_id, and an id MEDIUMINT NOT NULL PRIMARY KEY in the center. So
albums_tracks has:
album_id   id   track_id

I created a dates table, this contains 2 columns, id MEDIUMINT NOT NULL
PRIMARY KEY, and dates DATE. This has every date I've ever played a show in
it, with a unique id for each date.

Then I created a 'sample' playlist file, just trying to figure out how to do
it. So I made it with 2 columns, id and track_id, both MEDIUMINT NOT NULL
PRIMARY KEY. I figured the first id column could be matched with date.id to
get the date of the show, and the track_id could be matched back to
tracks.id and so on to get the rest of the data. So the data looks something
like:
id track_id
1  45
1  275
1  24
2  35
2  611
2  326
...

The first stab at a query to create a playlist ended up with 27,000 returns.
That ain't right! So I used this query:

SELECT dates.date, tracks.track, albums.album, artists.artist
FROM dates, tracks, albums, artists, albums_tracks, tracks_artists,
ex_playlist_tb
WHERE example_playlist.id = dates.id
AND example_playlist.track_id = tracks.id
AND tracks.id = albums_tracks.track_id
AND albums_tracks.track_id = tracks_artists.artist_id
AND albums.id = albums_tracks.album_id
AND artists.id = tracks_artists.artist_id
ORDER BY date

And got 90 returns, exactly double the amount of records in the dates table.
The dates match the first track, but the first track repeats for all
occurences of the date. The album is correctly matched with all occurrences
of the track, but the artist is not correct.

Is there something simple in my query I'm missing?

Thanks for your help on this.

Jason

-Original Message-
From: Opus [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 9:34 PM
To: Jason Soza; [EMAIL PROTECTED]
Subject: RE: Query Problem


Other than it is uglier than a Hut?

The higest ideals of a database is to separate data into tables that keep
track
of data for the same objects.  Some databases break these rules (oh and how)
for simpler queries, and sometimes for faster queries.

It is actually better to break the relationship info into separate tables.
Makes it easier to track at some point.

Something to add to the Albums_Tracks table would be index.   IE
Album_ID, Index, Track_ID
1, 1, 1
1, 2, 5
1, 3, 4
With this, you can keep trock of which order the tracks appear on albums.

As for the Playlists.  This is simular to album data.  IE, an ordered
collection of tracks with a title, and a date.

So, expanding Albums (possible rename to Collections) we have:
Collection_ID, Title, Date, Play_List
1, Oxygene, 1976 (use any date here, the year is normally all you get), N
2, Club Six Groove, 6/23/2002, Y

Note that you can put any date in for the 'album' and ignore everything
other
than the year, or you can research that data.

Now, when you create a new album or playlist (collection of tarcks) you
enter a
new row in the Collections table, and build the Albums_Tracks
(Collections_Tracks) table.

Advantages here.  With a specific Track (say #50), we can find out what
collections it was used for in the following way:
SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT
WHERE C.Collection_ID = CT.Collection_ID
AND CT.Track_ID = 50;

You could also find when it was played (include the Date, and filter for
Play_List), when it appeared in the performance, etc.

To recreate the playlist, you would sort on the index.

If you create new tables for each playlist, then you can't do any data
crunching, or comparisons to see what songs you use most, or least, or
paterns,
or what ever...

As a DJ, you might want to spend the extra time figuring out BPM for each
track, and adding that to the track data, then you can get the computer help
you look for good songs to tie together

You know, you could do all of this via a text file Of course a spread
sheet
app would help you copy and paste, and keep things neat.  Flat tables will
rule
the WORLD!!!  Oh wait, that was FrameWork will rule the world. NOT!


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query Problem

[Opus]

Other than it is uglier than a Hut?

The higest ideals of a database is to separate data into tables that keep track 
of data for the same objects.  Some databases break these rules (oh and how) 
for simpler queries, and sometimes for faster queries.

It is actually better to break the relationship info into separate tables.  
Makes it easier to track at some point.  

Something to add to the Albums_Tracks table would be index.   IE
Album_ID, Index, Track_ID
1, 1, 1
1, 2, 5
1, 3, 4
With this, you can keep trock of which order the tracks appear on albums.

As for the Playlists.  This is simular to album data.  IE, an ordered 
collection of tracks with a title, and a date.

So, expanding Albums (possible rename to Collections) we have:
Collection_ID, Title, Date, Play_List
1, Oxygene, 1976 (use any date here, the year is normally all you get), N
2, Club Six Groove, 6/23/2002, Y

Note that you can put any date in for the 'album' and ignore everything other 
than the year, or you can research that data.

Now, when you create a new album or playlist (collection of tarcks) you enter a 
new row in the Collections table, and build the Albums_Tracks 
(Collections_Tracks) table.  

Advantages here.  With a specific Track (say #50), we can find out what 
collections it was used for in the following way:
SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT
WHERE C.Collection_ID = CT.Collection_ID
AND CT.Track_ID = 50;

You could also find when it was played (include the Date, and filter for 
Play_List), when it appeared in the performance, etc.

To recreate the playlist, you would sort on the index.

If you create new tables for each playlist, then you can't do any data 
crunching, or comparisons to see what songs you use most, or least, or paterns, 
or what ever...

As a DJ, you might want to spend the extra time figuring out BPM for each 
track, and adding that to the track data, then you can get the computer help 
you look for good songs to tie together

You know, you could do all of this via a text file Of course a spread sheet 
app would help you copy and paste, and keep things neat.  Flat tables will rule 
the WORLD!!!  Oh wait, that was FrameWork will rule the world. NOT!



On 23 May 2002 at 17:28, Jason Soza wrote:

> Thanks for the tips! I'll definitely try that out. I've already 
> finished the arduous task of entering all of the playlist data, I have 
> all of the data in a readily sortable form, so trying out different 
> stuff doesn't involve much more than creating tables and writing my 
> queries.
> 
> Let me throw this idea out and see what you think. I was at work all 
> day trying to think of better ways to do all this...
> 
> What if I have one 'master' table, containing the following:
> master_idtrack_idalbum_idartist_id
> 
> The first column is the index for this table. The next 3 columns 
> contain complete track/album/artist links, i.e. the id's of each from 
> their respective tables. So now instead of my 3 'linking' tables, I 
> have one 'master' table. By querying the master_id, I get a complete 
> track/album/artist return. Of course, I could vary the query statement 
> to get only tracks/albums, albums/artists, and artists/tracks.
> 
> The ultimate goal here is for me to be able to assemble complete 
> playlists for different shows I've DJed. Using the above setup, I could 
> have one master_playlist table, contains 1 column, the date of every 
> show.
> 
> Everytime I do a new show (or create old ones) I simply create a 
> new 'playlist' table named after the date it aired. This table would 
> contain an auto_increment column to show the order and number of songs 
> played, and it would also contain a column referencing the 
> master.master_id column. When I queried the playlist table for a 
> certain date a show aired, or a certain period of time even, it would 
> then reference the correct playlist tables, which then reference back 
> to the master table.
> 
> Are there any inherent flaws with this design that you guys can point 
> out for me? Or is this actually a little more efficient than the 
> current way?
> 
> Jason Soza
> 
> - Original Message -
> From: "Opus" <[EMAIL PROTECTED]>
> Date: Thursday, May 23, 2002 4:50 pm
> Subject: RE: Query Problem
> 
> > Jason,
> > 
> > First, as to the structure of the database.  I agree with Sammy, 
> > you probably 
> > don't need 3 tables linking data together.  A more generic 
> > structure would have 
> > only 2 relationship tables connecting as such: albums <=> tracks 
> > <=> artists.
> > In this fashion, you can determine who the artist is for each 
> > album by looking 
> > at wh

RE: Query Problem

[Jason Soza]

Thanks for the tips! I'll definitely try that out. I've already 
finished the arduous task of entering all of the playlist data, I have 
all of the data in a readily sortable form, so trying out different 
stuff doesn't involve much more than creating tables and writing my 
queries.

Let me throw this idea out and see what you think. I was at work all 
day trying to think of better ways to do all this...

What if I have one 'master' table, containing the following:
master_idtrack_idalbum_idartist_id

The first column is the index for this table. The next 3 columns 
contain complete track/album/artist links, i.e. the id's of each from 
their respective tables. So now instead of my 3 'linking' tables, I 
have one 'master' table. By querying the master_id, I get a complete 
track/album/artist return. Of course, I could vary the query statement 
to get only tracks/albums, albums/artists, and artists/tracks.

The ultimate goal here is for me to be able to assemble complete 
playlists for different shows I've DJed. Using the above setup, I could 
have one master_playlist table, contains 1 column, the date of every 
show.

Everytime I do a new show (or create old ones) I simply create a 
new 'playlist' table named after the date it aired. This table would 
contain an auto_increment column to show the order and number of songs 
played, and it would also contain a column referencing the 
master.master_id column. When I queried the playlist table for a 
certain date a show aired, or a certain period of time even, it would 
then reference the correct playlist tables, which then reference back 
to the master table.

Are there any inherent flaws with this design that you guys can point 
out for me? Or is this actually a little more efficient than the 
current way?

Jason Soza

- Original Message -
From: "Opus" <[EMAIL PROTECTED]>
Date: Thursday, May 23, 2002 4:50 pm
Subject: RE: Query Problem

> Jason,
> 
> First, as to the structure of the database.  I agree with Sammy, 
> you probably 
> don't need 3 tables linking data together.  A more generic 
> structure would have 
> only 2 relationship tables connecting as such: albums <=> tracks 
> <=> artists.
> In this fashion, you can determine who the artist is for each 
> album by looking 
> at who the artist is for the tracks on the album.  This also 
> allows for albums 
> that are compilations or ones that you burn yourself.  There is 
> one more level 
> of abstraction that could be done, but that is a different topic.
> 
> The structure looks good other wise.  You might want to look at 
> indexing both 
> those columns as well.  That will help some speed issues at some 
> point.
> Second, it is poor practice to rely on/reference internal IDs with 
> an external 
> source.  Since the IDs are AUTOINCREMENT you never do know the 
> exact number it 
> will get.  Yes, you can try to control this via sorting your data 
> before you 
> load it, but if you change the data before you load it, your IDs 
> will all 
> change.  Think of these numbers as data that only your database 
> need know.  
> Other than using them to tie tables together, you should ignore them.
> 
> I.E. If you have the artist list '3 Doors Down, etc.' and you add 
> '10,000 
> Maniacs' to the begining of your data file to load, then '3 Doors 
> Down' goes 
> from ID 1 to ID 2. This will mangle all of your data sets for any 
> reference 
> artist, and possibly others.
> 
> This leaves the question of how to do this.  Loading the data 
> tables (albums, 
> tracks, artists) is straight forward.  You have that.  Describing 
> the 
> relationships is a bit different.  My suggestion would be to write 
> some code 
> someplace that takes a text file of the relationships and inserts 
> the data into 
> the relationship table based on the data in the database.
> 
> I.E. Build files that look like:
> ArtistTrack
> 3 Doors Down  Superman
> and
> Album Track
> 
> The code will look up the Artist, and the Track and insert the IDs 
> for both 
> into Artists_Tracks.
> 
> Honestly, with a bit more work, you really don't need to load the 
> data tables 
> directly, but indirectly as a result of loading the relationships. 
> If you get 
> a Null value while searching for an artist, then you need to add 
> the artist, 
> and then get the ID again.  Now you can populate a database of 5 
> tables with 2 
> files.
> 
> Good luck!


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query Problem

[Opus]

from the first artist shows in the tracks column and it
> shows for all artists (no other songs by the different artists appear). Am I
> just missing something in this query?
> 
> Thanks for your help,
> Jason
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sammy Lau
> Sent: Wednesday, May 22, 2002 8:18 PM
> To: Jason Soza
> Cc: [EMAIL PROTECTED]
> Subject: Re: Query Problem
> 
> 
> to make it easier for others to help you. you should have posted the
> create table statement, insert statement for a small set of data so that
> anyone of us could easier reproduce your problem.
> 
> btw, are you sure you need 3 links between those 3 tables. please
> correct me if i'm wrong. i think 2 links should be enough. ie. artist
> <-> album <-> tracks.
> 
> 
> Jason Soza wrote:
> >
> > I'm trying to compile 75 playlists of 25+ songs each into a database
> > sortable by date the show was produced, track, album, and artist. I got
> > advice from someone as to how to layout the table structure, and basically
> > it was said that I should have a basic structure of 6 tables, one for
> > artists, one for albums, and one for tracks. These tables should contain
> > whatever info I want, i.e. song title, albums, etc., and a unique id for
> > each. I should have 3 more tables, each linking the unique id's of each
> > other table together.
> >
> > So, I have 3 tables as was suggested, one for songs, one for albums, one
> for
> > artists. Each has two columns, one being whatever information I'm
> entering,
> > and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column.
> >
> > I have 3 other tables containing two MEDIUMINT columns, these link each of
> > the 3 others. I have:
> > artists_tracks
> > tracks_albums
> > albums_artists
> >
> > Each is setup so that the records represent 'links' - for example,
> > artists_tracks has artist_id and track_id columns. Each record is made up
> of
> > an artist_id and a matching track_id.
> >
> > My problem is, when querying some records are lost. But only on a certain
> > query. For instance:
> > SELECT tracks.tracks, artists.artists
> > FROM tracks, artists, artists_tracks
> > WHERE tracks.id = artists_tracks.track_id AND artists.id =
> > artist_tracks.tracks_id
> >
> > This works. It returns 614 records, each track mated with it's relative
> > artist. This same query works using albums and the tracks_albums table,
> > returning 614 records as well. Matching up albums to artists returns 413
> > records, which is the correct number.
> >
> > However, the following query returns 451 instead of the expected 614 and I
> > can find no distinguishable pattern as to the records dropped:
> > SELECT tracks.tracks, albums.albums, artists.artists
> > FROM tracks, albums, artists, tracks_albums, albums_artists,
> artists_tracks
> > WHERE tracks.id = tracks_albums.track_id AND albums.id =
> > tracks_albums.album_id
> > AND albums.id = albums_artists.album_id  AND artists.id =
> > albums_artists.artist_id
> > AND artists.id = artists_tracks.artist_id  AND tracks.id =
> > artists_tracks.track_id
> >
> > I've tried LEFT JOIN as well, and have tried numerous other suggestions
> out
> > of the manual, but everything turns up 451 records. I haven't been able to
> > track down WHY records are missing - it seems almost random. If I sort by
> > album, sometimes all songs from an album have been listed, sometimes only
> 3
> > of 5 or 7 of 10 are missing.
> >
> > Any help would be greatly appreciated. Thanks in advance.
> >
> 
> 


--Opus--

We never forbid where we do not also have the power to prevent.
- Orson Scott Card (Xenocide)


Get added to my Humor list:
mailto:[EMAIL PROTECTED]?subject=ADD_HUMOR
Get added to my Neat list:
mailto:[EMAIL PROTECTED]?subject=ADD_NEAT
Get my PGP public key:
mailto:[EMAIL PROTECTED]?subject=PSEND&body=send%20PublicKEY.asc
Visit My Home Page:
http://value.net/~opus/




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem

[Jason Soza]

I'm wondering if anyone has a response for the query question I posted 
last night regarding my Songs/Albums/Artists database and related 
linking tables.

I'm new to this list, so I'm not sure what kind of response time to 
expect. I'm still unable to solve this problem on my own, and searches 
of the manual are not turning up much.

Thanks,

Jason Soza


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query Problem

[Jason Soza]

I apologize for not including that in my original post. Here's my create
table statement:
For albums, artists, tracks:
CREATE TABLE artists (artists VARCHAR(50), id MEDIUMINT NOT NULL
AUTO_INCREMENT PRIMARY KEY)

For albums_artists, artists_tracks, tracks_albums:
CREATE TABLE albums_artists (album_id MEDIUMINT NOT NULL, artist_id
MEDIUMINT NOT NULL)

Insert statement:
LOAD DATA LOCAL INFILE "albumsartists.txt" INTO TABLE albums_artists

For tracks, albums, and songs, their .txt files are simply a one column
list. The other column is auto_increment. So a sample from tracks:
11am
1979
3 Libras
32 Flavors
4 Degrees
46 & 2
99 Red Balloons
A Place For My Head
A Place In The Sun
Abracadabra

>From albums:
311
14:59
8
(unknown)<---generic, unknown album. really a record.
13 Ways To Bleed Onstage
24/7
40oz To Freedom
A Boy Named Goo
A Few Small Repairs
A Little South Of Sanity

>From artists:
311
10,000 Maniacs
3 Doors Down
311
A Perfect Circle
AC/DC
Adema
Aerosmith
Afghan Whigs
AK1200

For albumsartists.txt, tracksalbums.txt, and artiststracks.txt, they're a
two-column, tab delimited text file. Examples from albumsartists.txt:
1   1
2   258
3   35
4   14
4   76
4   167
4   230
5   53
6   223
7   256

>From tracksalbums.txt:
1   237
2   221
3   222
4   41
5   354
6   16
7   315
8   176
9   12
10  2

>From artiststracks.txt:
1   15
1   19
1   250
2   40
3   38
3   138
3   281
3   303
3   363
5   3
5   62

I thought 2 links would be enough as well, but I tried this query using just
the tracks_albums and albums_artists table and couldn't get it working. For
example, using this:
SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums,
artists, tracks_albums, albums_artists
WHERE tracks.id = tracks_albums.track_id
AND albums.id = tracks_albums.album_id
AND albums.id = albums_artists.album_id
AND artists.id = albums_artists.artist_id

I get 718 returns instead of 614. If more than one artist is linked to one
album, only the song from the first artist shows in the tracks column and it
shows for all artists (no other songs by the different artists appear). Am I
just missing something in this query?

Thanks for your help,
Jason

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sammy Lau
Sent: Wednesday, May 22, 2002 8:18 PM
To: Jason Soza
Cc: [EMAIL PROTECTED]
Subject: Re: Query Problem


to make it easier for others to help you. you should have posted the
create table statement, insert statement for a small set of data so that
anyone of us could easier reproduce your problem.

btw, are you sure you need 3 links between those 3 tables. please
correct me if i'm wrong. i think 2 links should be enough. ie. artist
<-> album <-> tracks.


Jason Soza wrote:
>
> I'm trying to compile 75 playlists of 25+ songs each into a database
> sortable by date the show was produced, track, album, and artist. I got
> advice from someone as to how to layout the table structure, and basically
> it was said that I should have a basic structure of 6 tables, one for
> artists, one for albums, and one for tracks. These tables should contain
> whatever info I want, i.e. song title, albums, etc., and a unique id for
> each. I should have 3 more tables, each linking the unique id's of each
> other table together.
>
> So, I have 3 tables as was suggested, one for songs, one for albums, one
for
> artists. Each has two columns, one being whatever information I'm
entering,
> and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column.
>
> I have 3 other tables containing two MEDIUMINT columns, these link each of
> the 3 others. I have:
> artists_tracks
> tracks_albums
> albums_artists
>
> Each is setup so that the records represent 'links' - for example,
> artists_tracks has artist_id and track_id columns. Each record is made up
of
> an artist_id and a matching track_id.
>
> My problem is, when querying some records are lost. But only on a certain
> query. For instance:
> SELECT tracks.tracks, artists.artists
> FROM tracks, artists, artists_tracks
> WHERE tracks.id = artists_tracks.track_id AND artists.id =
> artist_tracks.tracks_id
>
> This works. It returns 614 records, each track mated with it's relative
> artist. This same query works using albums and the tracks_albums table,
> returning 614 records as well. Matching up albums to artists returns 413
> records, which is the correct number.
>
> However, the following query returns 451 instead of the expected 614 and I
> can find no distinguishable pattern as to the records dropped:
> SELECT tracks.tracks, albums.albums, artists.artists
> FROM tracks, albums, artists, tracks_albums, albums_

Re: Query Problem

[Sammy Lau]

to make it easier for others to help you. you should have posted the
create table statement, insert statement for a small set of data so that
anyone of us could easier reproduce your problem.

btw, are you sure you need 3 links between those 3 tables. please
correct me if i'm wrong. i think 2 links should be enough. ie. artist
<-> album <-> tracks.


Jason Soza wrote:
> 
> I'm trying to compile 75 playlists of 25+ songs each into a database
> sortable by date the show was produced, track, album, and artist. I got
> advice from someone as to how to layout the table structure, and basically
> it was said that I should have a basic structure of 6 tables, one for
> artists, one for albums, and one for tracks. These tables should contain
> whatever info I want, i.e. song title, albums, etc., and a unique id for
> each. I should have 3 more tables, each linking the unique id's of each
> other table together.
> 
> So, I have 3 tables as was suggested, one for songs, one for albums, one for
> artists. Each has two columns, one being whatever information I'm entering,
> and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column.
> 
> I have 3 other tables containing two MEDIUMINT columns, these link each of
> the 3 others. I have:
> artists_tracks
> tracks_albums
> albums_artists
> 
> Each is setup so that the records represent 'links' - for example,
> artists_tracks has artist_id and track_id columns. Each record is made up of
> an artist_id and a matching track_id.
> 
> My problem is, when querying some records are lost. But only on a certain
> query. For instance:
> SELECT tracks.tracks, artists.artists
> FROM tracks, artists, artists_tracks
> WHERE tracks.id = artists_tracks.track_id AND artists.id =
> artist_tracks.tracks_id
> 
> This works. It returns 614 records, each track mated with it's relative
> artist. This same query works using albums and the tracks_albums table,
> returning 614 records as well. Matching up albums to artists returns 413
> records, which is the correct number.
> 
> However, the following query returns 451 instead of the expected 614 and I
> can find no distinguishable pattern as to the records dropped:
> SELECT tracks.tracks, albums.albums, artists.artists
> FROM tracks, albums, artists, tracks_albums, albums_artists, artists_tracks
> WHERE tracks.id = tracks_albums.track_id AND albums.id =
> tracks_albums.album_id
> AND albums.id = albums_artists.album_id  AND artists.id =
> albums_artists.artist_id
> AND artists.id = artists_tracks.artist_id  AND tracks.id =
> artists_tracks.track_id
> 
> I've tried LEFT JOIN as well, and have tried numerous other suggestions out
> of the manual, but everything turns up 451 records. I haven't been able to
> track down WHY records are missing - it seems almost random. If I sort by
> album, sometimes all songs from an album have been listed, sometimes only 3
> of 5 or 7 of 10 are missing.
> 
> Any help would be greatly appreciated. Thanks in advance.
> 
> Jason Soza
> 
> Jason Soza
> '92 Twin Classic Red Miatae
> Juneau, Alaska
> http://www.phrog-net.com/sparkles/
> http://www.miatapix.net <--Is YOUR Miata here?
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Sammy Lau
mailto: [EMAIL PROTECTED]
- Tell me what you want and I'll tell you how you can live without it.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query problem

[Anvar Hussain K.M.]

Hi,
This might work for you:

select @tempvar := max(datecolumn) from tablename group by datecolumn order 
by datecolumn desc limit 3;
select * from tablename where datecol >= @tempvar order by datecolumn desc;

Anvar.

At 06:12 AM 02/05/2002 +, you wrote:
>hi everyboby,
>
>How to select latest 3 days records  from the table  according to the 
>latests date.
>The data is like this:-
>
>name  date
>a 02-03-01
>b 02-03-15
>c 02-03-20
>d 02-03-20
>e 02-04-28
>f 02-04-28
>g 02-04-30
>
>The result should be like this:-
>
>name  date
>g 02-04-30
>f 02-04-28
>e 02-04-28
>c 02-03-20
>d 02-03-20
>
>
>Is it possible.Please help me.
>
>query,database,sql
>
>Thanks in advance.
>
>
>
>
>
>
>_
>Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
>
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php  (the manual)
>   http://lists.mysql.com/  (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query problem

[Jan Peuker]

Uhm, 3?
Didn't you wrote 5?

If you want just 3 do this: SELECT name,date FROM dates ORDER BY date LIMIT
0,3

regars,

Jan

- Original Message -
From: "saraswathy saras" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 02, 2002 8:12 AM
Subject: query problem


> hi everyboby,
>
> How to select latest 3 days records  from the table  according to the
> latests date.
> The data is like this:-
>
> name  date
> a 02-03-01
> b 02-03-15
> c 02-03-20
> d 02-03-20
> e 02-04-28
> f 02-04-28
> g 02-04-30
>
> The result should be like this:-
>
> name  date
> g 02-04-30
> f 02-04-28
> e 02-04-28
> c 02-03-20
> d 02-03-20
>
>
> Is it possible.Please help me.
>
> query,database,sql
>
> Thanks in advance.
>
>
>
>
>
>
> _
> Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp.
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: query problem

[Roger Baklund]

* saraswathy saras
> How to select latest 3 days records  from the table  according to the 
> latests date.
> The data is like this:-
> 
> name  date
> a 02-03-01
> b 02-03-15
> c 02-03-20
> d 02-03-20
> e 02-04-28
> f 02-04-28
> g 02-04-30
> 
> The result should be like this:-
> 
> name  date
> g 02-04-30
> f 02-04-28
> e 02-04-28
> c 02-03-20
> d 02-03-20
> 
> 
> Is it possible.Please help me.

Yes, use ORDER BY ... DESC and LIMIT:

  SELECT * FROM table ORDER BY date_field DESC LIMIT 3

http://www.mysql.com/doc/S/E/SELECT.html >

-- 
Roger

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query problem

[Anvar Hussain K.M.]

Hi,
This might work for you:

select @tempvar := max(datecolumn) from tablename group by datecolumn order 
by datecolumn desc limit 3;
select * from tablename where datecol >= @tempvar order by datecolumn desc;

Anvar.

At 06:12 AM 02/05/2002 +, you wrote:
>hi everyboby,
>
>How to select latest 3 days records  from the table  according to the 
>latests date.
>The data is like this:-
>
>name  date
>a 02-03-01
>b 02-03-15
>c 02-03-20
>d 02-03-20
>e 02-04-28
>f 02-04-28
>g 02-04-30
>
>The result should be like this:-
>
>name  date
>g 02-04-30
>f 02-04-28
>e 02-04-28
>c 02-03-20
>d 02-03-20
>
>
>Is it possible.Please help me.
>
>query,database,sql
>
>Thanks in advance.
>
>
>
>
>
>
>_
>Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
>
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem...

[asherh]

Thanks guys, works a treat. Yip, it was the Alias thing... interesting.

- Original Message -
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "asherh" <[EMAIL PROTECTED]>
Sent: Wednesday, February 27, 2002 2:39 PM
Subject: RE: Query Problem...


> * asherh
> > An example of the record output I was after is...
> >
> > ProjectIDProjectOwnerProjectManager
> > A12345 Bob Smith   John Smith
> >
> > from tables:
> >
> > User -
> > UserIdFullName
> > 1Bob Smith
> > 2John Smith
> >
> > Project -
> > ProjectIdProjectOwner   ProjectManager
> > A1234512
> >
> > I have tried all sorts of joins and statements without much
> > success... I can
> > obtain one name or both names if they are the same... but not different
> > names together in the one record.
> >
> > Can you possibly provide an example of the specific joins you are
talking
> > about. I think I must be missing something fundamental here.
>
> You don't show your failing joins, but my guess is you are missing
aliases,
> which are needed when joining the same table multiple times:
>
> SELECT ProjectId,owner.FullName,manager.FullName
>   FROM Project
>   LEFT JOIN User AS owner ON
> owner.UserId = Project.ProjectOwnerID
>   LEFT JOIN User AS manager ON
> manager.UserId = Project.ProjectManagerID
>   WHERE
> ProjectId = 'A12345';
>
> --
> Roger
> query
>
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query Problem...

[Roger Baklund]

* asherh
> An example of the record output I was after is...
>
> ProjectIDProjectOwnerProjectManager
> A12345 Bob Smith   John Smith
>
> from tables:
>
> User -
> UserIdFullName
> 1Bob Smith
> 2John Smith
>
> Project -
> ProjectIdProjectOwner   ProjectManager
> A1234512
>
> I have tried all sorts of joins and statements without much
> success... I can
> obtain one name or both names if they are the same... but not different
> names together in the one record.
>
> Can you possibly provide an example of the specific joins you are talking
> about. I think I must be missing something fundamental here.

You don't show your failing joins, but my guess is you are missing aliases,
which are needed when joining the same table multiple times:

SELECT ProjectId,owner.FullName,manager.FullName
  FROM Project
  LEFT JOIN User AS owner ON
owner.UserId = Project.ProjectOwnerID
  LEFT JOIN User AS manager ON
manager.UserId = Project.ProjectManagerID
  WHERE
ProjectId = 'A12345';

--
Roger
query


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem...

[DL Neil]

Hi Ash,

> I have tried all sorts of joins and statements without much success... I can
> obtain one name or both names if they are the same... but not different
> names together in the one record.

=how about some example code showing what you are doing?
At the very least it gives me a 'starting point' and saves my time/typing.
Also without the 'hints' communicated by your code, I might assume you are 'smarter', 
or more of a beginner,
than you really are!

=it sounds as if it is not the 'type' of join that is the 'problem', but the number of 
joins.
A 'stock standard' LEFT INNER JOIN is quite sufficient.
The 'trick', as I mentioned, is to set up two joins!

> Can you possibly provide an example of the specific joins you are talking
> about. I think I must be missing something fundamental here.

Let's start with the project table and simply list that:

SELECT ProjectId, ProjectOwner, ProjectManager
  FROM AshProject

ProjectIdProjectOwner   ProjectManager
A1234512

Now let's bring in the User tbl and 'translate' the ProjectOwner's Id/nr into his/her 
name. Put in one join and
output the name of the ProjectOwner (as well as his/her number, for debugging 
purposes):

SELECT ProjectId, ProjectOwner, ProjectManager, FullName AS ProjectOwner
  FROM AshProject, AshUser
  WHERE ProjectOwner = UserId

ProjectIdProjectOwner   ProjectManager   ProjectOwner
A12345 1 2 Bob Smith

So far, so good. Now we need to translate/add the ProjectManager's name!

Persisting with a single join and trying to add WHERE ... AND ProjectManager = UserId, 
results in it all turning
to ashes, because MySQL goes looking for a SINGLE UserId value that will equal *both* 
names, and such will only
happen if the Project's Manager and Owner are the same person! (in the case of the 
limited sample data provided,
never!)

We need to add a second version/copy of the User tbl to the query, and distinguish 
between the two 'versions' by
using aliases - that way you can apparently use UserId 'twice', ie two *separate* 
UserIds and thus the two names
can be retrieved from different rows of the User tbl (or twice from the same row, if 
required):

SELECT ProjectId, ProjectOwner, ProjectManager,
AU1.FullName AS ProjectOwner, AU2.FullName AS ProjectManager
  FROM AshProject, AshUser AS AU1, AshUser AS AU2
  WHERE ProjectOwner = AU1.UserId
AND ProjectManager = AU2.UserId

ProjectIdProjectOwner   ProjectManager   ProjectOwner   ProjectManager
A12345 1 2 Bob Smith John Smith

Once you've checked the logic/implementation, don't forget to remove the 'workings' 
and then you'll have the
answer required.

Ok now?
=dn



> > > I am using MySQL 3.22.32 and are trying to accomplish the following
> (without
> > > going into too much detail, this is an example of the exact
> situation)...
> > >
> > > 1) I have two tables:
> > >
> > >  a) User table containing: UserID, FullName
> > >  b) Project table containing: ProjectID, ProjectManagerID and
> ProjectOwnerID
> > >
> > >  ProjectManagerID and ProjectOwnerID are effectively UserIDs from the
> User
> > > table.
> > >
> > > 2) When I pull a particular record from the database by ProjectID, for
> > > readability purposes, I would like the accompanying ProjectManagerID and
> > > ProjectOwnerID to
> > > be displayed as a name, not an ID (for example: John Smith, not A12930).
> > >
> > > Has anyone got any ideas how I can select (within one record) both names
> > > from the User table by each respective UserID (represented by the
> > > ProjectManagerID and the ProjectOwnerID)??
> >
> >
> > This is quite logical (when you look back at it!). Set up two joins from
> Project to User, the first equating
> > ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just
> because User is only one table,
> > doesn't mean you can't have multiple ways of joining to it!
> >
> > If that's not it, please send the query you have so far.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem...

[asherh]

Hi,

Thanks for the reply.

An example of the record output I was after is...

ProjectIDProjectOwnerProjectManager
A12345 Bob Smith   John Smith

from tables:

User -
UserIdFullName
1Bob Smith
2John Smith

Project -
ProjectIdProjectOwner   ProjectManager
A1234512

I have tried all sorts of joins and statements without much success... I can
obtain one name or both names if they are the same... but not different
names together in the one record.

Can you possibly provide an example of the specific joins you are talking
about. I think I must be missing something fundamental here.

Chrs,
Ash

- Original Message -
From: "DL Neil" <[EMAIL PROTECTED]>
To: "asherh" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 27, 2002 9:12 AM
Subject: Re: Query Problem...


> Hi Ash,
>
> > I am using MySQL 3.22.32 and are trying to accomplish the following
(without
> > going into too much detail, this is an example of the exact
situation)...
> >
> > 1) I have two tables:
> >
> >  a) User table containing: UserID, FullName
> >  b) Project table containing: ProjectID, ProjectManagerID and
ProjectOwnerID
> >
> >  ProjectManagerID and ProjectOwnerID are effectively UserIDs from the
User
> > table.
> >
> > 2) When I pull a particular record from the database by ProjectID, for
> > readability purposes, I would like the accompanying ProjectManagerID and
> > ProjectOwnerID to
> > be displayed as a name, not an ID (for example: John Smith, not A12930).
> >
> > Has anyone got any ideas how I can select (within one record) both names
> > from the User table by each respective UserID (represented by the
> > ProjectManagerID and the ProjectOwnerID)??
>
>
> This is quite logical (when you look back at it!). Set up two joins from
Project to User, the first equating
> ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just
because User is only one table,
> doesn't mean you can't have multiple ways of joining to it!
>
> If that's not it, please send the query you have so far.
>
> Regards,
> =dn
>
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem...

[DL Neil]

Hi Ash,

> I am using MySQL 3.22.32 and are trying to accomplish the following (without
> going into too much detail, this is an example of the exact situation)...
>
> 1) I have two tables:
>
>  a) User table containing: UserID, FullName
>  b) Project table containing: ProjectID, ProjectManagerID and ProjectOwnerID
>
>  ProjectManagerID and ProjectOwnerID are effectively UserIDs from the User
> table.
>
> 2) When I pull a particular record from the database by ProjectID, for
> readability purposes, I would like the accompanying ProjectManagerID and
> ProjectOwnerID to
> be displayed as a name, not an ID (for example: John Smith, not A12930).
>
> Has anyone got any ideas how I can select (within one record) both names
> from the User table by each respective UserID (represented by the
> ProjectManagerID and the ProjectOwnerID)??


This is quite logical (when you look back at it!). Set up two joins from Project to 
User, the first equating
ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just because User 
is only one table,
doesn't mean you can't have multiple ways of joining to it!

If that's not it, please send the query you have so far.

Regards,
=dn




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Problem

[Rodney Broom]

From: <[EMAIL PROTECTED]>

> >>My query
> select * from table1,table2 WHERE table1.ID = table2.code

> >> Query results
> 1 2 2
> 
> >> Desired results
> 1 2


Try:

  SELECT * FROM table1,table2
   WHERE table1.ID = table2.code
   GROUP BY table2.code


You'll find this someplace in:

  http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html

But it's a rather thick document.

---
Rodney Broom
Programmer: Desert.Net




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php