best way to search for....

[techlover]

Ok, I have a table, let's call it 'Things'. In Things is say 10 columns called 'item01' to 'item10'.

What I am trying to do is pull up anyone that has a specific 3 items in their item list. The items are not in order so you may have:

user: Joe
item01: apple
item02: orange
item03: pear

user: Fred
item01:pear
item02:orange
item03: apple
etc

Is there an easy way to do this without pulling each record? There is like 10,000 entries and growing.

I am totally lost on this one since the item columns are all different.

Any help is greatly appreciated.

THANKS!
😃

[chriskl]

I rather think you've badly designed your schema. You should have a table of user, a table of items and a table that maps users to items. Then the problem is trivial.

Howver, in your current schema you're going to have to do heaps of nastly ORing and ILIKEing - which will be nastly, slow and ugly...no way around it really...

Chris

[techlover]

hmm ok. Well here is what I am doing exactly.

I am entering my pick 6 tickets that I have played over time. I want to be able to then do a query of putting in say today's pick6 and finding out how many of my past tickets matched 3 of the 6, 4 of the 6, etc..

There has got to be an easy way to do this right?

I have this:

MyTickets
ticketid
number1
number2
number3
number4
number5
number6

I am also putting in tickets from friends so the list is getting good sized.

Any help is appreciated.

Thanks again 🙂

[Frag]

You are going to have to use brut force to get your answer and a db query isn't going to do it with that schema.