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

query problem with null

[Richard Reina]

 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?

Re: Alphabetical pagination mysql regexp query problem

[Michael Dykman]

Is this what you meant?

SELECT * FROM tbl where Fname REGEXP '^[abcd]' AND Lname REGEXP '^[abcd]'

(alternatively, the extression could be simpliefied as "REGEXP '^[a-d]" )

 - md


On Fri, Oct 22, 2010 at 11:01 AM, bharani kumar
 wrote:
> in my database,
>
>
> if i want to render firstname and lastname match case ,
>
> am using REGEXP '^[abcd]' WORK FOR SINGLE FIELD,
>
> Now i have fname and lastname ,
>
> How to make the query , which display both and single field satisfieds
> records ,
>
> That is my query should return like
>
> Name(combined Firstname and lastname)
>
> Abbas, Ajai,
> Ashok,Amar,
> Abdul,Baskar,
> Abdul,cagan,
> Arun,Deena
>
> Any ideas
>
>
> On Fri, Oct 22, 2010 at 8:55 AM, Michael Dykman  wrote:
>>
>> You regular expression is alittle off.  You don't need the OR operator
>> '|' inside the character class definition, it is implied.  Try this:
>>
>> [php]
>> SELECT * FROM tbl where Fname REGEXP '^[abcd]'
>> [/php]
>>
>>  - michael dykman
>>
>> On Thu, Oct 21, 2010 at 11:07 PM, bharani kumar
>>  wrote:
>> > Hi ,
>> >
>> > Am just trying alphabetical pagination ,
>> >
>> > Yes there is lot of pager class there for this ,
>> >
>> > but in my requirement little but diff , that is
>> >
>> > totally 4 sets ,
>> >
>> > A-D    E-H     I-P    Q-S (Assume there are pager links)
>> >
>> > When user click the A-D , then it should show all the customer name
>> > starting
>> > with A , B, C, D names should be pull out ,
>> >
>> > For that i just used this REGEXP QUERY ,
>> >
>> > [php]
>> > SELECT * FROM tbl where Fname REGEXP '^[a]'
>> >
>> > [/php]
>> >
>> > This works fine, only rendering the starting with "a" Names ,
>> >
>> > But if i add the or condition in that
>> >
>> > [php]
>> > SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]'
>> > [/php]
>> >
>> > This condition not working, the output for the above query , pullout the
>> > all
>> > records starting wiht what ever,(A-z)
>> >
>> > So What i want is,
>> >
>> > if i hit A-D the it should pullout only matched character
>> >
>> > example
>> >
>> > Amir
>> > Amboss
>> > Aron
>> > Basker
>> > Banu
>> > chithra
>> > chan
>> > dilli
>> >
>> >
>> > but my out put should not like , because this not exact records for that
>> > pager linke
>> >
>> > Amir
>> > Amboss
>> > Aron
>> > Basker
>> > Banu
>> > hanuman
>> > chithra
>> > gayathree
>> > chan
>> > dilli
>> > vasanth
>> > yakee
>> >
>> >
>> > Even i tried somthing like these query ,
>> >
>> > cFname  REGEXP '^[[:<:]]a.*|b.*[[:>:]]'
>> > cFname  REGEXP '^[[:<:]]a.*|b.*'
>> >
>> >
>> > If i add the OR condition in the REGEXP , then displaying unwanted
>> > records,
>> >
>> >
>> > Also main thing , i want solution for case insensitive ,
>> >
>> > Thanks
>> >
>> > --
>> > Regards
>> > B.S.Bharanikumar
>> > http://php-mysql-jquery.blogspot.com/
>> >
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdyk...@gmail.com
>>
>>  May the Source be with you.
>
>
>
> --
> Regards
> B.S.Bharanikumar
> http://php-mysql-jquery.blogspot.com/
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Alphabetical pagination mysql regexp query problem

[bharani kumar]

in my database,


if i want to render firstname and lastname match case ,

am using REGEXP '^[abcd]' WORK FOR SINGLE FIELD,

Now i have fname and lastname ,

How to make the query , which display both and single field satisfieds
records ,

That is my query should return like

Name(combined Firstname and lastname)

Abbas, Ajai,
Ashok,Amar,
Abdul,Baskar,
Abdul,cagan,
Arun,Deena

Any ideas


On Fri, Oct 22, 2010 at 8:55 AM, Michael Dykman  wrote:

> You regular expression is alittle off.  You don't need the OR operator
> '|' inside the character class definition, it is implied.  Try this:
>
> [php]
> SELECT * FROM tbl where Fname REGEXP '^[abcd]'
> [/php]
>
>  - michael dykman
>
> On Thu, Oct 21, 2010 at 11:07 PM, bharani kumar
>  wrote:
> > Hi ,
> >
> > Am just trying alphabetical pagination ,
> >
> > Yes there is lot of pager class there for this ,
> >
> > but in my requirement little but diff , that is
> >
> > totally 4 sets ,
> >
> > A-DE-H I-PQ-S (Assume there are pager links)
> >
> > When user click the A-D , then it should show all the customer name
> starting
> > with A , B, C, D names should be pull out ,
> >
> > For that i just used this REGEXP QUERY ,
> >
> > [php]
> > SELECT * FROM tbl where Fname REGEXP '^[a]'
> >
> > [/php]
> >
> > This works fine, only rendering the starting with "a" Names ,
> >
> > But if i add the or condition in that
> >
> > [php]
> > SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]'
> > [/php]
> >
> > This condition not working, the output for the above query , pullout the
> all
> > records starting wiht what ever,(A-z)
> >
> > So What i want is,
> >
> > if i hit A-D the it should pullout only matched character
> >
> > example
> >
> > Amir
> > Amboss
> > Aron
> > Basker
> > Banu
> > chithra
> > chan
> > dilli
> >
> >
> > but my out put should not like , because this not exact records for that
> > pager linke
> >
> > Amir
> > Amboss
> > Aron
> > Basker
> > Banu
> > hanuman
> > chithra
> > gayathree
> > chan
> > dilli
> > vasanth
> > yakee
> >
> >
> > Even i tried somthing like these query ,
> >
> > cFname  REGEXP '^[[:<:]]a.*|b.*[[:>:]]'
> > cFname  REGEXP '^[[:<:]]a.*|b.*'
> >
> >
> > If i add the OR condition in the REGEXP , then displaying unwanted
> records,
> >
> >
> > Also main thing , i want solution for case insensitive ,
> >
> > Thanks
> >
> > --
> > Regards
> > B.S.Bharanikumar
> > http://php-mysql-jquery.blogspot.com/
> >
>
>
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>



-- 
Regards
B.S.Bharanikumar
http://php-mysql-jquery.blogspot.com/

Re: Alphabetical pagination mysql regexp query problem

[Michael Dykman]

You regular expression is alittle off.  You don't need the OR operator
'|' inside the character class definition, it is implied.  Try this:

[php]
SELECT * FROM tbl where Fname REGEXP '^[abcd]'
[/php]

 - michael dykman

On Thu, Oct 21, 2010 at 11:07 PM, bharani kumar
 wrote:
> Hi ,
>
> Am just trying alphabetical pagination ,
>
> Yes there is lot of pager class there for this ,
>
> but in my requirement little but diff , that is
>
> totally 4 sets ,
>
> A-D    E-H     I-P    Q-S (Assume there are pager links)
>
> When user click the A-D , then it should show all the customer name starting
> with A , B, C, D names should be pull out ,
>
> For that i just used this REGEXP QUERY ,
>
> [php]
> SELECT * FROM tbl where Fname REGEXP '^[a]'
>
> [/php]
>
> This works fine, only rendering the starting with "a" Names ,
>
> But if i add the or condition in that
>
> [php]
> SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]'
> [/php]
>
> This condition not working, the output for the above query , pullout the all
> records starting wiht what ever,(A-z)
>
> So What i want is,
>
> if i hit A-D the it should pullout only matched character
>
> example
>
> Amir
> Amboss
> Aron
> Basker
> Banu
> chithra
> chan
> dilli
>
>
> but my out put should not like , because this not exact records for that
> pager linke
>
> Amir
> Amboss
> Aron
> Basker
> Banu
> hanuman
> chithra
> gayathree
> chan
> dilli
> vasanth
> yakee
>
>
> Even i tried somthing like these query ,
>
> cFname  REGEXP '^[[:<:]]a.*|b.*[[:>:]]'
> cFname  REGEXP '^[[:<:]]a.*|b.*'
>
>
> If i add the OR condition in the REGEXP , then displaying unwanted records,
>
>
> Also main thing , i want solution for case insensitive ,
>
> Thanks
>
> --
> Regards
> B.S.Bharanikumar
> http://php-mysql-jquery.blogspot.com/
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Alphabetical pagination mysql regexp query problem

[bharani kumar]

Hi ,

Am just trying alphabetical pagination ,

Yes there is lot of pager class there for this ,

but in my requirement little but diff , that is

totally 4 sets ,

A-DE-H I-PQ-S (Assume there are pager links)

When user click the A-D , then it should show all the customer name starting
with A , B, C, D names should be pull out ,

For that i just used this REGEXP QUERY ,

[php]
SELECT * FROM tbl where Fname REGEXP '^[a]'

[/php]

