for the mySQL gurus...

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

for the mySQL gurus...

Rick Dieringer-2
I'm working on a sql statement that fails in a very strange way.
When I do this:
select * from database.matches where sku = '02_SM11'
I get a list of matching records.
When I do this:
select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'
no records are returned.
If the search is on any other sku (other than those starting with 02), I get a list of matching records.
At first I thought the underscore was my problem, but replacing it with an alpha character has no effect.
Yes, there is a record for 02_SM11 in the products table.

Anybody have any thoughts on what's happening here?


#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: for the mySQL gurus...

Bil Corry-3
In the first query, you're searching matches.sku, in the second, matches.related.  They're two different fields, so you'll get two different results.

- Bil

On Apr 11, 2013, at 6:07 PM, Rick Dieringer <[hidden email]> wrote:

> I'm working on a sql statement that fails in a very strange way.
> When I do this:
> select * from database.matches where sku = '02_SM11'
> I get a list of matching records.
> When I do this:
> select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'
> no records are returned.
> If the search is on any other sku (other than those starting with 02), I get a list of matching records.
> At first I thought the underscore was my problem, but replacing it with an alpha character has no effect.
> Yes, there is a record for 02_SM11 in the products table.
>
> Anybody have any thoughts on what's happening here?
>
>
> #############################################################
>
> Attend the Lasso Developer Conference 2013!
> Sept 12-14, 2013 in Niagara Falls, Canada
> http://www.lassosoft.com/LDC-niagara-falls-2013
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: for the mySQL gurus...

John Morris-3
In reply to this post by Rick Dieringer-2
Are you sure that field matches.related has the value of 02_SM11?

 - John Morris

On Apr 11, 2013, at 11:07 AM, Rick Dieringer <[hidden email]> wrote:

> I'm working on a sql statement that fails in a very strange way.
> When I do this:
> select * from database.matches where sku = '02_SM11'
> I get a list of matching records.
> When I do this:
> select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'
> no records are returned.
> If the search is on any other sku (other than those starting with 02), I get a list of matching records.
> At first I thought the underscore was my problem, but replacing it with an alpha character has no effect.
> Yes, there is a record for 02_SM11 in the products table.
>
> Anybody have any thoughts on what's happening here?
>
>
> #############################################################
>
> Attend the Lasso Developer Conference 2013!
> Sept 12-14, 2013 in Niagara Falls, Canada
> http://www.lassosoft.com/LDC-niagara-falls-2013
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: for the mySQL gurus...

Tim Taplin
In reply to this post by Rick Dieringer-2
hmm, I dont know what your schema is, but:
On Apr 11, 2013, at 10:07 AM, Rick Dieringer <[hidden email]> wrote:

> I'm working on a sql statement that fails in a very strange way.
> When I do this:
> select * from database.matches where sku = '02_SM11'
here you're selecting from the table matches on field sku, right?

> I get a list of matching records.
> When I do this:
> select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'

here you're selecting from a join between tables products and matches using the fields sku and related. I dont know what the content of the field related is.

What happens if you do:

select * from database.products left join database.matches on products.sku = matches.sku and matches.related = '02_SM11'

I might try doing a straight select:

select products.sku, matches.sku, matches.related from database.products join database.matches on products.sku = matches.sku where matches.related LIKE '%SM11%'

or even without the where part.

I'm betting that either there's some issue with how 'related' is being filled in, or it has to do with the implicit inner join. Probably better to join on sku and use  a where statement to filter on related.

Tim Taplin

> no records are returned.
> If the search is on any other sku (other than those starting with 02), I get a list of matching records.
> At first I thought the underscore was my problem, but replacing it with an alpha character has no effect.
> Yes, there is a record for 02_SM11 in the products table.
>
> Anybody have any thoughts on what's happening here?
>
>
> #############################################################
>
> Attend the Lasso Developer Conference 2013!
> Sept 12-14, 2013 in Niagara Falls, Canada
> http://www.lassosoft.com/LDC-niagara-falls-2013
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: for the mySQL gurus...

Brad Lindsay
In reply to this post by Rick Dieringer-2
On 4/11/13 12:07 PM, Rick Dieringer wrote:
> I'm working on a sql statement that fails in a very strange way.
> When I do this:
> select * from database.matches where sku = '02_SM11'
> I get a list of matching records.
> When I do this:
> select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'
> no records are returned.

