Query syntax question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query syntax question

Author

Topic

jaycupw
Starting Member

12 Posts

Posted - 2006-04-18 : 13:06:23

Hi,

I need to query several tables in order to get the data that I want. What is the correct syntax for multiple SELECT statements? Do I need a seperator or similar?

Example:

select something, somethingelse
from somewhere where something='1'
AND somethingelse='True'

select anotherthing
from anywhere where anotherthing='1'

select item
from stuff
where item='2'

Thanks,
--Jakob

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-18 : 13:44:08

Nope - a space will do. That will give you multiple resultsets.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-18 : 13:44:26

Jacob,
Not clear enough what u need
Can u post some sample data and expected results?

Srinika

jaycupw
Starting Member

12 Posts

Posted - 2006-04-18 : 15:57:24

Nr - thanks.

Srinika,

Here is one query sample:

select UserID, AttributeDefinitionID, AttributeValue
from dnn_UcanUseUA_UserAttributes
where (AttributeDefinitionID='30' AND AttributeValue='True')
AND (AttributeDefinitionID='44' AND AttributeValue='Film')

One problem I'm having is that it doesn't return the data that is present (probably syntax error on my part). It runs fine if I remove one of the "WHERE" statements (either one) but not if both are present.

Any suggestions?
Thanks!!

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-18 : 15:57:33

The query as you've written it will be fine in something like Query Analyzer. However, most application languages will processs the results as individual result sets. So if you sent all 3 commands as a single command you would only get back the first result set initially, unless you told it specifically to get the next result set, processed those results, then told it to get the next result set. Etc.

Dalton

jaycupw
Starting Member

12 Posts

Posted - 2006-04-18 : 16:02:15

Dalton,

Thanks.

Not sure how to implement your suggestion. To give more background as to what I'm trying to accomplish, please see this post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64819

--Jakob

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-18 : 16:42:18

quote:
Originally posted by jaycupw

One problem I'm having is that it doesn't return the data that is present (probably syntax error on my part).

If syntax error u won't be able to run the query without errors

The following doesn't give any results :

select UserID, AttributeDefinitionID, AttributeValue
from dnn_UcanUseUA_UserAttributes
where (AttributeDefinitionID='30' AND AttributeValue='True')
AND (AttributeDefinitionID='44' AND AttributeValue='Film')

Because there cannot be a record which has
AttributeDefinitionID= '44' and '30' being at the same time

u may need an "OR"

eg.

select UserID, AttributeDefinitionID, AttributeValue
from dnn_UcanUseUA_UserAttributes
where (AttributeDefinitionID='30' AND AttributeValue='True')
OR (AttributeDefinitionID='44' AND AttributeValue='Film')

Srinika

jaycupw
Starting Member

12 Posts

Posted - 2006-04-18 : 17:48:35

Srinika,

Thanks. Yup, this is my dilemma. "OR" returns a lot I don't want. I have to verify that both AttributeDefinitionID values are what they are. How can I accomplish that? I have a feeling I am approaching this all wrong but am at a loss as to what to do differently....I need to find all users who have these two attributes.

Thanks again,
--Jakob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 18:17:38

Could you provide an example of what data is being returned with the OR and which parts you didn't want it to return?

Tara Kizer
aka tduggan

jaycupw
Starting Member

12 Posts

Posted - 2006-04-18 : 18:43:25

Tara,

Thanks for responding.

When using "OR", I don't think I'm actually getting accurate results. I need to verify both settings are correct.

I'm trying to "filter" (or find) users that have selected "Yes" to be on an "On-Call" list ("AttributeDefinitionID='30' AND AttributeValue='True'"). The user also selects which categories (OnCallCat) that applies to them. I used "Film" as an example.

For each user that has these two attributes, I need to return

FirstName
LastName
Email
PhoneNumber
Region
Country

which are located in different tables, somehow match everything up to each applicable user, and post everything in a HTML table (one user per row).

I hope this makes sense.

Thanks,
--Jakob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 18:46:11

If you could post sample data for the tables involved plus the expected result set using the sample data, we'd be able to help you easier and faster.

Tara Kizer
aka tduggan

jaycupw
Starting Member

12 Posts

Posted - 2006-04-18 : 21:11:37

Tara,

Sure.

I'm confused as to HOW to put all of this together in a good way.
Please keep in mind the final result of all of this will be displayed in a web browser.

========================================

1. Get user info - part 1

Table Name: dnn_Users
Columns: UserID, FirstName, LastName, Email

Query:
select UserID, FirstName, LastName, Email
from dnn_Users

Returns:
6, John, Smith, john@smith.net
7, Joe, Schmo, joe@schmo.net
8, Homer, Simpson, homer@doh.com

========================================

2. Get user info - part 2

I have yet to discover where "Region", "Country", and "Phone" are stored...(frown)

Query:
select UserID, Region, Country, Phone
from dnn_?????

Should return:
6, MA, USA, 617-123-1234
7, DC, USA, 212-123-1234
8, XX, USA, 123-555-1234

========================================

3. Check who's on "On-Call List"

Table Name: dnn_UcanUseUA_UserAttributes
Columns: UserID, AttributeDefinitionID, AttributeValue

select UserID, AttributeDefinitionID, AttributeValue
from dnn_UcanUseUA_UserAttributes
where (AttributeDefinitionID='30' AND AttributeValue='True')

Returns:
6, 30, True
8, 30, True

========================================

4. Check which categories are selected for "On-Call List" (will look for "Film" in this example)

Table Name: dnn_UcanUseUA_UserAttributes
Columns: UserID, AttributeDefinitionID, AttributeValue

select AttributeDefinitionID, AttributeValue
from dnn_UcanUseUA_UserAttributes
where (AttributeDefinitionID='44' AND AttributeValue='Film')

Returns:
8, 44, Film

========================================

The goal is to have a result (of ALL of the above) in a table displayed in a web page.
An example would be:

-------------------
ON-CALL LIST - FILM
-------------------
___________________________________________________________________
Name | Region | Country | Email | Phone |
-------------------------------------------------------------------
Homer Simpson | XX | USA | homer@doh.com | 123-555-1234 |
-------------------------------------------------------------------

Thanks again,
--Jakob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources