Mysql &quot;IN&quot;

Mysql "IN"

neox_blueline

Im questioning about a mysql query. I want to do something for example:

SELECT * FROM table WHERE "blah" IN row1;

I want that to search row1 for the text-string "blah".
(row1 is the row name)

Is this the proper usage?

Thanks,
neox

LordShryku

No, you need

SELECT * 
FROM table 
WHERE row1 = 'blah';

IN is used for multiple values from a single field, like

SELECT * 
FROM table 
WHERE row1 IN ('blah', 'blah1', 'blah2');

nemonoman

I use MySQL 3.something that lacks a subselect, and use the IN construct to mimic the missing SUBSELECT construct.

For example a standard SQL statement like

SELECT * from invoice WHERE customerid = (SELECT id FROM customer WHERE sex='F')

is the sort of subselect that doesn't work.

So, I'll dynamically use the subselect:

SELECT id FROM customer WHERE sex='F'

To build a comma-delimited list

1,5,31,255,299

etc.

Then I plug that into:

SELECT * from invoice WHERE customerid IN (1,5,31,255,299)

I BRING THIS UP BECAUSE OF A POTENTIAL DRAWBACK THAT CAN BE AVOIDED with IN constructs:

SELECT * FROM invoice WHERE customerid IN ()

will generate a mysql error.

SELECT * FROM invoice WHERE customerid IN (NULL)

runs just fine.

THEREFORE, when I dynamically build my comma delimited list, I always append a NULL, just in case

SELECT * from invoice WHERE customerid IN (NULL, 1,5,31,255,299)

Note that for this type of subselect, you want to be sure to choose from a column that is NOT NULL. But most columns should be NOT NULL if you follow good database design.

detlev

What is the difference in result between this hypothetical query
SELECT * from invoice WHERE customerid = (SELECT id FROM customer WHERE sex='F')

and rhe simpler one:
SELECT * from invoice WHERE sex='F' ?

nemonoman

Good database design puts the attribute with the entity and doesn't duplicate data.

Good design might have a "customer" table with a column "customerid" a column "sex" . Sex is an attribute of the customer.