what's up with the "matches.related='02_SM11'"? did you mean for that to
be "matches.sku="? In other words, did you mean:

SELECT * FROM database.products INNER JOIN database.matches ON
products.sku=matches.sku AND matches.sku = '02_SM11'


Brad

#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: for the mySQL gurus...

Jim Leavitt-2
In reply to this post by John Morris-3
What about something like this?

select * from database.products
join
database.matches on products.sku = matches.sku
where
matches.sku = '02_SM11'

This assumes that '02_SM11' will exist in the sku field in both the products table and the matches table.


On 2013-04-11, at 12:18 PM, John Morris wrote:

> Are you sure that field matches.related has the value of 02_SM11?
>
> - John Morris
>
> On Apr 11, 2013, at 11:07 AM, Rick Dieringer <[hidden email]> wrote:
>
>> I'm working on a sql statement that fails in a very strange way.
>> When I do this:
>> select * from database.matches where sku = '02_SM11'
>> I get a list of matching records.
>> When I do this:
>> select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'
>> no records are returned.
>> If the search is on any other sku (other than those starting with 02), I get a list of matching records.
>> At first I thought the underscore was my problem, but replacing it with an alpha character has no effect.
>> Yes, there is a record for 02_SM11 in the products table.
>>
>> Anybody have any thoughts on what's happening here?
>>
>>
>> #############################################################
>>
>> Attend the Lasso Developer Conference 2013!
>> Sept 12-14, 2013 in Niagara Falls, Canada
>> http://www.lassosoft.com/LDC-niagara-falls-2013
>>
>> #############################################################
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso
>> [hidden email]
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
> #############################################################
>
> Attend the Lasso Developer Conference 2013!
> Sept 12-14, 2013 in Niagara Falls, Canada
> http://www.lassosoft.com/LDC-niagara-falls-2013
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: for the mySQL gurus...

Rick Dieringer-2
achh...
sometimes the trees are too close to see the forest.
The numbers in the 'related' column were missing the underscore
Thanks to all for shining light in those dark recesses of my mind.
Rick

On Apr 11, 2013, at 12:23 PM, Jim Leavitt wrote:

> What about something like this?
>
> select * from database.products
> join
> database.matches on products.sku = matches.sku
> where
> matches.sku = '02_SM11'
>
> This assumes that '02_SM11' will exist in the sku field in both the products table and the matches table.
>
>
> On 2013-04-11, at 12:18 PM, John Morris wrote:
>
>> Are you sure that field matches.related has the value of 02_SM11?
>>
>> - John Morris
>>
>> On Apr 11, 2013, at 11:07 AM, Rick Dieringer <[hidden email]> wrote:
>>
>>> I'm working on a sql statement that fails in a very strange way.
>>> When I do this:
>>> select * from database.matches where sku = '02_SM11'
>>> I get a list of matching records.
>>> When I do this:
>>> select * from database.products join database.matches on products.sku = matches.sku and matches.related = '02_SM11'
>>> no records are returned.
>>> If the search is on any other sku (other than those starting with 02), I get a list of matching records.
>>> At first I thought the underscore was my problem, but replacing it with an alpha character has no effect.
>>> Yes, there is a record for 02_SM11 in the products table.
>>>
>>> Anybody have any thoughts on what's happening here?
>>>
>>>
>>> #############################################################
>>>
>>> Attend the Lasso Developer Conference 2013!
>>> Sept 12-14, 2013 in Niagara Falls, Canada
>>> http://www.lassosoft.com/LDC-niagara-falls-2013
>>>
>>> #############################################################
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso
>>> [hidden email]
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>> #############################################################
>>
>> Attend the Lasso Developer Conference 2013!
>> Sept 12-14, 2013 in Niagara Falls, Canada
>> http://www.lassosoft.com/LDC-niagara-falls-2013
>>
>> #############################################################
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso
>> [hidden email]
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
> #############################################################
>
> Attend the Lasso Developer Conference 2013!
> Sept 12-14, 2013 in Niagara Falls, Canada
> http://www.lassosoft.com/LDC-niagara-falls-2013
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>