Hi Julian
U tried UNION



 CREATE VIEW directory_listings
 AS
(
SELECT d.directID, d.busname, d.description, ad.address1, ad.address2,
 ad.city, ad.state, ad.zip, ad.country
 FROM directory d ,members ad
where  d.usememberaddress = 1
and d.memberID = ad.memberID

union

 SELECT d.directID, d.busname, d.description, ad.address1, ad.address2,
ad.city, ad.state, ad.zip, ad.country
FROM directory d ,directory_contact  ad
where  d.usememberaddress <> 1
and     members ad ON d.memberID = ad.memberID
 directory_contact ad ON d.directID = ad.directID
)

With Regards
Ravinder S Dhillon
----- Original Message -----
From: <julian@f...>
To: "sql language" <sql_language@p...>
Sent: Tuesday, September 10, 2002 10:26 AM
Subject: [sql_language] If statements with SELECT???


> Hi All,
>
> I have a problem that I've been working on for a few days now. I'm trying
> to create a record set that pulls from various different tables, depending
> on data within the core table. There are 4 tables involved in this query;
> members, directory, directory_address, directory_contact (shown beloW)...
>
> /* *************************  MEMBERS TABLE  ************************** */
> CREATE TABLE members
> (
> memberID int IDENTITY NOT NULL
> PRIMARY KEY,
> sal varchar(12) NOT NULL,
> firstname varchar(40) NOT NULL,
> lastname varchar(40) NOT NULL,
> company varchar(40) NULL,
> position varchar(40) NULL,
> address1 varchar(40) NOT NULL,
> address2 varchar(40) NULL,
> city varchar(40) NOT NULL,
> state varchar(40) NULL,
> zip varchar(12) NULL,
> country char(2) NOT NULL
> tel varchar(24) NOT NULL,
> fax varchar(24) NULL,
> email varchar(60) NOT NULL,
> website varchar(120) NULL
> )
> /* *************************  DIRECTORY TABLE  ************************ */
> CREATE TABLE directory
> (
> directID int IDENTITY NOT NULL
> PRIMARY KEY,
> memberID int NOT NULL,
> busname varchar(40) NOT NULL,
> description varchar(800) NULL,
> usememberaddress bit NOT NULL
> DEFAULT 1,
> usemembercontact bit NOT NULL
> DEFAULT 1,
> )
> /* *********************  DIRECTORY_ADDRESS TABLE  ******************** */
> CREATE TABLE directory_address
> (
> directID int NOT NULL
> PRIMARY KEY,
> address1 varchar(40) NOT NULL,
> address2 varchar(40) NULL,
> city varchar(40) NOT NULL,
> state varchar(40) NULL,
> zip varchar(12) NULL,
> country char(2) NOT NULL
> )
> /* *********************  DIRECTORY_CONTACT TABLE  ******************** */
> CREATE TABLE directory_contact
> (
> directID int NOT NULL
> PRIMARY KEY,
> tel varchar(24) NOT NULL,
> fax varchar(24) NULL,
> email varchar(60) NOT NULL,
> website varchar(120) NULL
> )
>
> The query calls 'directory' and depending on the value
> of 'directory.usememberaddress' and 'directory.usemembercontact', data is
> called from either 'members', 'directory_contact' or 'directory_address'.
> This was a view I tried to create as an experiment, it shows what I'm
> aiming for...
>
> CREATE VIEW directory_listings
> AS
> SELECT d.directID, d.busname, d.description, ad.address1, ad.address2,
> ad.city, ad.state, ad.zip, ad.country
> FROM directory d
> (
> IF d.usememberaddress = 1
> BEGIN
> JOIN members ad ON d.memberID = ad.memberID
> END
> ELSE
> BEGIN
> JOIN directory_contact ad ON d.directID = ad.directID
> END
> )
>
> There would also be an element to determine the contact information, not
> shown here. There are other possibilities that I can do, ie. creating a
> really wide recordset will all the information joined, then I could use
> asp to filter out the correct fields by seeing which were null. I could
> also create a recordset and the open subsequest recordsets depending on
> the data from each record.
>
> These options I'm assuming create a larger resource drain and on a busy
> site, this could burdon the server unnecessarily.
>
> I look forward to recieving any help you can offer.
>
> Thanks in advance,
> Jules F.