phpmyadmin Question posted in
The official documentation can be found here.

phpmyadmin gives syntax error to join 2 tables if a column contains substring

"mytest_people" and "mytest_hobbies":

  PK                 FK    <---  PK
+----+------+------+------+    +----+-----------------+
| Id | Name | Age  | H_ID |    | Id | Hobby           |
+----+------+------+------+    +----+-----------------+
| 0  | Pete | 100  | 0    |    | 0  | Reading News    |
| 1  | Mark | 5    | 1    |    | 1  | Reading Fiction |
| 2  | Bob  | 33   | 3    |    | 2  | Writing         |
| 3  | Lulu | 12   | 1    |    | 3  | Cooking         |
+----+------+------+------+    +----+-----------------+

This is what I am expecting to get:

+----+------+------+-----------------+
| Id | Name | Age  | Hobby           |
+----+------+------+-----------------+
| 0  | Pete | 100  | Reading News    |
| 1  | Mark | 5    | Reading Fiction |
| 3  | Lulu | 12   | Reading Fiction |
+----+------+------+-----------------+

This is in everyday-ish words /pseudo code:

IN DATABASE "my_new_test_data_base"
SELECT COLUMNS "Id, Name, Age"
FROM TABLE "mytest_people"
WHERE COLUMN "H_ID"
    EQUALS COLUMN "Id"
    FROM TABLE "mytest_hobbies"
THEN JOIN COLUMN Hobby
FROM TABLE mytest_hobbies
ON CONDITION Hobby VALUES
    CONTAIN 'Reading%' SUBSTRING;

This is my latest attempt at the SQL code:

USE my_new_test_data_base
SELECT mytest_people.Id, mytest_people.Name, mytest_people.Age FROM mytest_people
INNER JOIN mytest_hobbies (`Hobby`)
ON mytest_people.H_ID = mytest_hobbies.Id
WHERE `Hobby` LIKE 'Reading%';

This is the error I get:

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SELECT mytest_people.Id, mytest_people.Name, mytest_people.Age
FROM mytest_peop’ at line 2

Tags:

2

Answers


  1. 0

    some small misstale, every comand has to be ended by semicolion

    and aliases don’t belong in brackets

    USE my_new_test_data_base;
SELECT mytest_people.Id, mytest_people.Name, mytest_people.Age ,mytest_hobbies.`Hobby`
FROM mytest_people
INNER JOIN mytest_hobbies 
ON mytest_people.H_ID = mytest_hobbies.Id
WHERE mytest_hobbies.`Hobby` LIKE 'Reading%';
    Login or Signup to reply.
  2. 0

    If less typing is your thing:

    SELECT p.Id
     , p.Name
     , p.Age 
     , h.hobby
  FROM my_new_test_data_base.mytest_people p
  JOIN my_new_test_data_base.mytest_hobbies h
    ON p.H_ID = h.Id
 WHERE h.Hobby LIKE 'Reading%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search