Search code examples
mysqlsubquery

viewing different columns of two tables in a single table using subqueries

I have two tables T1 and T2 - T1-columns(c1, c2) T2-columns(c2, c4) c2 is the primary key of T1 and a foreign key in T2. I want to return a record which contains T1.c1 and T2.c4 where T2.c4= 'some value' using subqueries. So, I wrote a subquery - SELECT c1 FROM T1 WHERE c2=(SELECT c2 FROM T2 WHERE c4 = 'some value') Is there a way to view both these columns (T1.c1, T2.c4) in a single table with subqueries?

Solution

  • The value of c4 you want in the result is 'some value' (if it exists):

    SELECT c1, 'some value' c4 
FROM T1 
WHERE c2 = (SELECT c2 FROM T2 WHERE c4 = 'some value')

    This will work only if the subquery:

    SELECT c2 FROM T2 WHERE c4 = 'some value'

    returns only 1 row.

    But this requirement could be better resolved with a join:

    SELECT T1.c1, T2.c4 
FROM T1 INNER JOIN T2
ON T2.c2 = T1.c1
WHERE T2.c4 = 'some value';