Link to home
Create AccountLog in
Avatar of basefilm
basefilm

asked on

SQL Query -- Conflicting 'Where' params

I'm no SQL expert and could use some advice.

I'll explain the problem by starting with a simple query and building on it, to show the point where I'm getting stuck.

SELECT size from Table1
WHERE color='blue';

Now, I also need to select another field from Table1, but where color is different, as such:
SELECT param2 from Table1
WHERE color='red';

The result of the above two queries would be a single record, not two records.

So how do I combine two SQL queries with opposing 'WHERE' clauses into one query?  This seems like it should be possible--not necessarily in the 'WHERE clause, but otherwise possible.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of stalhw
stalhw

SELECT T1.size, T2.param2
FROM Table1 T1, Table1 T2
WHERE T1.color='blue' AND T2.color='red'

But it's really not clear what you are trying to accomplish...
Is there only one row where color is blue ? (and one where it's red)
Or to use a more modern syntax of the above query:
SELECT  T1.size,
        T2.param2
FROM    Table1 T1
        CROSS JOIN Table1 T2
WHERE   T1.color = 'blue'
        AND T2.color = 'red'

Open in new window

@basefilm

Both the answers of matthewspatrick and stalhw  are correct.
The difference between both queries is the execution plan

The first will run opun the table (or index on color) and do this Twice

The second will make a copy of the table (adding all fields double) and will run upon the table (or index on color) and do this also twice.

For an Query as this I would prefer Query 1 as the Cost for this query is the lowest.

If the values don't change much in this table you might even add the (nolock) specifier but then you must be sure there are not much changes.

SELECT
    (  
	SELECT [u]top 1[/u] size 
	from Table1 (nolock)
	WHERE color='blue'
    ) AS Item1,
    (  
	SELECT [u]top 1 [/u]param2 
	from Table1(nolock)
	WHERE color='red'
    ) AS Item2

Open in new window





@ matthewspatrick :

just added a Top 1 to ensure it's only 1 record you get
SELECT
    (  
	SELECT [u]top 1[/u] size 
	from Table1
	WHERE color='blue'
    ) AS Item1,
    (  
	SELECT [u]top 1 [/u]param2 
	from Table1
	WHERE color='red'
    ) AS Item2

Open in new window



@  stalhw (and  acperkins)
if 1 color has more records you also need a Top 1 to ensure you have only 1 records
SELECT  TOP 1
        T1.size,
        T2.param2
FROM    Table1 T1
        Cross JOIN Table1 T2
WHERE   T1.color = 'blue'
        AND T2.color = 'red'

Open in new window

poor_beggar,

I had considered using TOP 1, but decided not to, because if either sub-select returned more than one row I would expect that the Asker's overall approach should be revised.

That is why I led my comment with "Assuming each sub-select returns only 1 value..."

:)

Patrick
There seems to be some ambiguity in your question, primarily with whether or not there can only be one size with the color blue and one param2 with the color red. This really needs to be addressed. Also, you show topics of Oracle and SQL Server - is it one or the other or both?
This is a different approach than what has been mentioned so far, so it may or may not be what you were looking for
select color, size, param2,
 case color when 'blue' then size when 'red' then param2 else null end  val
from Table1
where color in ('blue', 'red') -- optional

Open in new window

this returns a single record from two different fields, depending on the color.
gatorvip,
you are wrong, your query will return 2 records:

blue, aSize, aParam2, aSize
and
red, aSize, aParam2, aParam2
@stalhw

It will return two rows, yes. That is by design - OP asked how to combine two SQL queries into one and that's what my answer was supposed to show.

If you re-read my post, I did specify that it may not be what he was looking for. Every other answer prior to mine had focused on joining table, which is how you interpreted the OP. My interpretation was different, hence my answer.

Without additional input from OP with sample data, I believe this is a moot discussion.