SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Building Tips (Need Them!)


Query Building Tips (Need Them!)

Author
Message
robertafricker
robertafricker
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 36
Hello All,

The topic of "How to Build Queries" is at the top of my list.
When it comes to deciphering how to break down the building blocks of the query into syntax parameters, I am still "not there yet." ESPECIALLY IN REGARD TO SUBQUERIES AND WHICH COMMANDS TO USE.
The following is a query example that I am currently working on.
I will post my answer underneath it, which is perhaps correct.
Please feel free to correct it and advise as to how I can better understand how the query translates into syntax. Here it is:

DB = Beer Drinkers
Tables and columns: Frequents: drinker, bar
Serves: bar, beer
Likes: drinker, bar

Query: List the bars that serve a beer that 'Joe' likes.

USE Beerdrinkers
SELECT bar
FROM serves
WHERE beer(SELECT FROM likes
WHERE drinker = ‘Joe’)

Let me know, thanks!
N J
N J
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 417
Your query will not work in sql server..also you dont need subquery for this..

try this..

select bar
from serves join likes on
serves.bar = likes.bar
and likes.drinker = 'joe'

some basic information about subqueries is here
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=74
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14578 Visits: 9518
It is important to realize that there are several types of Subquery:

1. Value Subqueries: These just return a single value per row and they look like this:
(Select col1 From ... )

If they return no value, you get a NULL, if they return multiple values, you will fail with an error. They are most often used to assign a value to an output column, or to compare to a value in your where clause.

2. IN subqueries: These return any number of rows, each with a single column and they look like this:
WHERE value IN(Select col1 From ... )

They are typically used in WHERE clauses to test if a particular value is in the list of values returned.

3. EXISTS subqueries: These return any number of rows with any number of columns and they look like this:
WHERE EXISTS(Select * From ... )

They are typically used in WHERE clauses to test if corresponding rows in another table exist. Because the column values returned are not actually used, typicaly you use a "*" instead of bothering to name any columns.

The query that you listed:
USE Beerdrinkers
SELECT bar
FROM serves
WHERE beer(SELECT FROM likes WHERE drinker = ‘Joe’)

has three obvious problems. First, "beer" is being used as a function, but "beer" is not a function: it is a column in your "serves" table and so should be used as a value in your query: either assigned to an output column, used in a formula/calculation or compared to another value.

Secondly, What kind of Subquery is this? If it is a Value subquery, then you need to use it like a value. If it is an IN subquery, you should have an "IN" and be comparing a value to the list of values that it returns. and, if it is an EXISTS subquery, then you should have an "EXISTS" and it should be alone in a WHERE or AND subclause.

Thirdly, your subquery: SELECT FROM... has no output column. What column you pick depends on the type of subquery (or "*" for an EXISTS).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
robertafricker
robertafricker
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 36
Hi NJ,

Believe it or not, I had that query as well as a 2nd option, and wasn't sure if it was correct.
In the meantime, I know now the complex query s/b:

USE Beerdrinkers
SELECT bar
FROM serves
WHERE beer IN
(SELECT beer FROM likes WHERE drinker = ‘Joe’)

(this is the second one I had)
USE Beerdrinkers
SELECT bar
FROM serves, likes
WHERE serves.beer = likes.beer
AND drinker = ‘Joe’

Thanks for the feedback. I have many more queries I'll be submitting over the next several days.

-Roberta-
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86192 Visits: 45229
robertafricker (3/30/2008)
Hi NJ,

(this is the second one I had)
USE Beerdrinkers
SELECT bar
FROM serves, likes
WHERE serves.beer = likes.beer
AND drinker = ‘Joe’



If I may make a minor correction...

SELECT bar
FROM serves INNER JOIN likes ON serves.beer = likes.beer
AND drinker = 'Joe'

It's not recommended to do joins in the where clause any more. Especially since, in 2005 and higher, the old style outer join (*=) does not work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86192 Visits: 45229
rbarryyoung (3/30/2008)
It is important to realize that there are several types of Subquery:

1. Value Subqueries: These just return a single value per row and they look like this:
(Select col1 From ... )

If they return no value, you get a NULL, if they return multiple values, you will fail with an error. They are most often used to assign a value to an output column, or to compare to a value in your where clause.

2. IN subqueries: These return any number of rows, each with a single column and they look like this:
WHERE value IN(Select col1 From ... )

They are typically used in WHERE clauses to test if a particular value is in the list of values returned.

3. EXISTS subqueries: These return any number of rows with any number of columns and they look like this:
WHERE EXISTS(Select * From ... )

They are typically used in WHERE clauses to test if corresponding rows in another table exist. Because the column values returned are not actually used, typicaly you use a "*" instead of bothering to name any columns.



4. Derived tables: These are used in the From clause, may return any number of columns and any number of rows. Once aliased, they can be joined to other tables and treated just like a table in the other clauses of the query.
Eg.


SELECT TheTable.Col1, TheDerivedTable.Col2, TheDerivedTable.SumOfCol2, TheTable.Col4
FROM TheTable
INNER JOIN
(SELECT Col1, Col2, SUM(Col3) AS SumOfCol3
FROM SomeOtherTable
GROUP BY Col1, Col2) TheDerivedTable
ON TheTable.Col1 = TheDerivedTable.Col1



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84557 Visits: 41064
On the subject of "Derived Tables", look into CTE's... they make for easier to read code and can be self aliased, if need be, without duplicating the "sub-query".

Also, take a look at the article at the URL in my signature line...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23261 Visits: 9730
Roberta:

Sub-queries are exactly what their name says, they are a query inside another query.

In the case you outlined, a join is better than a sub-query. They will both work, but the join will usually be faster. (Hidden little secret, SQL will often convert the "in (subquery)" code to a join behind the scenes, for exactly this reason. It just won't tell you it's doing it. Of course, if you look at the query plan, then the cat's out of the bag and you'll see the join.)

The way to write sub-queries is the same way you write any other query. They have the same rules. The only exception is that inline sub-queries can only return one row.

Inline sub-queries are where you have a sub-query in your Select clause, instead of your From or Where clause, or where the sub-query follows "=" instead of "in". They can also be used in Order By, and Group By clauses, the same as Select clauses.

For example:


select
(select title
from dbo.Titles
where gender = People.gender) as Title,
FirstName, LastName
from dbo.People



This assumes you have a "gender" column in your People table, and another gender column in your Titles table, and the two can match.

Again, this would be better solved with a join, but it's just a simple example to demonstrate the concept of an inline sub-query.

But you'll see the main rule, that sub-queries have to work the same way as main-queries. It has Select, it has From, it has Where. Same rules, same syntax, just the added rule that it can only return one row.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
robertafricker
robertafricker
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 36
Hi NJ,

Thanks for the site, I hadn't noticed it before. I checked it out, and it looks like a good resource.
I am going to submit a different type of query question later tonight, so please keep an eye out...Wink

=Roberta-
robertafricker
robertafricker
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 36
Hi rbarryyoung,

Thank you for breaking down the "IN" and "EXISTS" definitions.
The thing is, I am just not quite there yet as far as knowing in advance what I would like the result set to look like.
Where I am is in trying to figure out how to interpret a query piece by piece and then turn it into syntax.
So, what I'm going to do is submit another post titled, "Interpreting the Query" and ask some specific questions about "building" the syntax.
Once I understand this more clearly, I will understand result sets more clearly. Also, SEEING a result set is always so much more helpful as Gail sent me for WITH CUBE and WITH ROLLUP.
Your explanations are simple and clear, so pls keep an eye out for the next post, "Interpreting the Query." Thanks!

-Roberta-
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search