This works fine, only rendering the starting with "a" Names ,

But if i add the or condition in that

[php]
SELECT * FROM tbl where Fname REGEXP '^[a|b|c|d]'
[/php]

This condition not working, the output for the above query , pullout the all
records starting wiht what ever,(A-z)

So What i want is,

if i hit A-D the it should pullout only matched character

example

Amir
Amboss
Aron
Basker
Banu
chithra
chan
dilli


but my out put should not like , because this not exact records for that
pager linke

Amir
Amboss
Aron
Basker
Banu
hanuman
chithra
gayathree
chan
dilli
vasanth
yakee


Even i tried somthing like these query ,

cFname  REGEXP '^[[:<:]]a.*|b.*[[:>:]]'
cFname  REGEXP '^[[:<:]]a.*|b.*'


If i add the OR condition in the REGEXP , then displaying unwanted records,


Also main thing , i want solution for case insensitive ,

Thanks

-- 
Regards
B.S.Bharanikumar
http://php-mysql-jquery.blogspot.com/

RE: Update query problem

[Travis Ard]

Try using the IS NULL operator instead of !

-Travis

-Original Message-
From: Andy Wallace [mailto:awall...@ihouseweb.com] 
Sent: Thursday, September 16, 2010 10:47 AM
To: mysql@lists.mysql.com
Subject: Update query problem

So I'm having a problem with an update query. I have three tables:

Table: A
Columns:   acnt, name, company, email, domain

Table: AM
Columns:   acnt, m_id

Table: M
Columns:   m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update  A
join   AM on A.acnt = AM.acnt
joinM on AM.m_id = M.m_id
SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name),
 M.company = IF( (!M.company AND A.company != ''), A.company,
M.company),
 M.email   = IF( (!M.email   AND A.email != ''),   A.email,   M.email),
 M.domain  = IF( (!M.domain  AND A.domain != ''),  A.domain,  M.domain)

Any thoughts?

THanks,
andy

-- 
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Update query problem

[Andy Wallace]

So I'm having a problem with an update query. I have three tables:

Table: A
Columns:   acnt, name, company, email, domain

Table: AM
Columns:   acnt, m_id

Table: M
Columns:   m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update  A
join   AM on A.acnt = AM.acnt
joinM on AM.m_id = M.m_id
SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name),
M.company = IF( (!M.company AND A.company != ''), A.company, M.company),
M.email   = IF( (!M.email   AND A.email != ''),   A.email,   M.email),
M.domain  = IF( (!M.domain  AND A.domain != ''),  A.domain,  M.domain)

Any thoughts?

THanks,
andy

--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Distinct Query Problem

[Tompkins Neil]

Hi Sonal,

What information do you require ?  Basically in the MasterTB we have
productID and name, description.  In the LookupTB we have the productID from
the MasterTB, the productID from ProductTB and a char field for preferred
supplier Yes/No.  In the ProductTB we have a list of products from each
supplier.  This is link using the LookupTB.

Let me know what other information you require.

Thanks
Neil

On Tue, Dec 23, 2008 at 4:19 AM, Raghani, Sonal (IE10) <
sonal.ragh...@honeywell.com> wrote:

> Hi,
>
> The problem statement needs to be elaborated. Please give the exact
> columns interms of wat is suppliers which table is it coming from
> etc.
>
> -Original Message-
> From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
> Sent: Monday, December 22, 2008 5:16 PM
> To: Baron Schwartz; [MySQL]
> Subject: Re: Distinct Query Problem
>
> Hi,
>
> If anyone could point me in the right direction, I'd be most grateful.
>
> Thanks !
>
> Neil
>
> On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil
>  > wrote:
>
> > Hi
> >
> > I'm having trouble trying to figure this out.  Any help/example would
> be
> > grateful.
> >
> > Thanks
> > Neil
> >
> >   On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz 
> wrote:
> >
> >> On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
> >>  wrote:
> >>
> >> > of products for all suppliers for a particular product.  However I
> want
> >> to
> >> > be able to show the lowest price product from just the lowest
> priced
> >> > supplier.
> >>
> >> http://jan.kneschke.de/projects/mysql/groupwise-max
> >>
> >>
> http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row
> -per-group-in-sql/
> >>
> >> --
> >> Baron Schwartz, Director of Consulting, Percona Inc.
> >> Our Blog: http://www.mysqlperformanceblog.com/
> >> Our Services: http://www.percona.com/services.html
> >>
> >
> >
>

Re: Distinct Query Problem

[Brent Baisley]

On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
 wrote:
> Hi,
>
> I've the following query which I'm having problems with.  Basically I have
> a 5 tables as follows :
>
> MasterTB - Contains list of master records
> LookupTB - Contains relationship between MasterTB to ProductTB
> ContentTB - Contains description of product, and location of data files
> PriceTB - Contains list of prices per day for each product
> ProductTB - List of products
>
> SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name,
> ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts
> FROM MasterTB
> INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID
> INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID
> INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID
> INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID
> WHERE MasterTB.Enabled = 'Yes'
> AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www'
> AND PriceTB.Price > 0
> AND PriceTB.Quantity > 0
> GROUP BY ProductTB.ProductID, MasterTB.MasterID
> ORDER BY ProductTB.MarkUp DESC
>
> Basically each product is listed in the master table, and can have a number
> of suppliers linked to it (ProductTB).  The query above will show me a list
> of products for all suppliers for a particular product.  However I want to
> be able to show the lowest price product from just the lowest priced
> supplier.
>
> Any ideas ?
>
> Thanks,
> Neil
>

You are actually going to need at least 2 queries, which will be
nested. You need to first find the lowest price, then figure out which
supplier has that lowest price. If more than one supplier has the same
lowest price, you won't be able to do it in a single query and will
likely need to do post processing.
Just an example to point you in the right direction. First, get the
lowest price for the product(s) you are interested in:
SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice
FROM PriceTB GROUP BY ProductID

Then you use that as a "virtual" table (MinPriceList) to join on the
supplier with that price for that product.
SELECT ProductTB.Supplier, MinPriceList.ProductID,
MinPriceList.MinPrice As PriceDiscounts
FROM MasterTB
INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID
INNER JOIN (
SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice
FROM PriceTB GROUP BY ProductID
) AS MinPriceList ON ProductTB.ProductID=MinPriceList.ProductID AND
ProductTB.Price=MinPriceList.MinPrice
INNER JOIN ...

Basically what you are doing is creating a virtual table on the fly
based on a select statement.  It sort of like a temp table, but
without having to go through the creation and management of it. Treat
the virtual table created from the query as if it was a regular table.
As I mentioned, this will break if more than one supplier has the same
price. You'll get an arbitrary supplier ID out of those with the
minimum price. This is because there is no unique value to join on.

Hope that points you in the right direction.

Brent Baisley

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Distinct Query Problem

[Tompkins Neil]

OK, I've made further progress by changing GROUP BY ProductTB.ProductID,
MasterTB.MasterID to GROUP BY MasterTB.MasterID.

However ProductTB.Supplier is showing the incorrect Supplier.  Why is this ?

Thanks
Neil

On Mon, Dec 22, 2008 at 11:45 AM, Tompkins Neil <
neil.tompk...@googlemail.com> wrote:

> Hi,
>
> If anyone could point me in the right direction, I'd be most grateful.
>
> Thanks !
>
> Neil
>
>   On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil <
> neil.tompk...@googlemail.com> wrote:
>
>> Hi
>>
>> I'm having trouble trying to figure this out.  Any help/example would be
>> grateful.
>>
>> Thanks
>> Neil
>>
>>   On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz wrote:
>>
>>> On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
>>>  wrote:
>>>
>>> > of products for all suppliers for a particular product.  However I want
>>> to
>>> > be able to show the lowest price product from just the lowest priced
>>> > supplier.
>>>
>>> http://jan.kneschke.de/projects/mysql/groupwise-max
>>>
>>> http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
>>>
>>> --
>>> Baron Schwartz, Director of Consulting, Percona Inc.
>>> Our Blog: http://www.mysqlperformanceblog.com/
>>> Our Services: http://www.percona.com/services.html
>>>
>>
>>
>

Re: Distinct Query Problem

[Tompkins Neil]

Hi,

If anyone could point me in the right direction, I'd be most grateful.

Thanks !

Neil

On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil  wrote:

> Hi
>
> I'm having trouble trying to figure this out.  Any help/example would be
> grateful.
>
> Thanks
> Neil
>
>   On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz  wrote:
>
>> On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
>>  wrote:
>>
>> > of products for all suppliers for a particular product.  However I want
>> to
>> > be able to show the lowest price product from just the lowest priced
>> > supplier.
>>
>> http://jan.kneschke.de/projects/mysql/groupwise-max
>>
>> http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
>>
>> --
>> Baron Schwartz, Director of Consulting, Percona Inc.
>> Our Blog: http://www.mysqlperformanceblog.com/
>> Our Services: http://www.percona.com/services.html
>>
>
>

Re: Distinct Query Problem

[Tompkins Neil]

Hi

I'm having trouble trying to figure this out.  Any help/example would be
grateful.

Thanks
Neil

On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz  wrote:

