How to join multi condition query mysql-CodePudding

apt table

unique_id	apt_name	apt_data	status
1111	Jose	Michigan	Active
2222	Michael	Michigan	Offline
3333	Mike	Canada	Active

book table

conv_id	apt_uniqueid	user_uniqueid	conv_status	conv_data
1A2B	1111	77777	invalid	1
3C4D	2222	88888	valid	0
5E6F	3333	99999	valid	0
7F8G	1111	99999	invalid	1
5T8Y	2222	99999	invalid	1

My Expected query is Every single apt account which status 'Active' and apt account which in book table conv_status = 'invalid' and conv_data = 1

Expected query when user_uniqueid = '99999'

conv_id	unique_id	apt_name	apt_data	status	conv_status
0	1111	Jose	Michigan	Active	0
0	3333	Mike	Canada	Active	0
7F8G	1111	Jose	Michigan	Active	1
5T8Y	2222	Michael	Michigan	Offline	1

$sql = "SELECT * FROM apt WHERE sts = 'Active' 
        UNION
        SELECT apt.*, book.* 
        FROM book
        INNER JOIN apt ON book.apt_uniqueid = apt.unique_id
        WHERE conv_status = 'invalid' AND (user_uniqueid = {$_SESSION['unique_id']} AND conv_data = '1') ORDER BY unique_id DESC";

CodePudding user response：

It depends on the amount of data you want from tables.

Common Use joins are INNER , LEFT, RIGHT.

Now if you want data from the mentioned tables, Then simply use :-

SELECT book.conv_id, apt.unique_id, apt.apt_name,apt.apt_data,apt.status,book.conv_status,
FROM book
INNER JOIN Customers ON book.apt_uniqueid=apt.unique_id
WHERE book.conv_status = 'invalid' AND book.user_uniqueid = {$_SESSION['unique_id']} AND book.conv_data = '1' ORDER BY apt.unique_id DESC";

For more details:

CodePudding user response：

Try to use left join if it's working

$sql = "SELECT apt.unique_id,
apt.apt_name,
apt.apt_data,
apt.status,
book.conv_id,
book.apt_uniqueid,
book.user_uniqueid,
book.conv_status,
book.conv_data
FROM apt
LEFT JOIN book ON book.apt_unqieid = apt.unique_id
WHERE apt.status= 'ACTIVE' AND apt.unique_id = '$_SESSION['unique_']' AND 
book.conv_data = '1' ORDER BY apt.unique_id DESC
";