0

I'm posting this question because I did manage to find a similar question, but they weren't using aliases.

I have two tables - I want to grab everything from table1 and just grab the user_name and Team from table2.

My original query is grabbing everything from table2

SELECT * 
FROM qabin.allqas t1 
JOIN login.users t2 
ON (t1.Submitter = t2.user_name) 
WHERE t1.Status='Complete'

That's all well and good and works fine, but I would like to just get the user_name and the Team

To make things more interesting, they are in different databases, though it hasn't been an issue.

One is in the qabin database and the other is in the login database.

I have tried:

SELECT 
  qabin.allqas.* AS t1,
  login.users.Team,
  login.users.user_name
JOIN login.users t2
ON (t1.Submitter = t2.user_name)
WHERE t1.Status='Complete'

I need the t1 and t2 aliases because they are used elsewhere for building a larger query string.

Thanks in advance!

Community
  • 1
  • 1
user1274820
  • 7,786
  • 3
  • 37
  • 74
  • Since you pointed out the other question its useful to know that you can follow that answer and just substitute the aliases for the table names – Dan May 12 '16 at 17:25
  • I was trying to avoid this as there are many, many lines of code that use those aliases. – user1274820 May 12 '16 at 17:29

3 Answers3

2

Why not use a subquery?

SELECT * 
FROM qabin.allqas t1 
JOIN (SELECT user_name, Team FROM login.users) t2 
ON (t1.Submitter = t2.user_name) 
WHERE t1.Status='Complete'
rgvassar
  • 5,635
  • 1
  • 24
  • 31
  • Awesome, very fast answer and it works :) Also, TIL what subqueries are. – user1274820 May 12 '16 at 17:26
  • That's kind of a roundabout way to accomplish something that is much simpler. – Dan May 12 '16 at 17:27
  • 1
    He said that he uses this alias later in a large query. If for some reason he wants only these two fields available to this alias, this is how you do it. – rgvassar May 12 '16 at 17:35
  • 1
    Ah, you're right. For the simple case it's more than whats needed, but for a more complex query, your answer is nice :) – Dan May 12 '16 at 18:32
1

You cant use one alias (t1) for a set of columns. I guess what you are looking for is something like:

SELECT t1.*, t2.team, t2.user_name
FROM qabin.allqas t1 
JOIN login.users t2 
    ON (t1.Submitter = t2.user_name) 
WHERE t1.Status='Complete'

I would recommend using the real column names from t1 instead of t1.*

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
  • I build a string based on a bunch of parameters that are sent, so let me explain what it's used for. Basically another string that appears later is: `$queryString .= "AND t1.DateCompleted BETWEEN '$startDate' AND '$endDate' ";` I was hoping to avoid having to rewrite a bunch of later code – user1274820 May 12 '16 at 17:27
  • I'm relatively new to it, but rest assured that I am escaping all post data – user1274820 May 12 '16 at 17:27
1

Try This query :

SELECT t1.*, t2.user_name, t2.Team
FROM qabin.allqas t1 
JOIN login.users t2 
ON t1.Submitter 1= t2.user_name
WHERE t1.Status='Complete'
Tony
  • 204
  • 1
  • 4