Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 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.
Reply With Quote
  #2  
Old 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.
I think from reading your example and your code that you wanted to 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 not having value B for the fields RefD.
If so:

Code:
  SELECT *  
  FROM TABLE  
  WHERE RefD = 'B' 
  OR (RefA = 'B' AND RefD <> 'B')
Reply With Quote
  #3  
Old 04-05-2009
XSI XSI is offline
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)
Reply With Quote
  #4  
Old 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.
Reply With Quote
  #5  
Old 04-05-2009
XSI XSI is offline
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))
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How can I convert 2 queries into single query"
Thread Thread Starter Forum Replies Last Post
Want to make query dependent on another query. MACE Software Development 4 01-02-2010 05:22 PM
MySQL query in a query Rail racer Software Development 3 21-07-2009 07:06 PM
Turn on MySQL query cache to speed up query performance DMA2Superman Software Development 3 07-07-2009 10:26 AM
Need to have multiple where queries in MySQL query Anti_Trend Software Development 1 03-07-2009 10:17 PM
Convert Mulitple .avi into Single DVD ARTHUR18 Windows Software 3 16-06-2009 11:15 PM


All times are GMT +5.5. The time now is 03:16 AM.