Invoices are related to customers. So the invoice table would have a column: customerid. Invoices don't in themselves have a "Sex" attribute, so this attribute wouldn't be part of the invoice table. [Now I'm sort of sorry I picked this example!!].

The invoice has a customer, and the customer has a sex.

OK? Keep your data attributes with their entities. That's good design.

A simpler query that would work in THIS instance is a JOIN:

SELECT invoice.*
FROM invoice, customer
WHERE invoice.customerid=customer.id
AND customer.sex='F'

There are some occasions where the IN construct is better than the JOIN and vice versa. It's good to keep both tools in your arsenal.

detlev

Sorry nemonoman, I'm a little bit confused today and lost, that 2 different tables are involved.

In which cases the IN construct has advantages compared to the JOIN construct?

nemonoman

Get a little further into SQL and you'll discover the cases yourself.

Using an IN construct to mimic a SUBSELECT is a concept that likely may not apply to you at all. Others reading this note might have run across the situation, however.

The typical method for selecting data from multiple tables is to use a JOIN construct. You should familiarize yourself with this construct and get fluent with it.

Once you're familiar with JOINs, you will find occasions where the JOIN construct is troublesome. It's nice to remember the IN construct in these instances, as allows (a) increased ability to manipulate data that simple JOINs don't (at least not easily) and (b) a way of breaking up a complex query debugging.

It's clear that these are subtler elements than you need at the moment.

At it's most basic level, IN beats ORs for simplicity:

SELECT * from invoice
WHERE id=1
OR id=2
OR id=3
OR id=4

etc

is a drag compared to
SELECT * from invoice WHERE id IN (1,2,3,4)

neox_blueline

I probably misguided you but, instead of needing the exact string of "Hi" in a column, i just want it to return the rows where the letter "i" isin any of the rows.. so when calling for matches it doesnt have to be an exact match.. so basicly its (SELECT * FROM table WHERE "i" ISIN row) .. if that makes sence.

nemonoman

probably misguided you but, instead of needing the exact string of "Hi" in a column, i just want it to return the rows where the letter "i" isin any of the rows.. so when calling for matches it doesnt have to be an exact match.. so basicly its (SELECT * FROM table WHERE "i" ISIN row) .. if that makes sence.

Yes, it's much simpler:

SELECT * FROM table WHERE column LIKE '%i%'

LIKE (not =). LIKE means 'use wildcards in this select'

%=wildcard symbol

%i%= anything that contains i

%i= anything that ends in i

i%= anything that starts with i

I believe these searches are NOT case sensitive in MySQL

neox_blueline

AHh! Thanks loads.

xnor82

i had tried to make a query with subquery. and it did't work on mysql 3

my query is:
select * from tbl1 where r1 IN (select r2 from tbl2)

i think mysql 3 does't support subqueries...

because i tried same query like this:

select * from tbl1 where r1 IN (1,2,3)

then i worked.

2005

Is it possible to use IN for my situation...?

I store multiple comma separated values in one DB table field in this way 1,5,7,11,15.

Now I have to find all rows which contain, for example, value 1.
If I use LIKE %1% the query will return all rows with 1 and 11.
If I search for 5 I will get also all rows with 15.

I tried to use IN, but it doesn't work so far 🙁

LordShryku

No, IN won't work for you (bad database design BTW).

You'd probably want to do something like this

WHERE field LIKE '%,1,%'
   OR field LIKE '%,1'
   OR field LIKE '1,%'
   OR field = '1'

2005

Thanks LordShryky, I will try this. If not I have to change script so that the smallest value will be 10. Then it will work with LIKE%%.

That do you mean - bad DB design. Do you think it's better to make a separate column for each option? I have users profiles with many questions and some questions with 20-30 options, for example - "Your interests"...

That's why I store data in this way.

LordShryku

Not seperate columns, but a seperate table. The general rule can be any delimited type value should be stored as seperate records in another table, as long as you have a primary key to link it back to the original table.

2005

It's not a problem to create a separate table, but what is the reason to do so?

nemonoman

Person

ID
(other info)

PersonInterest

PersonID
InterestID

Person ID 5 has interests 1, 3, 4, 6

one person record: ID 5
4 PersonInterest Records

Personid 5, interestID 1
Personid 5, interestID 3
Personid 5, interestID 4
Personid 5, interestID 6

etc

SELECT Person.ID
FROM Person, PersonInterest
WHERE
Person.ID=PersonInterest.PersonID
AND InterestID=4

PersonIDs of persons with interest 4

nemonoman

To respond to your "why do it this way" post...

from the example it should be obvious.

Simple query,
not a lot of half-assed "LIKE"s with a bunch of different cases

Here's an IN statement: persons with one of 4 different interest.

SELECT Person.ID
FROM Person, PersonInterest
WHERE
Person.ID=PersonInterest.PersonID
AND InterestID IN (1, 2, 4, 6)
group by 1

Try that with your comma delimited fields and LIKE statements. Good design==easier programming.

2005

Thank you for your replies. Now I understand what you mean.

I never stored multiple values in one field and I wrote script in this way because I have seen other scripts that use one DB field for multiple values. So, I was sure, there must be a simple method for searching.

For example, PHP MatchMaker from deltascripts.com, stores multiple values in one field. This script was free some time ago and I think I have a copy. I have to find it to see how this script does searching.

2005

Yes, I had a copy of PHP MatchMaker.

This script some time ago was for free but now it costs USD 199, but look how it does searching:

SELECT * from users where ((f_11 like '%1%' OR f_11 like '%11%' OR f_11 like '%12%') AND completed_profile=1) order by lastlogin desc

If I search for "1" the script will find all records not only with "1" but "11" too.

So, you are true, I have to use a separate table to store data.

Thank you.