> On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
>  wrote:
>
> > of products for all suppliers for a particular product.  However I want
> to
> > be able to show the lowest price product from just the lowest priced
> > supplier.
>
> http://jan.kneschke.de/projects/mysql/groupwise-max
>
> http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
>
> --
> Baron Schwartz, Director of Consulting, Percona Inc.
> Our Blog: http://www.mysqlperformanceblog.com/
> Our Services: http://www.percona.com/services.html
>

Re: Distinct Query Problem

[Jochem van Dieten]

On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote:
> Basically each product is listed in the master table, and can have a number
> of suppliers linked to it (ProductTB).  The query above will show me a list
> of products for all suppliers for a particular product.  However I want to
> be able to show the lowest price product from just the lowest priced
> supplier.

Search the manual for "group-wise maximum".

Jochem

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Distinct Query Problem

[Tompkins Neil]

Hi,

I've the following query which I'm having problems with.  Basically I have
a 5 tables as follows :

MasterTB - Contains list of master records
LookupTB - Contains relationship between MasterTB to ProductTB
ContentTB - Contains description of product, and location of data files
PriceTB - Contains list of prices per day for each product
ProductTB - List of products

SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name,
ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts
FROM MasterTB
INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID
INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID
INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID
INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID
WHERE MasterTB.Enabled = 'Yes'
AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www'
AND PriceTB.Price > 0
AND PriceTB.Quantity > 0
GROUP BY ProductTB.ProductID, MasterTB.MasterID
ORDER BY ProductTB.MarkUp DESC

Basically each product is listed in the master table, and can have a number
of suppliers linked to it (ProductTB).  The query above will show me a list
of products for all suppliers for a particular product.  However I want to
be able to show the lowest price product from just the lowest priced
supplier.

Any ideas ?

Thanks,
Neil

Query problem

[Matthew Stuart]

I am trying to display results from one or the other part of the  
query, however, at the moment it is showing results from both parts.  
The Replace part of the query works fine in that it chooses the  
correct data to display, but the content relevant to /8/ always  
displays even when I select, say, /13/ or /22/. I need to hide  
results for /8/ until it is selected.


rsChannelArticles.Source = "SELECT * FROM bunker01db1.tbl_allarticles  
WHERE (fld_category LIKE '%/" + Replace(rsChannelArticles__channel,  
"'", "''") + "/%' AND fld_show = 1 AND fld_reldate <=NOW()) OR  
(fld_category LIKE '%/8/%' AND fld_reldate >=NOW()) ORDER BY  
fld_reldate DESC"


The reason for this is that the webpage displays content when a  
release date/time (or embargo) has passed, however, the section /8/  
needs content to disappear once that date/time has passed.


Any ideas?

Thanks

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]

Query problem

[sivasakthi]

Hi all,

Iam  having the one table name called AccessDetails and data inside that
tables is following,

DateTime UserName   SiteName
ScanType   Status   Virus_Category

| 2008-04-16 | 13:05:31 | 172.16.1.22 | - | www.veer.com
|C   | A| unclassified   |
| 2008-04-16 | 13:05:31 | 172.16.1.52 | - | blogactiv.eu
|C  | O | unclassified   |
| 2008-04-16 | 13:05:32 | 172.16.1.22 | - | www.veer.com
|V  | A| Internet  |
| 2008-04-16 | 13:05:32 | 172.16.1.52 | - |
www.verylowsodium.com |C  | D| unclassified   |
| 2008-04-16 | 13:05:32 | 172.16.1.52 | - | blogactiv.eu
|V | A | unclassified   |


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.. 


select
count(a.UserName),sum(b.totalsites),sum(a.Allow),sum(a.Denied),sum(a.Over),sum(b.totalconn)
 from (select a.UserName,sum(a.Allow) as Allow,sum(a.Denied) as 
Denied,sum(a.Over) as Over from (select UserName,case Status when 'A' then 
count(distinct SiteName) else 0 END as Allow ,case Status when 'D' then 
count(distinct SiteName) else 0 END as Denied,case Status when 'O' then 
count(distinct SiteName) else 0 END as Over from AccessDetails where 
Virus_category = 'unclassified ' and Date<='2008-04-16' and Date>='2008-04-16' 
and ScanType='C' group by UserName, Status) a group by a.UserName) a left join 
(select UserName,count(distinct SiteName)as totalsites, count(Time)as totalconn 
from AccessDetails where Virus_category = 'unclassified ' and 
Date<='2008-04-16' and Date>='2008-04-16' and ScanType='C' and Virus_category 
<> '-' and UserName <> '-' group by UserName)b on a.UserName=b.UserName where 
b.totalsites is not null 



Thanks In Advance ,

Re: SQL query problem

[Ravi Kumar.]

Dear Mat,

Your mail is not very clear. But I have a feeling that using '%' wildcard in
the like operand should help you

Regards,

Ravi.

On 11/14/07, Matthew Stuart <[EMAIL PROTECTED]> wrote:
>
> Hi, I have built a site with Dreamweaver and I have a problem with a
> query.
>
> I am trying to pass a parameter from one page to another to drill
> down. Basically, I have one product entry that is in multiple
> categories on my website. So, say it's a dress, it is therefore
> related to category 1 which is 'Girls', but it is also more
> specifically related to category 2 which is 'Girls Dresses'.
>
> The way I have set this up is to have a column called MultiCategoryID
> that holds both the number 1 and 2 like this: /1/2/
>
> When a user clicks a link to look at dresses, the parameter 2 is
> passed, but my query on the result page is wrong in some way because
> no records are displaying even though there is content to display.
> This is what I have so far:
>
> SELECT *
> FROM Products
> WHERE MultiCategoryID LIKE '/catdrill/'
> ORDER BY ProductID DESC
>
> The parameter settings are:
> Name: catdrill
> Type: Numeric
> Value: Request("MCID") MCID is the url parameter being passed
> Default value: 2
>
> Only when I test the Default value with an exact match of /1/2/ does
> any product display. What have I done wrong here? Is there a way to
> get it to recognise that I want it to pick specific numbers between
> the slashes rather than the whole lot? I have tried to change the
> slashes to full stops just in case they are causing problems, but
> it's still giving the same problem.
>
> Thanks.
>
> Mat
>
>

SQL query problem

[Matthew Stuart]

Hi, I have built a site with Dreamweaver and I have a problem with a  
query.


I am trying to pass a parameter from one page to another to drill  
down. Basically, I have one product entry that is in multiple  
categories on my website. So, say it's a dress, it is therefore  
related to category 1 which is 'Girls', but it is also more  
specifically related to category 2 which is 'Girls Dresses'.


The way I have set this up is to have a column called MultiCategoryID  
that holds both the number 1 and 2 like this: /1/2/


When a user clicks a link to look at dresses, the parameter 2 is  
passed, but my query on the result page is wrong in some way because  
no records are displaying even though there is content to display.  
This is what I have so far:


SELECT *
FROM Products
WHERE MultiCategoryID LIKE '/catdrill/'
ORDER BY ProductID DESC

The parameter settings are:
Name: catdrill
Type: Numeric
Value: Request("MCID") MCID is the url parameter being passed
Default value: 2

Only when I test the Default value with an exact match of /1/2/ does  
any product display. What have I done wrong here? Is there a way to  
get it to recognise that I want it to pick specific numbers between  
the slashes rather than the whole lot? I have tried to change the  
slashes to full stops just in case they are causing problems, but  
it's still giving the same problem.


Thanks.

Mat

Re: Insert Select query problem

[Ed Reed]

Thanks Jay,
 
I had to make a change to the first part of the query to get the results that I 
wanted but your suggestion was definitely what I needed to get to the solution.
 
Thanks again.
 
For those that are interested, here's the final solution,
 
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)>0,r.qty, totals.TotQty)
FROM request r 
  JOIN 
   (SELECT Item, SUM(Qty) AS TotQty 
FROM inventory 
GROUP BY Item) AS totals 
  ON r.Required = totals.Item 
 
UNION All
SELECT 0, totals.Item, (r.Qty - totals.TotQty) 
FROM request r 
   JOIN 
  (SELECT Item, SUM(Qty) AS TotQty 
   FROM inventory 
   GROUP BY Item) AS totals 
   ON r.Required = totals.Item 
WHERE r.Qty > totals.TotQty;

