how to pass value from one dataframe to another dataframe?

> val a=spark.sql(select max(CID) as C_ID from AAA 
> val b=spark.sql(select * from  NST where C_ID= ' ')
> 

I have to pass the the C_ID value to where condition in below data frame as parameter. Any suggestions how I can do this ? i should not use subquery concept as data is in millions and multiple tables are there in joins,here i have mentioned sample query.

Answer

Store sql result into a variable using mkString and then use the variable in your where clause.

Example:

val df=Seq((1,"a"),(2,"b")).toDF("CID","n")
df.createOrReplaceTempView("AAA")

val df1=Seq((1,"a"),(2,"b")).toDF("C_ID","j")
df1.createOrReplaceTempView("NST")

val a=spark.sql("select max(CID) from AAA").collect()(0).mkString
spark.sql(s"select * from NST where C_ID=${a}").show()

#+----+---+
#|C_ID|  j|
#+----+---+
#|   2|  b|
#+----+---+