Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Query Building Tips (Need Them!) Expand / Collapse
Author
Message
Posted Sunday, March 30, 2008 7:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2008 1:41 PM
Points: 35, 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!

Post #476756
Posted Sunday, March 30, 2008 9:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:38 PM
Points: 156, 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
Post #476762
Posted Sunday, March 30, 2008 9:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #476771
Posted Sunday, March 30, 2008 11:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2008 1:41 PM
Points: 35, 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-
Post #476778
Posted Monday, March 31, 2008 6:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #476916
Posted Monday, March 31, 2008 6:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #476920
Posted Monday, March 31, 2008 6:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 35,359, Visits: 31,898
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #476935
Posted Monday, March 31, 2008 11:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #477144
Posted Monday, March 31, 2008 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2008 1:41 PM
Points: 35, 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...;)

=Roberta-
Post #477396
Posted Monday, March 31, 2008 10:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2008 1:41 PM
Points: 35, 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-
Post #477398
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse