Unwanted Multiple Results with OR in SQL

Report · Apr 28, 2006

With the following SQL, I get two of every result. If I take the AND G.pid
LIKE '%MMColParam3%' OR P.ID LIKE '%MMColParam%' off, I get one of each but
I don't get all the results that I am searching for.

Any Ideas what is causing this?

SELECT P.ProdID, P.ID AS ProductID, P.Name, P.Price, P.ShipCost, PC.gid AS
PCgid, G.gid, G.pid AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
FROM dbo.Products P
INNER JOIN dbo.Groups G ON P.ID = G.pid
INNER JOIN dbo.PCat PC ON G.gid = PC.gid
INNER JOIN dbo.Description D ON G.pid = D.PID
WHERE P.Price IS NOT NULL AND D.Paragraph LIKE '%MMColParam3%' OR P.Name
LIKE '%MMColParam3%' AND G.pid LIKE '%MMColParam3%' OR P.ID LIKE
'%MMColParam%'

Report · Apr 28, 2006

Remember your order of operations. Your WHERE clause will be interpreted
like this:

(P.Price IS NOT NULL AND D.Paragraph LIKE '%MMColParam3%')
OR
(P.Name LIKE '%MMColParam3%' AND G.pid LIKE '%MMColParam3%')
OR
P.ID LIKE '%MMColParam%'

Is that what you intend?

Report · Apr 28, 2006

No, that gives me the wrong result. I don't understand what you are getting
at...sorry.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e2tqbs$g4m$1@forums.macromedia.com...
> Remember your order of operations. Your WHERE clause will be interpreted
> like this:
>
> (P.Price IS NOT NULL AND D.Paragraph LIKE '%MMColParam3%')
> OR
> (P.Name LIKE '%MMColParam3%' AND G.pid LIKE '%MMColParam3%')
> OR
> P.ID LIKE '%MMColParam%'
>
> Is that what you intend?
>

Report · Apr 28, 2006

This is a search page and I want to just search the (D.Paragraph OR P.Name
OR G.pid) anything that is like the variable '%MMColParam%'

"lee" <lfairban_nospam@amep.com> wrote in message
news:e2tsob$j8l$1@forums.macromedia.com...
> No, that gives me the wrong result. I don't understand what you are
> getting at...sorry.
>
>
> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
> news:e2tqbs$g4m$1@forums.macromedia.com...
>> Remember your order of operations. Your WHERE clause will be interpreted
>> like this:
>>
>> (P.Price IS NOT NULL AND D.Paragraph LIKE '%MMColParam3%')
>> OR
>> (P.Name LIKE '%MMColParam3%' AND G.pid LIKE '%MMColParam3%')
>> OR
>> P.ID LIKE '%MMColParam%'
>>
>> Is that what you intend?
>>
>
>

Report · Apr 28, 2006

"lee" <lfairban_nospam@amep.com> wrote in message
news:e2tt0r$jmk$1@forums.macromedia.com...
> This is a search page and I want to just search the (D.Paragraph OR
> P.Name OR G.pid) anything that is like the variable '%MMColParam%'
>

I've pointed you here before. I'm going to do it again. It's the best
thing I can do for you.
www.sqlcourse.com
www.sqlcourse2.com

It will take an hour or two of your time. That's it. You can't hide behind
the "artist" label and not learn the basics of the tools you're working
with.

Good luck.

Report · Apr 28, 2006

I appreciate your help but the links haven't helped much. You could send me
to a thousand sites to read their instructions and unless it meshes well
with my problem, I won't get it. I've tried. I've gotten books, I've gone
through sqlcourse. a couple times. I learn from is getting something to
work and seeing how it reacts.

I just spent the last hour looking up order of operations and found nothing
that would help me figure what is going on with this data but I could find
nothing that helped.

A huge part of this problem is that I inherited a product line of roughly
2500 products and the Data was not designed well, I feel. For one, the
'ID"s were things like t-56 which doesn't work very well.

I ask these questions, because there is so much that is going on behind in
the data and the basic course just doesn't get me where I need to be. The
SQLcourse doesn't explain how, exactly, things work when joining 4 other
tables with a left on one of them. It gives simple examples and then jumps
to the next topic. I don't see my data in the examples on the SQL sites. I
need more advanced stuff but I'm not able to spend as much time as I need to
learn it.

I can only work 08:00 till 20:00 so many days in a row.

It sounds like you're giving up on me, I understand.

You've been a great help and I appreciate it.

