SQL Select problem (subquery needed??)

deian · January 31st, 2004, 04:07 PM

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

planoie · February 1st, 2004, 01:10 AM

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.

joefawcett · February 1st, 2004, 07:44 AM

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

deian · February 1st, 2004, 07:51 AM

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

Jeff Mason · February 2nd, 2004, 01:15 PM

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

deian · February 2nd, 2004, 01:51 PM

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.

stephanel · February 2nd, 2004, 02:22 PM

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

deian · February 3rd, 2004, 02:40 PM

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)