stack stack - 7 months ago 10
SQL Question

How to use JOIN instead of comma?

I have this query:

INSERT INTO Votes (id_post,id_user)
SELECT ?,?
FROM Posts p, Users u
WHERE p.id_user = :id_author
AND u.id = $_SESSION['id']
AND u.active = 1
limit 1;


Now I want to use
JOIN
instead of
,
. But there isn't any common column between those two tables. So what should I write in
ON
clause?




What I'm trying to do:

I have three tables:

// Posts
+----+----------+---------------+-----------+
| id | title | content | id_author |
+----+----------+---------------+-----------+
| 1 | title1 | content1 | 1234 |
| 2 | title2 | content2 | 5678 |
+----+----------+---------------+-----------+
// ^ the id of post's author

// Users
+----+--------+--------+
| id | name | active |
+----+--------+--------+
| 1 | jack | 1 |
| 2 | peter | 0 |
| 3 | John | 1 |
+----+--------+--------+

// Votes
+----+---------+---------+
| id | id_post | id_user |
+----+---------+---------+
| 1 | 32 | 1234 |
| 2 | 634 | 5678 |
| 3 | 352 | 1234 |
+----+---------+---------+
// ^ the id of current user


Now I need to check two conditions before inserting a new vote into
Votes
table:


  1. Is the id of author the same as what I pass as
    id_author
    ?
    Posts.id_user = :id_author
    (I know I can do that by a FK, but I don't want)

  2. The account of current user is active?
    Users.active = 1



Sum Up: I'm trying to don't let people be able to vote who are inactive (
active = 0
)
. For example if Stackoverflow bans you, then you cannot vote to posts anymore, because you (current user) are banned. So I'm pretty sure
$_SESSION['id']
should be used in the query to determine current user.

Answer

I suggest using exists instead of join:

INSERT INTO Votes (id_post, id_user) 
SELECT id_post, id_user FROM (SELECT ? id_post, ? id_user) a
WHERE EXISTS (
    SELECT 1 FROM Users
    WHERE id = ?
    AND active = 1
) AND EXISTS (
    SELECT 1 FROM posts
    WHERE id_user = :id_author
)