Thanks

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e2tuda$lcp$1@forums.macromedia.com...
>
> "lee" <lfairban_nospam@amep.com> wrote in message
> news:e2tt0r$jmk$1@forums.macromedia.com...
>> This is a search page and I want to just search the (D.Paragraph OR
>> P.Name OR G.pid) anything that is like the variable '%MMColParam%'
>>
>
> I've pointed you here before. I'm going to do it again. It's the best
> thing I can do for you.
> www.sqlcourse.com
> www.sqlcourse2.com
>
> It will take an hour or two of your time. That's it. You can't hide
> behind the "artist" label and not learn the basics of the tools you're
> working with.
>
> Good luck.
>

Report · May 01, 2006

"lee" <lfairban_nospam@amep.com> wrote in message
news:e2u2ch$q84$1@forums.macromedia.com...
>
> It sounds like you're giving up on me, I understand.
>
> You've been a great help and I appreciate it.
>
> Thanks
>
>

Hmm... you reacted without anger. Perhaps you are willing to learn.

But you have to understand my position, too. After going through those
courses, you should have a good grasp on boolean logic (unless the courses
aren't as good as I remember).

You can only use AND and OR on conditions, not values, meaning each one must
be a complete comparison. In your case, if you want to test three different
columns against the same parameter, you would write

WHERE D.Paragraph LIKE '%MMColParam%' OR P.Name LIKE '%MMColParam%' OR G.pid
LIKE '%MMColParam%'

What I needed you to learn and understand is that if you have other
conditions that need to also be true, parentheses are your friends. Suppose
you want results from a certain date range. You can't just add the
conditions into the list because in all likelihood, they'll be evaluated
differently than you expect (NOT comes first, then AND, then OR, with ties
going left to right - it's just easier to use parentheses and force the
proper order).

WHERE MyDate BETWEEN 'someDate' AND 'someOtherDate' AND (D.Paragraph LIKE
'%MMColParam%' OR P.Name LIKE '%MMColParam%' OR G.pid LIKE '%MMColParam%')

The simple set of parentheses makes sure that your rows have a particular
date range and that one of the three fields you're looking at matches your
search term.

Report · May 01, 2006

I got something worked out. In my PCat, I have multiples gids that are in
separate categories so it was probably showing two of the same Product if
there were multiple category listings.

WHERE P.Price IS NOT NULL AND (D.Paragraph LIKE '%MMColParam3%' OR P.Name
LIKE '%MMColParam3%' OR P.ID LIKE '%MMColParam%')

Thanks for not giving up...yet.

I tried the above statement. It didn't work
I came up with the same results. It just didn't make sense that some
displayed with "doubles" and some didn't

Below WORKED!

SELECT P.ProdID, P.ID AS ProductID, P.Name, P.Price, P.ShipCost, G.gid,
G.pid AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
FROM dbo.Products P INNER JOIN dbo.Groups G ON P.ID = G.pid INNER JOIN
dbo.Description D ON G.pid = D.PID
WHERE P.Price IS NOT NULL AND (D.Paragraph LIKE '%MMColParam3%' OR P.Name
LIKE '%MMColParam3%' OR P.ID LIKE '%MMColParam%')

When I took out the INNER JOIN dbo.PCat PC ON G.gid = PC.gid , I got the
results that I wanted.
At least I know what to look out for now.

Report · May 02, 2006

"lee" <lfairban_nospam@amep.com> wrote in message
news:e35qd4$pgt$1@forums.macromedia.com...
>I got something worked out. In my PCat, I have multiples gids that are in
>separate categories so it was probably showing two of the same Product if
>there were multiple category listings.

You're absolutely right. If the product belonged to more than one category,
then it would appear once in the results for each category it belonged to
unless you filtered down to a certain category in another condition in the
where clause. A common cause of unexpected results is putting too much
information into a query - remember to only take what you need and no more.

Report · May 02, 2006

Yes, I am a "Fish Out Of Water"...

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e37lr4$63q$1@forums.macromedia.com...
>
> "lee" <lfairban_nospam@amep.com> wrote in message
> news:e35qd4$pgt$1@forums.macromedia.com...
>>I got something worked out. In my PCat, I have multiples gids that are in
>>separate categories so it was probably showing two of the same Product if
>>there were multiple category listings.
>
> You're absolutely right. If the product belonged to more than one
> category, then it would appear once in the results for each category it
> belonged to unless you filtered down to a certain category in another
> condition in the where clause. A common cause of unexpected results is
> putting too much information into a query - remember to only take what you
> need and no more.
>