Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 31st, 2004, 04:07 PM
Authorized User
 
Join Date: Dec 2003
Location: , Wales, United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Select problem (subquery needed??)

Hi,

I am having problems constructing what I seemed to be a pretty straightforward SQL select problem. Here's the details:

I have a table with 3 colums: type, language, data

how do I select the 'Data' where the 'Language' is 'langa' or 'langb' and type is 'typea' or 'typeb' (without bringing out all the data that is true for all typea, typeb, langa and langb together?)

Basically I want to filter the data to the languages of 'typea' or 'typeb' first, THEN filter that data to 'typea' or 'typeb'.

I'm thinking I might need to perform a subquery first?

I'm trying to display contents for a web site based on a language and datatype, and more variables soon for more complex data filtering like page number etc.

I'm using ASP.NET to generate the SQL to get the data from an Access DB.

Examples will get me on my way, this is really bugging me here and I've got a feeling it will be a simple SQL statement. Can anyone give me an example based on the above data?

Thanks so much.

Deian
Reply With Quote
  #2 (permalink)  
Old February 1st, 2004, 01:10 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You practically already wrote the SQL you need. ;)

SELECT data FROM table WHERE
(Language='langa' OR Language='langb')
AND (type='typea' OR type='typeb')

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #3 (permalink)  
Old February 1st, 2004, 07:44 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

If you only want to match where language is langa and type is typea or language is langb and type is typeb then you need a slightly different form:
Code:
SELECT data FROM table WHERE 
(Language = 'langa' AND type = 'typea') 
OR (Language= 'langb' AND type = 'typeb')
--

Joe
Reply With Quote
  #4 (permalink)  
Old February 1st, 2004, 07:51 AM
Authorized User
 
Join Date: Dec 2003
Location: , Wales, United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

that doesn't work though...
it brings out the other types and languages in the type and language fields.

it doesn't filter the data to what i ask it for.

any ideas??

thanks
Deian

Reply With Quote
  #5 (permalink)  
Old February 2nd, 2004, 01:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

You said:
Quote:
quote:
that doesn't work though...
it brings out the other types and languages in the type and language fields.

it doesn't filter the data to what i ask it for.
But earlier you said you wanted:
Quote:
quote:
Basically I want to filter the data to the languages of 'typea' or 'typeb' first, THEN filter that data to 'typea' or 'typeb'.
How does the WHERE clause, earlier posted by joefawcett:
Code:
WHERE 
(Language = 'langa' AND type = 'typea') 
OR (Language= 'langb' AND type = 'typeb')
not do that?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #6 (permalink)  
Old February 2nd, 2004, 01:51 PM
Authorized User
 
Join Date: Dec 2003
Location: , Wales, United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

no it doesn't i kno it's hard to believe i can't believe it either, if any of u wanna kno about this weird 'select not working' problem i'm getting please let me know, i'm willing to send the db to someone, i don't think my structure is wrong.

If I do a simple select it works, if i do a select where i filter it from one field and ask for type a or type b it's ok, or when i filter the language and ask for lang a or lang b, but when i ask it to filter the language and type 'or' filters it brings the whole db out.

This is really frustrating.

Reply With Quote
  #7 (permalink)  
Old February 2nd, 2004, 02:22 PM
Authorized User
 
Join Date: Jun 2003
Location: , Quebec, Canada.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Language is a reserved word for Access. You will need to change your column name. I would suggest that you add a prefix to your column name.

Stéphane Lajoie
Reply With Quote
  #8 (permalink)  
Old February 3rd, 2004, 02:40 PM
Authorized User
 
Join Date: Dec 2003
Location: , Wales, United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I think it's fixed now. My actual column name I was using was 'data_language'. I changed this to 'data_lang'.

The SQL thats worked was:

SELECT data_type, data_contents
FROM content
WHERE (data_type = 'title' OR data_type = 'slogan')
AND (data_lang = '" & langvar &"' OR data_lang = 'mixedlang')

(lang var being a variable allowing dynamic language changes from the user (in my case welsh or english) :) ).

All tests so far seem to be working.

Thanks for all your help guys. (I may be thick yes)

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL SubQuery mcinar SQL Language 2 March 3rd, 2005 12:51 PM
Correlated Subquery Rewrite Needed gstanden SQL Language 5 November 24th, 2004 02:39 PM
Correlated Subquery part of Select List gstanden SQL Language 1 November 24th, 2004 07:25 AM
help needed with a SQL select statement problem wslyhbb Java Databases 1 August 14th, 2003 07:30 AM



All times are GMT -4. The time now is 10:00 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.