SQL Question

[Scarpozzi]

I'm learning SQL queries. Working with multiple tables, joins, etc...

One of the problems I have is that there is a table with questions and answers. I have to do a sub query to create a value. I've seen this done by throwing a whole subquery in the SELECT statement of the main query, then assigning it a name "VALUE1". Just an example.

Can I use the value of that subquery in the WHERE statement of the main query?

Basically, I'm trying to isolate records. I may need to rethink my query design...just wondering if that's possible. Sorry if my lingo is poor....still learning.

 

[Ken g6]

Yes, that sounds reasonable.

https://en.wikipedia.org/wiki/SQL#Subqueries

The main thing I'd want to check is whether the subquery returns one value or multiple values. One value is generally easier to deal with, but I think an "in" could handle multiple values.

 

[Jeraden]

I'm learning SQL queries. Working with multiple tables, joins, etc...

One of the problems I have is that there is a table with questions and answers. I have to do a sub query to create a value. I've seen this done by throwing a whole subquery in the SELECT statement of the main query, then assigning it a name "VALUE1". Just an example.

Can I use the value of that subquery in the WHERE statement of the main query?

Basically, I'm trying to isolate records. I may need to rethink my query design...just wondering if that's possible. Sorry if my lingo is poor....still learning.

If you are asking what I think you are asking, then the answer is no... well, maybe it depends on what database you are using. I'm an Oracle programmer, and at least in Oracle, you can't do what I think you are asking directly.

select table_a.whatever, (select blahblah from table_b) value1
from table_a
where value1 = 'something'

You can't do that. The where clause will give you an error saying value1 is an invalid identifier. The where clause can only see columns from the tables in the from clause. The where clause can not see anything in the select clause.

What you CAN do though is wrap that entire thing into another select...

select * from (
select table_a.whatever, (select blahblah from table_b) value1
from table_a
)
where value1 = 'something'

As then value1 can be seen by the where clause, since it's now functioning as a table column in the from clause. Hope that makes sense. At least I *think* that is what you were asking.

 

[brandonb]

Jeraden has the right answer. But here are my thoughts as well.

I would not use sub queries. They are extremely slow. For every row in a result set, another SQL statement is run inside of the server. Granted, the data could be cached, but its generally a bad practice that will eventually bite you in the rear end. Especially if you have a large result set. You'll wonder why your query takes over 10 minutes to run.

It's best to use a nested join or table (like Jaraden said). I will show you the syntax, you may need to figure out how to implement this inside of your database (as I don't know the structure).

Here is a join. Jaraden gave you the syntax of a nested table. You can also nest the table and join in the same way if needed.

SELECT stuff, morestuff, D1.avalue
FROM data
JOIN
(
     SELECT stuff, whatever, avalue
     FROM moredata
     WHERE avalue= 'filter'
) AS D1 ON data.stuff = D1.stuff

SQL natively will run all nested queries first. So the select from moredata is run once before data. The results are in the database memory. Then the query from data is run and merged with moredata. This is 2 "selects" within the database and a memory merge. (View an execution plan if your DB supports that to see what happens internally). A sub query will run a "select" within the database 1 + number of rows. If you have many sub queries it ends up being like 1 + (number of rows * number of subqueries) and you can see how this will turn into a monster quickly.

 

[Scarpozzi]

Thanks for the responses.

That's what I was thinking about the nested queries. I've actually had some of my test examples crash because they've hit memory constraints pretty quickly. My biggest problem is I'm not doing native SQL and having to use SQL code through a 3rd party app. It's making the response delayed and the server itself is a VM, so it's already running with a limp.

I'm looking at using LISTAGG and simply grabbing all my results in one basic query...then throw an inner join on to get the rest of the data I need without doing the subqueries.

It's really challenging the way I think because I'm used to other scripting languages that allow simple if-thens or even running through an export list with a foreach loop. (which is somehow quicker than some of these queries I've tested)

 

[KentState]

It's really challenging the way I think because I'm used to other scripting languages that allow simple if-thens or even running through an export list with a foreach loop. (which is somehow quicker than some of these queries I've tested)

That's where you have to change your thinking. SQL is about answering a bunch of data questions in a single batch and not iterating through rows asking the question over and over. You can leverage cursors like a loop, but that can be efficient in something like SQL Server.

One approach is to think about how to return all the data to answer your question even if it means duplication and additional data. From there, you modify your joins and predicates to reduce the set to just the data you want.

 

[sao123]

A table with both questions and answers doesn't sound setup correct...
(this seems to be a many to many relationship)
If the data was relationally correct, seems there should be 3 tables,
Questions, Answers, and a RelateQuestionToAnswer Table...

 

[Ken g6]

A table with both questions and answers doesn't sound setup correct...
(this seems to be a many to many relationship)
If the data was relationally correct, seems there should be 3 tables,
Questions, Answers, and a RelateQuestionToAnswer Table...

I think it's just a many-to-one relationship. There are many answers to a question, but only one question for every answer. So I think only 2 tables are needed.

 

[sao123]

what happens if 2 questions both have the same answer?

That would introduce ambiguity without the third table.

 

[Ken g6]

Then there are two entries in the answers table with the same text but different associated questions. (Associated question being a column too.) Redundant, but worth the potential extra space I think.

 

[sao123]

Then there are two entries in the answers table with the same text but different associated questions. (Associated question being a column too.) Redundant, but worth the potential extra space I think.

you can do that, its just not third normal form.