how to get data from more than 2 tables

[shadd]

i have these tables:

create table tblQuestions
(
	QueId		varchar(255),
	instruction  	text  ,
	question  	text  ,
	Primary key(Que_Id)

);
create table tblAnswers
(
	QueAns_Id	int unsigned not null auto_increment,
	Answer  	text  
	Primary key(QueAns_Id)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table tblScore
(
	Ans_Id		varchar(255),
	QueAns_Id	int unsigned not null ,
	User_id  	int unsigned  ,
	Answer_score    float not null default 0.0,/*0.0 or 1.0*/

	Primary key(Ans_Id,User_id),
	foreign key(QueAns_Id) references tblAnswers(QueAns_Id) on delete cascade on update cascade,
	foreign key(User_id) references tblUsers(sUser_id) on delete cascade on update cascade
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table tblUsers
(
	sUser_Id  	mediumint unsigned not null auto_increment,
	First_name	varchar(50)  not null,
	Primary key(sUser_Id),
	unique key(S_UPassword)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO tblUsers(First_name) VALUES('Apple);
INSERT INTO tblQuestions(QueId,instruction
,question )VALUES('1.1.49','','Hard work may lead to success.(rewrite using:.....could.........)')
('1.1.49','','Hard work may lead to success.(rewrite using:.....could.........)');
('1.1.50','','Jessy can speak English yet she has never been to school.(Rewrit using:......inspite of ......)');
('1.1.51',' Read the passage below and then answer in full sentences the questions that follow.','What is the passage about?');
('1.1.51.10','','why am i not in order?');
('1.1.51.2','','Why is it iam not in place above 1.1.51.10');
('1.1.51.3','','Yeah part b,he should be below us!');

iam using the select query below to get the data,but the questions are not in order
i like to have:
1.1.49
1.1.50
1.1.51
1.1.51.2
1.1.51.3
1.1.51.10

   SELECT q.QueId AS Q_id,
	    CASE when ROUND((LENGTH(q.QueId)-LENGTH(replace(q.QueId,'.',''))) / LENGTH('.') )>2 then
		concat(q.Que_Id,CHAR(CAST(SUBSTRING_INDEX(q.QueId,'.',-1) AS INTEGER)+96))END as QueNo,
	    CASE when ROUND((LENGTH(q.QueId)-LENGTH(replace(q.QueId,'.',''))) / LENGTH('.') )>2 then
            	CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(q.QueId,'.',-2),'.',1) AS INTEGER)
            ELSE CAST(SUBSTRING_INDEX(q.QueId,'.',-1) AS INTEGER)end as qNo,
	    q.instruction,  	
	    q.question,  	
	Z.QueAns_Id,	
	Z.Answer,
	Z.User_id,Z.Answer_score
FROM
	     tblQuestions q

LEFT JOIN
(select 
	B.QueAns_Id,	
	B.Answer,
	C.Ans_Id,C.User_id,C.Answer_score
from tblAnswers B
LEFT JOIN
tblScore C
ON B.QueAns_Id=C.QueAns_Id
AND C.User_id=1)AS Z
ON q.QueId=Z.Ans_Id
and SUBSTRING_INDEX(q.QueId,'.',2)='1.1'
	    order by qNo asc;

 

[Barand]

1 hour ago, shadd said:

create table tblQuestions
(
	QueId		varchar(255),
	instruction  	text  ,
	question  	text  ,
	Primary key(Que_Id)

);

FYI - it helps if the column defined as the primary key exists in the table!

Same goes for the unique key in user table.

Plus other syntax errors - I was trying to help but I gave up.

[Barand]

Why the Freckle didn't you post the code that actually used to get the results you are complaining about. Once I got the data loaded, your query wouldn't even run without corrections to column names.

Anyway - the answer to your question...

They are in the wrong order because you order by your generated qNo column. I'd give up on that method.

If you are using MariaDB, you can ORDER BY NATURAL_SORT_KEY(Q_id)

If MySQL (which doesn't have that function), use FetchAll() to get an array of your results then natsort($results) use a custom sort which does a strnatcmp() on the Q_id column
 

$res = $pdo->query(" ... ");
$result = $res->FetchAll();
usort($results, fn($a,$b)=>strnatcmp($a['Q_id'], $b['Q_id']));

(Using sort($results) would have sorted using the values of the first column in each row - I assumed natsort() would do the same (silly me) )