CODEWITHSUNDEEP
mysql
cj333
cj333

Reputation: 2609

mysql how to make a query like this

Here is some innodb table, am store products id, bm store description key words. 

CREATE TABLE IF NOT EXISTS `myt` (
  `am` varchar(15) NOT NULL,
  `bm` varchar(30) NOT NULL,
  KEY `am` (`am`),
  KEY `bm` (`bm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `myt` (`am`, `bm`) VALUES
('1', 'hello'),
('1', 'world'),
('2', 'this'),
('2', 'world'),
('3', 'hello'),
('2', 'there');

How to get a products which has words 'hello world'?

Query that i tried:

(select * from myt a where bm='hello') union (select * from myt b where bm='world') where a.am=b.am

gave me an error:

 #1064 - You have an error in your SQL syntax;

If i store data like this, should index be worked? Thanks.

P.S.

Is it matter if a innodb table without primary key?

Upvotes: 0

Views: 51

Answers (3)

user2169391
user2169391

Reputation:

First you shouldn't use a varchar to store an id.

select am
from myt
where bm in ('hello', 'world')
group by am
having count(am) = 2

The count should equal the number of descriptions in the where clause (2 in this case)

Consider using PostgreSQL instead of MySQL for this kind of queries. With PostgreSQL you can store arrays in field bm, like;

INSERT INTO `myt` (`am`, `bm`) VALUES
('1', ['hello','world']),
('2', ['this', 'world', 'there']),
('3', 'hello');

It also has strong features to search these array fields afterwards.

Upvotes: 1

naveen goyal
naveen goyal

Reputation: 4629

Try This ....

select * 
from 
    (
        select * 
        from myt 
        where bm='hello' 
        group by am
    ) t
join 
    (
        select * 
        from myt 
        where bm='world' 
        group by am
    ) t1 
    on t.am=t1.am

Upvotes: 0

Alex
Alex

Reputation: 7374

You can use the OR in mysql to return matches for two things:

select * from myt a where bm='hello' or bm='world'

Upvotes: 0

Related Questions