>>> On 8/10/07 at 12:33 PM, in message <[EMAIL PROTECTED]>, Jay Pipes <[EMAIL 
>>> PROTECTED]> wrote:
Ed Reed wrote:
> Hi All,
>  
> I have an issue that I need to resolve that is difficult to explain. I
> hope that someone can understand what I*m trying to do and shed some
> light on a solution. Here goes.
>  
> I have three tables, inventory, which is a list of transactions with
> positive and negative values; request, which essentially is a temporary
> table that gets deleted after it*s used here; and purchase, which holds
> the solution as to whether an item is to be purchased or removed from
> inventory,
>  
> CREATE TABLE `inventory` (
>   `id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `Item` varchar(100) NOT NULL DEFAULT '',
>   `Qty` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; 
>  
> CREATE TABLE `purchase` (
>   `id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `Source` int(11) DEFAULT NULL,
>   `Item` varchar(100) NOT NULL DEFAULT '',
>   `Qty` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
>  
> CREATE TABLE `request` (
>   `id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `Required` varchar(100) NOT NULL DEFAULT '',
>   `Qty` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 
>  
> My Inventory and Request tables have data in them like this,
>  
> Insert Into `inventory` (Item, Qty)
> Values 
> ('Apples',5),
> ('Bananas',4),
> ('Cherries',6),
> ('Apples',-1),
> ('Bananas',1),
> ('Cherries',-2),
> ('Apples',3),
> ('Bananas',-7),
> ('Cherries',19),
> ('Apples',-5),
> ('Bananas',88),
> ('Cherries',6);
>  
> Insert Into `request` (Required, Qty)
> Values
> ('Apples', 12),
> ('Bananas', 112),
> ('Cherries', 5);
>  
> Now what I*d like to do is create a single Insert Select query that
> creates a record in my purchase table for each of the items in my
> request table based on the number of items available in my inventory.
> But, if there aren't enough items in the inventory to cover the amount
> requested, I need to have a second record for that item in the purchase
> table with the qty difference to another source. So based on the data in
> the inventory my current totals are,
>  
> +--+--+
> | Item | Sum(Qty) |
> +--+--+
> | Apples   | 2|
> | Bananas  | 86   |
> | Cherries | 29   |
> +--+--+
>  
> and based on the qty of items in my request I would like to have a
> purchase table that looks like this,
>  
> ++--+-+
> | Source | Item | Qty |
> ++--+-+
> | 1  | Apples   | 2   |
> | 0  | Apples   | 10  |
> | 1  | Bananas  | 86  |
> | 0  | Bananas  | 26  |
> | 1  | Cherries | 5   |
> ++--+-+
>  
> with a source of 1 meaning pull the items from inventory and a source
> of 0 means purchase them from somewhere else.
>  
> Can anyone help me with this?

Try this:

INSERT INTO purchase (Source, Item, Qty)
SELECT
   1, totals.Item, r.Qty
FROM request r
JOIN (
  SELECT Item, SUM(Qty) AS TotQty
  FROM inventory
  GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty <= totals.TotQty
UNION ALL
SELECT
   0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
  SELECT Item, SUM(Qty) AS TotQty
  FROM inventory
  GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty > totals.TotQty;

cheers,

Jay

Re: Insert Select query problem

[Jay Pipes]

Ed Reed wrote:

Hi All,
 
I have an issue that I need to resolve that is difficult to explain. I

hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
 
I have three tables, inventory, which is a list of transactions with

positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
 
CREATE TABLE `inventory` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Item` varchar(100) NOT NULL DEFAULT '',
  `Qty` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; 
 
CREATE TABLE `purchase` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Source` int(11) DEFAULT NULL,
  `Item` varchar(100) NOT NULL DEFAULT '',
  `Qty` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
 
CREATE TABLE `request` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Required` varchar(100) NOT NULL DEFAULT '',
  `Qty` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 
 
My Inventory and Request tables have data in them like this,
 
Insert Into `inventory` (Item, Qty)
Values 
('Apples',5),

('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
 
Insert Into `request` (Required, Qty)

Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
 
Now what I*d like to do is create a single Insert Select query that

creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
 
+--+--+

| Item | Sum(Qty) |
+--+--+
| Apples   | 2|
| Bananas  | 86   |
| Cherries | 29   |
+--+--+
 
and based on the qty of items in my request I would like to have a

purchase table that looks like this,
 
++--+-+

| Source | Item | Qty |
++--+-+
| 1  | Apples   | 2   |
| 0  | Apples   | 10  |
| 1  | Bananas  | 86  |
| 0  | Bananas  | 26  |
| 1  | Cherries | 5   |
++--+-+
 
with a source of 1 meaning pull the items from inventory and a source

of 0 means purchase them from somewhere else.
 
Can anyone help me with this?


Try this:

INSERT INTO purchase (Source, Item, Qty)
SELECT
  1, totals.Item, r.Qty
FROM request r
JOIN (
 SELECT Item, SUM(Qty) AS TotQty
 FROM inventory
 GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty <= totals.TotQty
UNION ALL
SELECT
  0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
 SELECT Item, SUM(Qty) AS TotQty
 FROM inventory
 GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty > totals.TotQty;

cheers,

Jay

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

Insert Select query problem

[Ed Reed]

Hi All,
 
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
 
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
 
CREATE TABLE `inventory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Item` varchar(100) NOT NULL DEFAULT '',
  `Qty` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; 
 
CREATE TABLE `purchase` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Source` int(11) DEFAULT NULL,
  `Item` varchar(100) NOT NULL DEFAULT '',
  `Qty` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
 
CREATE TABLE `request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Required` varchar(100) NOT NULL DEFAULT '',
  `Qty` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 
 
My Inventory and Request tables have data in them like this,
 
Insert Into `inventory` (Item, Qty)
Values 
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
 
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
 
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
 
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples   | 2|
| Bananas  | 86   |
| Cherries | 29   |
+--+--+
 
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
 
++--+-+
| Source | Item | Qty |
++--+-+
| 1  | Apples   | 2   |
| 0  | Apples   | 10  |
| 1  | Bananas  | 86  |
| 0  | Bananas  | 26  |
| 1  | Cherries | 5   |
++--+-+
 
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
 
Can anyone help me with this?
 
Thanks

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]

Query problem

[ross]

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. 


Thanks,

CREATE TABLE `images` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `property_id` varchar(10) default NULL,
  `name` varchar(30) NOT NULL default '',
  `type` varchar(30) NOT NULL default '',
  `size` int(11) NOT NULL default '0',
  `position` int(10) unsigned NOT NULL default '0',
  `title` varchar(100) NOT NULL,
  `img_url` varchar(200) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=264 ;



CREATE TABLE `properties` (
  `property_id` varchar(20) NOT NULL,
  `postcode` varchar(20) default NULL,
  `address` varchar(200) default NULL,
  `short_desc` varchar(500) default NULL,
  `long_desc` varchar(500) default NULL,
  `latitude` double(100,20) default NULL,
  `longitude` double(100,20) default NULL,
  `rent` varchar(50) default NULL,
  `available_from` date default NULL,
  `rent_type` varchar(255) default NULL,
  `double_rooms` int(2) default NULL,
  `single_rooms` int(2) default NULL,
  `twin_rooms` int(2) default NULL,
  `additional_rooms` varchar(500) default 'on',
  `features` varchar(500) default NULL,
  `status` enum('off','on') default 'on',
  PRIMARY KEY  (`property_id`)
) 
 t: 0131 553 3935 | m:07816 996 930 | [EMAIL PROTECTED] | 
http://www:blue-fly.co.uk

Re: Slow seach - Possible better query PROBLEM SOLVED

[Albert Padley]

Dan,

Actually you were on the right track. I changed your suggested query  
to the following and it seems to work and is a lot quicker.


SELECT id, subject, updated FROM mrldisc WHERE updated > SUBDATE(NOW 
(), INTERVAL 48 HOUR) AND mainthread = 'T' ORDER BY updated DESC   
LIMIT 50


Thanks.

Al


On Oct 31, 2006, at 4:01 PM, Albert Padley wrote:


Dan,


On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote:


Albert, it seems like the first query could be simplified, like so:

SELECT id, subject, updated FROM mrldisc
WHERE updated > SUBDATE(NOW(), INTERVAL 48 HOUR)
ORDER BY updated DESC  LIMIT 50


This query won't work. The table contains threaded messages. The  
thread_id refers back to the id of the first message in the thread.  
The above query would return a list of all messages in the last 48  
hours when all I want is to return the original message in the thread.




This might help it hit the index you've created on the UPDATED  
column.

I know there have been bugs here and there with the optimizer and IN
subqueries not hitting indices, especially in earlier 4.1.x releases.

If you have a large number of rows with recent values for the UPDATED
column, MySQL may be doing a table scan.  This is an instance where
test/development scenarios don't always work quite as well as real
data.  However, 6-12 seconds for a 5000 row table does seem slow ...


In our test database it's probably less than 50 rows that have been  
updated in the last 48 hours.




Can you post the output of EXPLAIN  ?  That will help us see
how MySQL is planning to run your query, and may also reveal a little
bit about your data.


1   PRIMARY mrldisc index   updated 4   4888Using 
where
2	DEPENDENT SUBQUERY	mrldisc	index_subquery	thread_id,updated	 
thread_id	5	func	8	Using index; Using where


Thanks.

Al




Thanks,
Dan


On 10/31/06, Albert Padley <[EMAIL PROTECTED]> wrote:

I have a query that works and returns the correct results. However,
it is very slow ( 6-12 seconds on 5000 row table). Since this table
will grow to several hundred thousand rows very shortly, I am  
worried.


Here is the query that works:

SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT
thread_id FROM mrldisc WHERE updated > SUBDATE(NOW(), INTERVAL 48
HOUR))) ORDER BY updated DESC  LIMIT 50

Here is the table schema:

CREATE TABLE `mrldisc` (
   `id` int(14) NOT NULL auto_increment,
   `thread_id` int(14) default NULL,
   `author` varchar(100) NOT NULL default '',
   `state` varchar(25) NOT NULL default '',
   `subject` varchar(100) NOT NULL default '',
   `message` longtext NOT NULL,
   `date` varchar(50) NOT NULL default '',
   `jdate` varchar(50) default '-00-00 00:00:00',
   `statespecific` varchar(25) NOT NULL default '',
   `mainthread` char(1) NOT NULL default '',
   `email` varchar(100) NOT NULL default '',
   `threadcount` int(11) NOT NULL default '0',
   `updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
   `ip_address` varchar(15) NOT NULL default '',
   PRIMARY KEY  (`id`),
   KEY `thread_id` (`thread_id`),
   KEY `statespecific` (`statespecific`),
   KEY `state` (`state`),
   KEY `updated` (`updated`),
   KEY `email` (`email`),
   KEY `mainthread` (`mainthread`),
   KEY `jdate` (`jdate`),
   FULLTEXT KEY `author` (`author`),
   FULLTEXT KEY `message` (`message`),
   FULLTEXT KEY `subject` (`subject`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I have tried other queries like:

SELECT DISTINCT * FROM mrldisc WHERE updated > SUBDATE(NOW(),
INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50

This one was close, but returned the oldest row in the group rather
than the most recent row.

I've also thought about doing the search with a temporary table, but
haven't gotten very far with that.

Any pointers would be greatly appreciated.

Thanks.

Al Padley





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





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[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 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: AW: Query problem

[John Meyer]

André Hänsel wrote:
>> -Ursprüngliche Nachricht-
>> Von: Miles Thompson [mailto:[EMAIL PROTECTED] 
>> Gesendet: Donnerstag, 3. August 2006 21:56
>> An: mysql@lists.mysql.com
>> Betreff: Re: Query problem
>>
>> At 03:08 PM 8/3/2006, André Hänsel wrote:
>>
>>> I have a table logging downloads (time, username, download).
>>>
>>> Now I'd like to have the last 5 downloads per user.
>>>
>> 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.
> 
> Assuming I have a (temporary) table of usernames, how can that be of any
> help?
> 
> 
all right, here's how this goes.

Create a stored procedure
WHERE YOU SELECT DISTINCT username.
Then for each user,
retrieve the five 5.
Put them in a union,
enjoy.

-- 
John Meyer
http://pueblonative.wordpress.com
http://pueblonative.110mb.com/board

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

AW: Query problem

[André Hänsel]

> -Ursprüngliche Nachricht-
> Von: Miles Thompson [mailto:[EMAIL PROTECTED] 
> Gesendet: Donnerstag, 3. August 2006 21:56
> An: mysql@lists.mysql.com
> Betreff: Re: Query problem
> 
> At 03:08 PM 8/3/2006, André Hänsel wrote:
> 
> >I have a table logging downloads (time, username, download).
> >
> >Now I'd like to have the last 5 downloads per user.
> >
> 
> 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.

Assuming I have a (temporary) table of usernames, how can that be of any
help?


--
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: AW: Query problem

[John Meyer]

SELECT DISTINCT username, time, download
FROM table
 ORDER BY time DESC
GROUP BY username

André Hänsel 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. :)
> 
> Regards,
> André
> 
>> -Ursprüngliche Nachricht-
>> Von: Dan Buettner [mailto:[EMAIL PROTECTED] 
>> Gesendet: Donnerstag, 3. August 2006 20:15
>> An: André Hänsel
>> Cc: mysql@lists.mysql.com
>> Betreff: Re: Query problem
>>
>> 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]
>>
> 
> 


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

AW: Query problem

[André Hänsel]

Hi Dan, hi Obed,

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

Regards,
André

> -Ursprüngliche Nachricht-
> Von: Dan Buettner [mailto:[EMAIL PROTECTED] 
> Gesendet: Donnerstag, 3. August 2006 20:15
> An: André Hänsel
> Cc: mysql@lists.mysql.com
> Betreff: Re: Query problem
> 
> 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]
> 


--
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]

Query problem

[André Hänsel]

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]

Re: Select query problem

[Dan Bolser]

Barry wrote:

Nenad Bosanac schrieb:


Hi I have one problem that i can`t resolve.



still need advice or is it solved?




IF!!! you need IF!! :)

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

Re: Select query problem

[Barry]

Nenad Bosanac schrieb:
Hi 
I have one problem that i can`t resolve.


still need advice or is it solved?


--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

Select query problem

[Nenad Bosanac]

Hi 
I have one problem that i can`t resolve.
I have 3 tables

TABLE `predmet` (
  `PredmetID` int(10) unsigned NOT NULL
auto_increment,
  `BrojPredmeta` int(10) unsigned NOT NULL default
'0',
  `VrstaPredmetaID` int(10) unsigned NOT NULL default
'0',
  `KorisnikID` int(10) unsigned NOT NULL default '0',
  `GrupaID` int(10) unsigned NOT NULL default '0',
  `PodgrupaID` int(10) unsigned NOT NULL default '0',
  `DatumZaduzenja` date NOT NULL default '-00-00',
  `DatumRazduzenja` date default NULL,
  `DatumUrgencije` date default NULL,
  `Komentar` text,

TABLE `predmet_referent` (
  `PredmetID` int(10) unsigned NOT NULL default '0',
  `VrstaPredmetaID` int(10) unsigned NOT NULL default
'0',
  `KorisnikID` int(10) unsigned NOT NULL default '0',
  `GrupaID` int(10) unsigned NOT NULL default '0',
  `PodgrupaID` int(10) unsigned NOT NULL default '0',
  `ReferentID` int(10) unsigned NOT NULL default '0',

which reference all from table predmet
and 

CREATE TABLE `referent` (
  `ReferentID` int(10) unsigned NOT NULL
auto_increment,
  `SifraReferenta` varchar(2) NOT NULL default '',
  `ImeReferenta` varchar(30) NOT NULL default '',
  `PrezimeReferenta` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`ReferentID`)

My table predmet_referent looks like this

PredmetID VrstaPredmetaID KorisnikID GrupaID
PodgrupaID ReferentID
1, 1, 18, 4, 4, 1
1, 1, 18, 4, 4, 2
2, 1, 21, 6, 3, 2
2, 1, 21, 6, 3, 3
3, 1, 22, 5, 1, 2
3, 1, 22, 5, 1, 1
3, 1, 22, 5, 1, 3
4, 3, 23, 6, 3, 1
4, 3, 23, 6, 3, 2
4, 3, 23, 6, 3, 3

and table referent looks like

ReferentID SifraReferenta ImeReferenta
PrezimeReferenta
1, '01', 'Nada', 'Nadić'
2, '03', 'Goran', 'Gavrančić'
3, '04', 'Dragan', 'Perišić'

I want to make select query so thatt result from that 
query look something like this

PredmetID BrojPredmeta Referent1 Referent2 Referent3
1  121215  12  
2  121355  23
3  236564  21 3
4  213545  12 3

How can i do this?This is very importat for me
Any help will be great.  

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.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

[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]

Query problem

[John Meyer]

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.


--
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]

Query problem: UNION in subquery

[Luke]

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]

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]

query problem

[ross]

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 

query problem

[ross]

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]

query problem

[Conor McTernan]

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: Interesting Query Problem

[Marco Neves]

Hi,

An alternative for any MySQL version (from 3.23.??) would be:

SELECT r1.question_id,count(r1.member_id)
FROM Records r1
LEFT JOIN Records r2 ON r1.question_id=r2.question_id
AND r2.member_id=
WHERE r2.question_id IS NULL;

 must be the member name.

mpneves

On Thursday 19 January 2006 11:18, Gleb Paharenko wrote:
> Hello.
>
> Perhaps this will work (depends on the version of MySQL you're using):
>
> select question_id
>   , count(*)
> from Records
> group by question_id
> having question_id not in (
>   select distinct question_id
>   from Records r
>   where member_id = @current_member_id);
>
> @current_member_id equals to current_user
>
> G G wrote:
> > Hello,
> >
> > I have a simple Records table with two columns, member_id and
> > question_id.
> >
> >
> >
> > The object of the query is to retrieve the question_id, as well as how
> > many times it's been answered - as long as the current user hasn't
> > answered it (member_id).  So, the query shouldn't return any
> > question_id's (and counts) if it has been answered by the current user.
> >
> >
> >
> >  Right now I have this:
> >
> > SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY
> > question_id;
> >
> >
> >
> > I've tried throwing in different variants of 'WHERE member_id != X', but
> > all that seems to return is the count of questions answered, minus the
> > amount of times the particular user has answered them.  For example, if
> > user X has answered a question that had been answered another 50 times,
> > my query will still return that question_id, but with a count of 49.
> >
> >
> >
> > Your help is appreciated in advance.  Thanks!
> >
> >
> >
> >
> >
> > Kind Regards,
> >
> > Gerald Glickman
> >
> >
> >
> > G2 Innovations.com, Inc.
> >
> > http://www.g2innovations.com 
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
><___/   www.mysql.com

-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

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

Re: Interesting Query Problem

[Gleb Paharenko]

Hello.

Perhaps this will work (depends on the version of MySQL you're using):

select question_id
, count(*)
from Records
group by question_id
having question_id not in (
select distinct question_id
from Records r
where member_id = @current_member_id);

@current_member_id equals to current_user

G G wrote:
> Hello,
> 
> I have a simple Records table with two columns, member_id and question_id.  
> 
>  
> 
> The object of the query is to retrieve the question_id, as well as how many
> times it's been answered - as long as the current user hasn't answered it
> (member_id).  So, the query shouldn't return any question_id's (and counts)
> if it has been answered by the current user.
> 
>  
> 
>  Right now I have this:
> 
> SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY
> question_id;
> 
>  
> 
> I've tried throwing in different variants of 'WHERE member_id != X', but all
> that seems to return is the count of questions answered, minus the amount of
> times the particular user has answered them.  For example, if user X has
> answered a question that had been answered another 50 times, my query will
> still return that question_id, but with a count of 49.
> 
>  
> 
> Your help is appreciated in advance.  Thanks!
> 
>  
> 
>  
> 
> Kind Regards,
> 
> Gerald Glickman
> 
>  
> 
> G2 Innovations.com, Inc.
> 
> http://www.g2innovations.com  
> 
>  
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

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

Interesting Query Problem

[G G]

Hello,

I have a simple Records table with two columns, member_id and question_id.  

 

The object of the query is to retrieve the question_id, as well as how many
times it's been answered - as long as the current user hasn't answered it
(member_id).  So, the query shouldn't return any question_id's (and counts)
if it has been answered by the current user.

 

 Right now I have this:

SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY
question_id;

 

I've tried throwing in different variants of 'WHERE member_id != X', but all
that seems to return is the count of questions answered, minus the amount of
times the particular user has answered them.  For example, if user X has
answered a question that had been answered another 50 times, my query will
still return that question_id, but with a count of 49.

 

Your help is appreciated in advance.  Thanks!

 

 

Kind Regards,

Gerald Glickman

 

G2 Innovations.com, Inc.

http://www.g2innovations.com  

 

Re: Delete query problem

[Dušan Pavlica]

Tomas,
thanks that was the solution I couldn't find out.
I had to change only ON clause to master_tbl.ID=detail_tbl.Master_ID .

Thanks also to Felix, I know I could use referential integrity but I knew 
there is a way how to do it without it and I was trying to find that way.


Dusan.

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

To: 
Sent: Wednesday, December 21, 2005 11:12 AM
Subject: Re: Delete query problem



What about

DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON 
master_tbl.ID=detail_tbl.ID WHERE .


T.R.


Dušan Pavlica napsal(a):

Hello, I have master-detail tables and I would like to delete in one 
statement master record and all detail records but not every master record 
has details. MySQL versions 4.1.10 and higher.

Could someone help me, please, to create such a query?

Example:
CREATE TABLE  `master_tbl` (
 `ID` int(10) unsigned NOT NULL auto_increment,
 `Desc` varchar(45) NOT NULL default '',
 `Data` varchar(45)  NOT NULL default '',
 PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;

CREATE TABLE  `detail_tbl` (
 `Master_ID` int(10) unsigned NOT NULL default '0',
 `ID` int(10) unsigned NOT NULL default '0',
 `Desc` varchar(45) NOT NULL default '',
 PRIMARY KEY  (`Master_ID`)
) ENGINE=InnoDB;

This query works only for master records which have at least one detail 
record:

DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl

WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10



Thanks in advance

Dusan Pavlica





--
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: Delete query problem

[Felix Geerinckx]

On 21/12/2005, Dušan Pavlica wrote:

> Hello, 
> I have master-detail tables and I would like to delete in one
> statement master record and all detail records but not every master
> record has details.  MySQL versions 4.1.10 and higher.  Could someone
> help me, please, to create such a query?
> 
> Example:
> CREATE TABLE  `master_tbl` (
>   `ID` int(10) unsigned NOT NULL auto_increment,
>   `Desc` varchar(45) NOT NULL default '',
>   `Data` varchar(45)  NOT NULL default '',
>   PRIMARY KEY  (`ID`)
> ) ENGINE=InnoDB;
> 
> CREATE TABLE  `detail_tbl` (
>   `Master_ID` int(10) unsigned NOT NULL default '0',
>   `ID` int(10) unsigned NOT NULL default '0',
>   `Desc` varchar(45) NOT NULL default '',
>   PRIMARY KEY  (`Master_ID`)
> ) ENGINE=InnoDB;

You realize you can only have one detail record per Master_ID (since
you made Master_ID the PK in the detail table)?
 
Since you are using InnoDB, you should enforce referential integrity
with referential actions:

CREATE TABLE  `detail_tbl` (
   `Master_ID` int(10) unsigned NOT NULL default '0',
   `ID` int(10) unsigned NOT NULL default '0',
   `Desc` varchar(45) NOT NULL default '',
   PRIMARY KEY  (`Master_ID`),
   FOREIGN KEY (`Master_ID`) REFERENCES `master_tbl` (`ID`) 
   ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

That way, you only have to

DELECT FROM master_tbl WHERE Master_ID = 10;

to delete both master and detail records. 

-- 
felix

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

Re: Delete query problem

[Tomas Rasek]

What about

DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON 
master_tbl.ID=detail_tbl.ID WHERE .


T.R.


Dušan Pavlica napsal(a):

Hello, 
I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. 
MySQL versions 4.1.10 and higher.

Could someone help me, please, to create such a query?

Example:
CREATE TABLE  `master_tbl` (
 `ID` int(10) unsigned NOT NULL auto_increment,
 `Desc` varchar(45) NOT NULL default '',
 `Data` varchar(45)  NOT NULL default '',
 PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;

CREATE TABLE  `detail_tbl` (
 `Master_ID` int(10) unsigned NOT NULL default '0',
 `ID` int(10) unsigned NOT NULL default '0',
 `Desc` varchar(45) NOT NULL default '',
 PRIMARY KEY  (`Master_ID`)
) ENGINE=InnoDB;

This query works only for master records which have at least one detail record:
DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl

WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10



Thanks in advance

Dusan Pavlica

 




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

Delete query problem

[Dušan Pavlica]

Hello, 
I have master-detail tables and I would like to delete in one statement master 
record and all detail records but not every master record has details. 
MySQL versions 4.1.10 and higher.
Could someone help me, please, to create such a query?

Example:
CREATE TABLE  `master_tbl` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Desc` varchar(45) NOT NULL default '',
  `Data` varchar(45)  NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;

CREATE TABLE  `detail_tbl` (
  `Master_ID` int(10) unsigned NOT NULL default '0',
  `ID` int(10) unsigned NOT NULL default '0',
  `Desc` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`Master_ID`)
) ENGINE=InnoDB;

This query works only for master records which have at least one detail record:
DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl

WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10



Thanks in advance

Dusan Pavlica

Re: Insert query problem

[Brent Baisley]

You can use the back quote(`) to escape the field name. It's the ~  
key on the keyboard.

tickets(`from`,`department`,...

If you type SHOW CREATE TABLE tickets, you'll see mysql escapes all  
the field names to avoid reserve word conflicts.


On Nov 29, 2005, at 2:14 PM, Rhino wrote:



- Original Message - From: "Jeff" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, November 29, 2005 11:42 AM
Subject: Insert query problem




All,

I can't get this query to run, it keeps compaining that there is a
problem:

The Query:

insert into
tickets 
(id,from,department,subject,body,lastaction,lastpost,priority,sta

tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
<[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')

The error:

You have an error in your SQL syntax near
'from,department,subject,body,lastaction,lastpost,priority,status,cre 
ate

d,fromnam' at line 1

I suspect it doesn't like the fact that I have a field named  
"from" but
I KNOW it's possible to write to this table and that field, I just  
can't
figure out the correct syntax to get it to accept the fieldname  
"from".



One of my least favourite aspects of MySQL is the vague error  
messages, like the one you quote in your post. I keep hoping that  
they will make them much clearer but it doesn't seem to have  
happened yet


In any case, you may well be right that MySQL doesn't like you  
having a column named 'from'. Most dialects of SQL tolerate that  
kind of thing but usually require escape characters of some kind  
around words like 'from', which are keywords, when they are used  
outside of their normal purpose. I've never had the desire to call  
a column 'from' so I've never learned the escape character  
technique for MySQL so I would suggest the following possibilities:


- change your column name so that _isn't_ a keyword. That would be  
my first choice if it was my database.
- search the MySQL manual for 'escape character' and/or 'keyword';  
with a bit of luck, it is documented there somewhere
- search the MySQL mailing list archives. I'm virtually positive  
I've seen the matter come up in the list but I just don't remember  
the escape syntax at the moment.


If you escape the column named 'from' and the query still doesn't  
work, please repost so that we can investigate other possibilities.


Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.10/186 - Release Date:  
29/11/2005



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






--
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: Insert query problem

[Rhino]

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

To: 
Sent: Tuesday, November 29, 2005 11:42 AM
Subject: Insert query problem



All,

I can't get this query to run, it keeps compaining that there is a
problem:

The Query:

insert into
tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
<[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')

The error:

You have an error in your SQL syntax near
'from,department,subject,body,lastaction,lastpost,priority,status,create
d,fromnam' at line 1

I suspect it doesn't like the fact that I have a field named "from" but
I KNOW it's possible to write to this table and that field, I just can't
figure out the correct syntax to get it to accept the fieldname "from".

One of my least favourite aspects of MySQL is the vague error messages, like 
the one you quote in your post. I keep hoping that they will make them much 
clearer but it doesn't seem to have happened yet


In any case, you may well be right that MySQL doesn't like you having a 
column named 'from'. Most dialects of SQL tolerate that kind of thing but 
usually require escape characters of some kind around words like 'from', 
which are keywords, when they are used outside of their normal purpose. I've 
never had the desire to call a column 'from' so I've never learned the 
escape character technique for MySQL so I would suggest the following 
possibilities:


- change your column name so that _isn't_ a keyword. That would be my first 
choice if it was my database.
- search the MySQL manual for 'escape character' and/or 'keyword'; with a 
bit of luck, it is documented there somewhere
- search the MySQL mailing list archives. I'm virtually positive I've seen 
the matter come up in the list but I just don't remember the escape syntax 
at the moment.


If you escape the column named 'from' and the query still doesn't work, 
please repost so that we can investigate other possibilities.


Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.10/186 - Release Date: 29/11/2005


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

RE: Insert query problem

[mel list_php]

You can use backticks ( ` ) to escape the names:
INSERT INTO `tickets` ( `id` , `from` , `departement` .

hth,
melanie


From: "Jeff" <[EMAIL PROTECTED]>
To: 
Subject: Insert query problem
Date: Tue, 29 Nov 2005 11:42:34 -0500

All,

I can't get this query to run, it keeps compaining that there is a
problem:

The Query:

insert into
tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
<[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')

The error:

You have an error in your SQL syntax near
'from,department,subject,body,lastaction,lastpost,priority,status,create
d,fromnam' at line 1

I suspect it doesn't like the fact that I have a field named "from" but
I KNOW it's possible to write to this table and that field, I just can't
figure out the correct syntax to get it to accept the fieldname "from".

Thanks,

Jeff



_
MSN Messenger 7.5 is now out. Download it for FREE here. 
http://messenger.msn.co.uk



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

RE: Insert query problem

[Easyhorpak.com Easyhorpak.com]

Many Many wrong SQL syntax error check your symbol (') becareful it 'very 
important .

You can do that.

Cheer!!



From: "Jeff" <[EMAIL PROTECTED]>
To: 
Subject: Insert query problem
Date: Tue, 29 Nov 2005 11:42:34 -0500

All,

I can't get this query to run, it keeps compaining that there is a
problem:

The Query:

insert into
tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
<[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')

The error:

You have an error in your SQL syntax near
'from,department,subject,body,lastaction,lastpost,priority,status,create
d,fromnam' at line 1

I suspect it doesn't like the fact that I have a field named "from" but
I KNOW it's possible to write to this table and that field, I just can't
figure out the correct syntax to get it to accept the fieldname "from".

Thanks,

Jeff



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




_
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: Insert query problem

[Kristen G. Thorson]

Jeff wrote:


tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
<[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')

 



Use `from`

(those are backticks)  See:

http://dev.mysql.com/doc/refman/5.0/en/legal-names.html


kgt


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

RE: Insert query problem [solved]

[Jeff]

Nevermind, found that usig `from` works.

Jeff

> -Original Message-
> From: Jeff [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 29, 2005 11:43
> To: mysql@lists.mysql.com
> Subject: Insert query problem
> 
> 
> All,
> 
> I can't get this query to run, it keeps compaining that there is a
> problem:
> 
> The Query:
> 
> insert into 
> tickets(id,from,department,subject,body,lastaction,lastpost,pr
> iority,sta
> tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
> values(null,'jmckeon','1','test','test
> test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
> <[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
> McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')
> 
> The error: 
> 
> You have an error in your SQL syntax near 
> 'from,department,subject,body,lastaction,lastpost,priority,sta
> tus,create
> d,fromnam' at line 1
> 
> I suspect it doesn't like the fact that I have a field named 
> "from" but I KNOW it's possible to write to this table and 
> that field, I just can't figure out the correct syntax to get 
> it to accept the fieldname "from".
> 
> Thanks,
> 
> Jeff
> 
> 
> 
> -- 
> 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 



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

Re: Insert query problem

[SGreen]

"Jeff" <[EMAIL PROTECTED]> wrote on 11/29/2005 11:42:34 AM:

> All,
> 
> I can't get this query to run, it keeps compaining that there is a
> problem:
> 
> The Query:
> 
> insert into
> tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
> tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
> values(null,'jmckeon','1','test','test
> test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
> <[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
> McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')
> 
> The error: 
> 
> You have an error in your SQL syntax near
> 'from,department,subject,body,lastaction,lastpost,priority,status,create
> d,fromnam' at line 1
> 
> I suspect it doesn't like the fact that I have a field named "from" but
> I KNOW it's possible to write to this table and that field, I just can't
> figure out the correct syntax to get it to accept the fieldname "from".
> 
> Thanks,
> 
> Jeff
> 
> 

You have to use backticks: `from`
http://dev.mysql.com/doc/refman/4.1/en/legal-names.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Insert query problem

[Jeff]

All,

I can't get this query to run, it keeps compaining that there is a
problem:

The Query:

insert into
tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta
tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto)
values(null,'jmckeon','1','test','test
test',unix_timestamp(now()),'"[EMAIL PROTECTED]"
<[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff
McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]')

The error: 

You have an error in your SQL syntax near
'from,department,subject,body,lastaction,lastpost,priority,status,create
d,fromnam' at line 1

I suspect it doesn't like the fact that I have a field named "from" but
I KNOW it's possible to write to this table and that field, I just can't
figure out the correct syntax to get it to accept the fieldname "from".

Thanks,

Jeff



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

Re: Lost connection to MySQL server during query problem

[Gleb Paharenko]

Hello.



You should solve the issue with server crashes. I'm not familiar with JDBC,

and if changing the values of parameters with SET statement doesn't work

with JDBC, probably somebody clever on the list could help you. You may

forward your message to:



  http://lists.mysql.com/java









>Yes the server dies during the queries from JDBC.

> 

>   Regarding the client parameters, what if the client doesn't have mySQL

>   installed?

>

>Amir







  

"Amir Shay" <[EMAIL PROTECTED]> wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: Lost connection to MySQL server during query problem

[Amir Shay]

 
Hi,
 
Yes the server dies during the queries from JDBC.
 
Regarding the client parameters, what if the client doesn't have mySQL
installed?
 
Amir
 
 
Hello.
 
Does SHOW STATUS executed from JDBC client return increased values of
this parameters? Could your server die during queries from JDBC (check
the error log)?
Sometimes you should increase interactive_timeout as well. A lot of
variables usually
could be changed using SET statement. See:
  http://dev.mysql.com/doc/mysql/en/system-variables.html
 
 
>Sometimes when querying mySQL 4.1.11 on Linux machine I get the error
>"Lost connection to MySQL server during query". Here are the symptoms
> 
> 
> 
> 1.When running the query from the server it returns OK
> 2.When running the query from another machine using the mySQL
> query browser it returns OK
> 3.When running the query from another machine from Java using
> mySQL JDBC it fails with " Lost connection to..."
> 4.When running the query from another machine using software like
> EMS it fails with " Lost connection to..."
> 5.The error is always after 4.7 seconds and only in "heavy"
> queries (not only SELECT, even DELETES )
> 
> The problem is probably not in the communication parameters, like
> max_allowed_packet or connection_timeout on the server because I
> increased then 
> 
> 
>   Does anyone know how can I control those parameters from JDBC?
> 
 
 
"Amir Shay" <[EMAIL PROTECTED]> wrote:
 
 
-- 
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

 

Re: Lost connection to MySQL server during query problem

[Gleb Paharenko]

Hello.



Does SHOW STATUS executed from JDBC client return increased values of

this parameters? Could your server die during queries from JDBC (check the 
error log)? Sometimes you should increase interactive_timeout as well. A lot of 
variables usually could be changed using SET statement. See:

  http://dev.mysql.com/doc/mysql/en/system-variables.html





>Sometimes when querying mySQL 4.1.11 on Linux machine I get the error

>"Lost connection to MySQL server during query". Here are the symptoms

>

> 

>

> 1.When running the query from the server it returns OK

> 2.When running the query from another machine using the mySQL

> query browser it returns OK

> 3.When running the query from another machine from Java using

> mySQL JDBC it fails with " Lost connection to..."

> 4.When running the query from another machine using software like

> EMS it fails with " Lost connection to..."

> 5.The error is always after 4.7 seconds and only in "heavy"

> queries (not only SELECT, even DELETES )

>

> The problem is probably not in the communication parameters, like

> max_allowed_packet or connection_timeout on the server because I

> increased then 

>

>

>   Does anyone know how can I control those parameters from JDBC?

>





"Amir Shay" <[EMAIL PROTECTED]> wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Lost connection to MySQL server during query problem

[Amir Shay]

Hi,

 

Sometimes when querying mySQL 4.1.11 on Linux machine I get the error
"Lost connection to MySQL server during query". Here are the symptoms

 

1.  When running the query from the server it returns OK
2.  When running the query from another machine using the mySQL
query browser it returns OK
3.  When running the query from another machine from Java using
mySQL JDBC it fails with " Lost connection to..."
4.  When running the query from another machine using software like
EMS it fails with " Lost connection to..."
5.  The error is always after 4.7 seconds and only in "heavy"
queries (not only SELECT, even DELETES )

The problem is probably not in the communication parameters, like
max_allowed_packet or connection_timeout on the server because I
increased then 

 

Does anyone know how can I control those parameters from JDBC?

 

Amir

 

 

Bug or query problem?

[Ryan A]

Hi,
This is driving me nuts, please tell me is this a bug or a problem wiht my
query:

jappz_guestbook(owner_cno,  sent_datetime,  is_secret,  accepted)
jappz_member_profile(cno, pic_name)




jappz_guestbook's owner_cno and jappz_member_profile's cno are the same

what i need to do is select * from jappz_guestbook where owner_cno=x and
only select pic_name from jappz_member_profile where cno=jappz_guestbook.cno


This is my query:
SELECT
from_cno,from_usernam,sent_datetime,is_secret,accepted,jappz_member_profile.
pic1 FROM jappz_guestbook
INNER JOIN jappz_member_profile ON jappz_guestbook.owner_cno =
jappz_member_profile.cno
 WHERE jappz_guestbook.owner_cno = jappz_member_profile.cno order by
from_cno DESC limit 0,30

I am getting 4 results:
from_cno  from_usernam  sent_datetime is_secret  accepted
pic1
1 r 2005-05-01 00:03:14 0
0 s.jpg
1 r 2005-05-01 00:04:09 1
0 s.jpg
3 ryana32005-05-01 01:15:57 0 0
s.jpg
3 ryana3 2005-05-01 01:16:061 0
s.jpg


which is wrong because "pic1" for "from_cno" should be "r.jpg" but its
somehow "cacheing" the above.

Please advise.

Thanks,
Ryan A




-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 4/29/2005


-- 
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]

Query problem

[Dto. Sistemas de Unitel]

Hi,

I’m trying to make a query that need
to search throught a table and have to search different terms, and SUM their
Rank (is for a small search engine I have to design), this is the table:

Table Indexes

ID – bigint 
ID_Termino -bigint
Rank – double

Table Terminos

ID_Termino – bigint – Primary key
Termino – Varchar(50)

Table productos

ID – bigint
prod_descripcion – varchar(255)

 

I have another engine that search throught text and
apply a Rank to each word and store it on table indexes and if this word doesn’t
exist it store it in table terminus and the id of this word is related in indexes
table. Indexes is related with another table called productos that is the one that
is analyzed by my engine. When any client ask to me for search “computer intel”
y search with the next query, but the results aren’t 100% good because I have
to use a clause OR in termino, but I want to use there something like an and (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). 

SELECT STRAIGHT_JOIN

terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino,s

um(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

Thanks for all and best regards,

Roberto

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]

Query Problem

[Xristos Karvouneas]

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]

Re: Multi-Table Query Problem...

[SGreen]

"Nick Zukin" <[EMAIL PROTECTED]> wrote on 03/14/2005 05:22:38 PM:

> I'm trying to do a multitable query and am having problems.
> 
> I have three tables: vendors, products, and vendorproducts. The
> vendorproducts table creates a many to many relationship between the 
vendors
> and the products. There is nothing more than the vendor and product ids 
in
> the vendorproducts table.
> 
> I want to be able to create a query that will find vendors who have 
certain
> products. However, I'm trying to make a keyword search (PHP/MySQL) so 
that
> using form data I can search multiple columns for the same keyword. 
Here's
> how I am currently doing the query:
> 
> $query  = "SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate,
> v.vendorid ";
> $query .= "FROM vendorproducts AS vp ";
> $query .= "INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ";
> $query .= "INNER JOIN products AS p ON vp.vpvendorid = p.productid ";
> $query .= "WHERE (p.productname LIKE '%".$_GET['keyword']."%') ";
> $query .= "OR (p.productfamily LIKE '%".$_GET['keyword']."%') ";
> $query .= "OR (v.vcategory LIKE '%".$_GET['keyword']."%') ";
> $query .= "GROUP BY v.vbusiness ";
> 
> As an example, it might look like this:
> 
> SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid
> FROM vendorproducts AS vp
> INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid
> INNER JOIN products AS p ON vp.vpvendorid = p.productid
> WHERE (p.productname LIKE '%Apples%')
> OR (p.productfamily LIKE '%Apples%')
> OR (v.vcategory LIKE '%Apples%')
> GROUP BY v.vbusiness
> 
> Where am I going wrong? The results aren't random, but I can't see how
> they're coming up with what they're coming up with.
> 
> TIA,
> 
> Nick
> 
> 
> 


You are "GROUPING" when you shouldn't. You only need to GROUP whenever you 
want to perform some kind of aggregate function (AVG, SUM, COUNT, etc). 
What you see is a pseudo-random result from all of the possible results 
that meet your criteria. Drop your GROUP BY clause and your results should 
come back into line.

If you were trying to eliminate duplicate rows, you might want to try 
SELECT DISTINCT.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Multi-Table Query Problem...SOLVED

[Nick Zukin]

Thanks.  That was it.  I'm glad it was something so simple.  Just needed a
careful set of eyes.  I was worried my understanding of SQL/JOINs was
screwy.

Thanks again.

Nick

PS I'll read up on the FULLTEXT matching.  I don't know it well.

>> $query  = "SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate,
>> v.vendorid ";
>> $query .= "FROM vendorproducts AS vp ";
>> $query .= "INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ";
>> $query .= "INNER JOIN products AS p ON vp.vpvendorid = p.productid ";
 ^^
>I'd guess the problem is right there.  Shouldn't that be something like

>   INNER JOIN products AS p ON vp.vpproductid = p.productid

>instead?




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

Re: Multi-Table Query Problem...

[Michael Stassen]

Nick Zukin wrote:
I'm trying to do a multitable query and am having problems.
I have three tables: vendors, products, and vendorproducts. The
vendorproducts table creates a many to many relationship between the vendors
and the products. There is nothing more than the vendor and product ids in
the vendorproducts table.
I want to be able to create a query that will find vendors who have certain
products. However, I'm trying to make a keyword search (PHP/MySQL) so that
using form data I can search multiple columns for the same keyword. Here's
how I am currently doing the query:
$query  = "SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate,
v.vendorid ";
$query .= "FROM vendorproducts AS vp ";
$query .= "INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ";
$query .= "INNER JOIN products AS p ON vp.vpvendorid = p.productid ";
^^
I'd guess the problem is right there.  Shouldn't that be something like
  INNER JOIN products AS p ON vp.vpproductid = p.productid
instead?
$query .= "WHERE (p.productname LIKE '%".$_GET['keyword']."%') ";
$query .= "OR (p.productfamily LIKE '%".$_GET['keyword']."%') ";
$query .= "OR (v.vcategory LIKE '%".$_GET['keyword']."%') ";
$query .= "GROUP BY v.vbusiness ";
As an example, it might look like this:
SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid
FROM vendorproducts AS vp
INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid
INNER JOIN products AS p ON vp.vpvendorid = p.productid
WHERE (p.productname LIKE '%Apples%')
OR (p.productfamily LIKE '%Apples%')
OR (v.vcategory LIKE '%Apples%')
GROUP BY v.vbusiness
Where am I going wrong? The results aren't random, but I can't see how
they're coming up with what they're coming up with.
TIA,
Nick
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Multi-Table Query Problem...

[Oropeza Querejeta, Alejandro]

Why don't you use Fulltext indexes, they are very fast and you use
several columns.

http://dev.mysql.com/doc/mysql/en/fulltext-search.html

Best regards

Alejandro 

-Mensaje original-
De: nick [mailto:[EMAIL PROTECTED] 
Enviado el: Lunes, 14 de Marzo de 2005 05:59 p.m.
Para: Mysql List
Asunto: Re: Multi-Table Query Problem...

On Mon, 14 Mar 2005 15:12:18 -0800, Scott Klarenbach wrote
> Because, with the '%keyword%' operator, you're going to match any of 
> those columns that contain the keyword inside of it.  This can be a 
> little confusing as 'ef' will return true on 'abcdefghijk'?  Instead, 
> you might try 'keyword%' so that 'apple' returns true for 'apples', 
> 'apple juice', 'apple cider', BUT returns false for 'ple'.
> 
> But, without seeing the results and what you'd hoped them to be, it's 
> tough to narrow down the problem.
> 
> sk


Yes, I understand, but that's not what I'm getting.  I'm getting rows
returned where "apples" is not present in any of the columns in any way.
I think it's a syntax mistake in my query, but I can't see it.

Nick

--
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: Multi-Table Query Problem...

[nick]

On Mon, 14 Mar 2005 15:12:18 -0800, Scott Klarenbach wrote
> Because, with the '%keyword%' operator, you're going to match any of
> those columns that contain the keyword inside of it.  This can be a
> little confusing as 'ef' will return true on 'abcdefghijk'?  Instead,
> you might try 'keyword%' so that 'apple' returns true for 'apples',
> 'apple juice', 'apple cider', BUT returns false for 'ple'.
> 
> But, without seeing the results and what you'd hoped them to be, it's
> tough to narrow down the problem.
> 
> sk


Yes, I understand, but that's not what I'm getting.  I'm getting rows 
returned where "apples" is not present in any of the columns in any way.  I 
think it's a syntax mistake in my query, but I can't see it.

Nick

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