MYSQL - SELECT AS alias to be used in subquery

Question

I want to be able to use one of the items I am selecting further down in my sub query. As you can see in this example I have set the "variable" in question as 100 but in reality this is going to be a dynamic number so I will need to be able to access it in the subquery.

INSERT INTO users_friends (userId, friendId) 
SELECT 77, id as noob
FROM users WHERE email = 'a@g.com' 
AND
NOT EXISTS (SELECT * FROM users_friends
WHERE userId = 77 and friendId = noob)
LIMIT 1

Answer 1

INSERT INTO users_friends (userId, friendId) 
SELECT 77, id 
FROM users WHERE email = 'a@g.com' 
AND
NOT EXISTS (SELECT * FROM users_friends
WHERE userId = 77 and friendId = (SELECT id 
FROM users WHERE email = 'a@g.com'))
LIMIT 1

Answer 2

EDIT

I would recommend changing your table structure.

    CREATE TABLE users_friends(
userid int, 
friendid int,
primary key (userid, friendid)
);

CREATE TABLE users (
 userid int primary key, 
 email varchar(100), 
 name VARCHAR (100),
 index (email,name)
);


INSERT INTO users VALUES (1, 'a@g.com', 'noob'), (2,'b@g.com', 'Joe');


INSERT INTO users_friends (userId, friendId) 
VALUES (2, (SELECT userId 
FROM users 
WHERE email = 'a@g.com' 
AND name = "noob"
AND NOT exists (SELECT * FROM users_friends as uf 
            JOIN users as u 
            ON u.userid = uf.userid 
            where uf.friendid = 2 AND name = "noob"            
           )
           ) 
       );

SQL FIDDLE DEMO

Try this:

    <?php

    function select_query ($userid,$friendname, $email){
    $host = "host";
    $user = "username";
    $password = "password";
    $database = "database name";

    // open connection to databse
    $link = mysqli_connect($host, $user, $password, $database);
            IF (!$link){
                echo ("Unable to connect to database!");
            }
            ELSE {
               //Is someone registered at other conference from table registration
               $query = "    INSERT INTO users_friends (userId, friendId) 
VALUES (".$userId.", (SELECT userId 
FROM users 
WHERE email = '".$email."' 
AND name = '".$friendname."'
AND NOT exists (SELECT * FROM users_friends as uf 
            JOIN users as u 
            ON u.userid = uf.userid 
            where uf.friendid = ".$userId." AND name = '"$friendname"'            
           )
           ) 
       )"; 
               $result = mysqli_query($link, $query);
               return $query;
               return $result;
            }
            mysqli_close($link);
    }

    echo select_query(1,noob,'a@g.com');
   ?>

Like I mentioned above I not sure what you mean. If you mean dynamic in the sense that you can change the value of the variable this might help. In your previous posts you used PHP. So, my guess is that your are using PHP.

Answer 3

I think the safest approach is to define these in a subquery. I typically give this the alias of const:

INSERT INTO users_friends (userId, friendId) 
    SELECT const.userId, const.noob
    FROM users cross join
         (select 77 as userId, 100 as noob) const
    WHERE email = 'a@g.com' AND
          NOT EXISTS (SELECT *
                      FROM users_friends
                      WHERE userId = const.userId and friendId = const.noob
                     )
    LIMIT 1

I am concerned about SGeddes's approach, because it relies on the correct evaluation of variables outside the scope of the query. This might work in this case, but I prefer a solution where the query does not rely on outside variables. By the way, this should also work in any database, and is not MySQL-specific.

Answer 4

I'm not completely sure I understand your question, but NOT EXISTS works just like LEFT JOIN and IS NULL. So I think this will work:

SELECT 77, @noob
FROM users u
  JOIN (SELECT @noob:= 100) r
  LEFT JOIN users_friends uf on u.userid = uf.userid and uf.friendid = @noob
WHERE email = 'a@g.com' 
  AND uf.userid IS NULL

And here is the SQL Fiddle.