CODEWITHSUNDEEP
mysqlsqljoin
edgarmtze
edgarmtze

Reputation: 25048

Get table information by name, get its id, then get other information using gotten id

I have two tables in sql, person, and status.

I want to search a row by name of person, after getting the row, get id, then get other row of other table using that id

I have

DROP    TABLE IF EXISTS `tbl_person`; 
CREATE TABLE `tbl_person` (
    id_person           INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name                VARCHAR(25) NOT NULL ,
    email               VARCHAR(25) NOT NULL , 
    info                VARCHAR(25) NOT NULL ,
    info2               VARCHAR(25) NOT NULL ,
    CONSTRAINT `uc_Info_Person` UNIQUE (`id_person`)           
) ENGINE=InnoDB                 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


DROP    TABLE IF EXISTS `tbl_status` ; 
CREATE  TABLE `tbl_status` (
    id_status       INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
    id_person       INTEGER     NOT NULL,
    a_ingre         INTEGER     NOT NULL DEFAULT 0,
    b_on_alfa       INTEGER     NOT NULL DEFAULT 0,
    c_on_bith       INTEGER     NOT NULL DEFAULT 0,
    treatment       VARCHAR(25) NOT NULL ,
    medic           VARCHAR(25) NOT NULL ,
    reingr          VARCHAR(25) NOT NULL ,
    FOREIGN KEY (id_person)             REFERENCES `tbl_person`    (id_person), 
    CONSTRAINT `uc_Info_status`  UNIQUE         (`id_person`,`id_status`)     
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


INSERT INTO `tbl_person` VALUES (1, 'JOE DOE', '[email protected]','description','other one');
INSERT INTO `tbl_status` VALUES (1, 1, 10,20,30,'diclofenaco','dosis', 'reign');

and the query:

select * from       `tbl_person` person
         INNER JOIN `tbl_status` status 
         on person.name like 'Joe' 
         and person.id_person = status.id_person ;

But that does not return anything. Is it possible to make a single query that involves all this?

Please take a look at the fiddle

Upvotes: 0

Views: 53

Answers (1)

Dan J
Dan J

Reputation: 16708

You're missing a wildcard in your LIKE operand (see here). The following will find records whose name begins with "Joe", for example:

SELECT * FROM `tbl_person` person
INNER JOIN `tbl_status` status 
    ON person.name LIKE 'Joe%' -- Note the % trailing the search string
    AND person.id_person = status.id_person;

Upvotes: 2

Related Questions