Tricky Query help 
Author Message
 Tricky Query help

Hi All,

  I have a rather tricky query to create and I wanted to see if I could get
some help. It may be near impossible to discribe but I will give it a shot.

I have 5 tables involved...  we will call them A - E

In this query, I need to find a specific record in table A, then check to
see if that exists in Table B. Table B could return several records from the
1 record in Table A.

If the record(s) are found in table B, Then I need to return a joined
recordset from tables A,C,D,E that match each returned record from Table B.

If there is no match in table B, Then I need to return just the records in
table A

Let me try again:

If "number" is in  A.number
    Then return all records from table B that B.number = A.number
        Then Join Tables A,C,D,E and return all records that match on
C.Number = B.Number For each B.Number from the first Query.
Else
    Return * From A Where A.number = "number"

I am still a rookie so this is rather complicated for me. I am not sure how
to Query and get results without creating a recordset, but instead use the
results to populate another query that does return a recordset. Actually it
is 2 non recordset Queries....  And to further my problem, how to store
multiple search criteria and exicute them all in one recordset. A temporary
table maybe??

As you can see by my ramblings, I am still rather confused..:)

Thanks

Doyle Johnson



Tue, 31 Dec 2002 03:00:00 GMT
 Tricky Query help

Doyle,

Questions of this type are best answered when you post table schemas (CREATE
TABLE's), sample data (INSERTs) and the output that you expect from the sample
data.

I have guessed at the table schemas ... I have also limited it to table A thru
C, since adding tables D and E should be fairly intuitive ...

create table A
(
 a1 int,
 adata varchar (5)
)

create table B
(
 b1 int,
 b2 int
)

create table C
(
 b2 int,
 cdata varchar (5)
)
go

insert into A values (1, 'abcde')
insert into A values (2, 'fghij')

insert into B values (2, 1)
insert into B values (2, 2)
insert into B values (2, 3)

insert into C values (1, 'xxxxx')
insert into C values (2, 'yyyyy')
insert into C values (3, 'zzzzz')

select A.*, C.*
from A
left outer join B
  on (A.a1 = B.b1)
left outer join C
  on (B.b2 = C.b2)

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


Quote:
> Hi All,

>   I have a rather tricky query to create and I wanted to see if I could get
> some help. It may be near impossible to discribe but I will give it a shot.

> I have 5 tables involved...  we will call them A - E

> In this query, I need to find a specific record in table A, then check to
> see if that exists in Table B. Table B could return several records from the
> 1 record in Table A.

> If the record(s) are found in table B, Then I need to return a joined
> recordset from tables A,C,D,E that match each returned record from Table B.

> If there is no match in table B, Then I need to return just the records in
> table A

> Let me try again:

> If "number" is in  A.number
>     Then return all records from table B that B.number = A.number
>         Then Join Tables A,C,D,E and return all records that match on
> C.Number = B.Number For each B.Number from the first Query.
> Else
>     Return * From A Where A.number = "number"

> I am still a rookie so this is rather complicated for me. I am not sure how
> to Query and get results without creating a recordset, but instead use the
> results to populate another query that does return a recordset. Actually it
> is 2 non recordset Queries....  And to further my problem, how to store
> multiple search criteria and exicute them all in one recordset. A temporary
> table maybe??

> As you can see by my ramblings, I am still rather confused..:)

> Thanks

> Doyle Johnson



Wed, 01 Jan 2003 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. tricky query - please help

2. Help with tricky SQL query

3. Need help with a tricky query

4. Help with a tricky SQL query

5. HELP: Tricky query....

6. A Tuff Tricky One - Please Help with Queries

7. Tricky update query

8. Tricky query question

9. Tricky UPDATE / Select SQL query problem.

10. tricky query

11. Tricky query question.

12. tricky query (for me atleast)


 
Powered by phpBB® Forum Software