How to use intersect in a query?

Question

I have 2 tables. I want to check the very last data inserted in the tables. So, I want to retrieve the last data from both tables and then check if that last data from both tables are same or not.

$q1 =   "SELECT * ".
        "FROM `admin`, `student` ".
        "ORDER BY `admin`.`id` DESC  LIMIT 1 ".
        "INTERSECT".
        "SELECT *".
        "FROM `admin`, `student`".
        "WHERE `admin`.`pcode` = `student`.`code` AND
`admin`.`puser_name` = `student`.`puser_name`";

I cannot use intersect operator so I am getting difficulty in changing in other form.

You forgot the space after `INTERSECT`, so your query reads like `INTERSECTSELECT`. — KIKO Software, Jun 01 '19 at 09:40
I edited that, but the main problem is how to change my query in another form. I mean I cannot use INTERSECT,right? — Potato23, Jun 01 '19 at 09:46
$q1 = "SELECT * ". "FROM admin, student ". "WHERE admin.pcode = student.code AND admin.puser_name = student.puser_name AND admin.pcode IN ". "(SELECT admin.pcode ". "FROM admin ". "ORDER BY admin.id DESC LIMIT 1) " ; — Potato23, Jun 01 '19 at 09:55
In addition to the space problem noted by KIKO Software, `INTERSECT` is not supported by MySQL at all. So I marked this question as a duplicate of another asking for an alternative way to achieve the result. — Bill Karwin, Jun 01 '19 at 16:22

score 0 · Answer 1 · answered Jun 01 '19 at 12:15

Try this Query:

$q1 = "SELECT * FROM admin
WHERE admin.pcode = student.code
INTERSECT
SELECT *FROM student
WHERE admin.puser_name = student.puser_name
ORDER BY admin.id DESC  LIMIT 1";

The INTERSECT operator compares the result of two queries and returns the distinct rows that are output by both left and right queries.

score 0 · Answer 2 · answered Jun 01 '19 at 12:36

Try this query:

$q1 =   "SELECT * FROM admin, student ORDER BY admin.id DESC  LIMIT 1 
        INTERSECT 
        SELECT * FROM admin, student 
        WHERE admin.pcode = student.code AND admin.puser_name = student.puser_name 
        ";

Hope will help

How to use intersect in a query?

2 Answers2