TechArena Community How can I convert 2 queries into single query

#1
04-05-2009
 Member Join Date: Feb 2008 Posts: 1,260
How can I convert 2 queries into single query

I have the following table and I try to extract all lines with the B value for the fields RefD and all lines whose RefA fields to the value B and the value of their RefD fields is among those with value B for the fields RefD. If my table contains:
RefA ..... RefD
..K ........W
..Z ....... E
..A ........ B
..C ........ B
D ........ B
B ........ C
B ........ H

the result is that waiting

RefA RefD ....
A...... B
C ..... B
D ..... B
B ..... C

At the moment I made the two requests
the first on my table
Code:
`SELECT * FROM TABLE WHERE RefD=B`
And the second on the outcome of the requests
Code:
`SELECT * FROM TABLE WHERE RefD=A OR RefD=C OR RefD=D`
I want to know if it is possible to do this in one query.
#2
04-05-2009
 Member Join Date: Jan 2008 Posts: 1,515
Re: How can I convert 2 queries into single query

When you write
Quote:
 extract all lines with the B value for the fields RefD and all lines whose RefA fields to the value B and the value of their RefD fields is among those with the value B for the fields RefD.

Quote:
 extract all lines with the B value for the fields RefD and all lines whose RefA fields to the value B and the value of their RefD fields is among those not having value B for the fields RefD.
If so:

Code:
```  SELECT *
FROM TABLE
WHERE RefD = 'B'
OR (RefA = 'B' AND RefD <> 'B')```
#3
04-05-2009
 Member Join Date: May 2008 Posts: 271
Re: How can I convert 2 queries into single query

A union is sufficient

Code:
```SELECT * FROM TABLE WHERE RefD=B
UNION
SELECT * FROM TABLE WHERE RefD IN (A, C, D)```
#4
04-05-2009
 Member Join Date: Feb 2008 Posts: 1,260
Re: How can I convert 2 queries into single query

@ Modifier

No it does not work because if you apply this solution to my example the line
"B ........ H" will appear which is not needed.

@ XSI

Except that I do not know the values A, C, D in advance, that is why I make 2 requests at this time.
#5
04-05-2009
 Member Join Date: May 2008 Posts: 271
Re: How can I convert 2 queries into single query

Use a subquery:

Code:
```SELECT * FROM TABLE
WHERE RefD = B OR (RefA=B AND RefD IN ( SELECT RefD FROM TABLE WHERE RefD=B))```

 Tags: