SQL Query

[Jeremy21b]

Hello.

I am having some problems creating an SQL query to do exactly what I want. Say the table is about widgets, I want to query only widgets available in both blue and red. If I just do WHERE Color = 'Red' or Color = 'Blue', it will get everything in either color. So how can I make it only return results that match both criteria? I could write some code to get around this, but it would mess up the recordset page size and recordset total records. So can I achieve this with just the SQL statement? Thanks.

 

[Genimuse]

Can you give us a quick rundown of your table structure? I assume from your description that you're joining two tables.

 

[Jeremy21b]

well I'd rather not give my exact tables, but stick with the widgets example. Say the table has the following columns: widgetname, color, size. It is just 1 table, but I want to only return results that are available in both blue or red, but not just one color.

 

[snowboardr]

just use

WHERE color = 'Red' AND Color = 'Blue'

"AND" makes it so it only shows tables with those two values.


Now if this is a table join then just do this

SELECT t1.color1,t2.color2 FROM table1 as t1, table2 as t2 WHERE t1.color1='Red' AND t2.color2='Blue'

www.sitesd.com
ASP WEB DEVELOPMENT

 

[snowboardr]

make a note with my first example the two tables i hope are named differently. if not that won't work exactly.

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Genimuse]

Join the table on itself, one version where the color is red, the other where it's blue.

 

[Genimuse]

Outer join, that is.

 

[Jeremy21b]

It is just 1 table, but 'AND' wouldn't work because there are no specific rows that the Color is both. The color is one or the other. Say I wanted to get any rows in size 'large' and color is available in both red and blue.

 

[Jeremy21b]

There we go...Outer JOIN...I think that's my solution. What would the proper syntax to Outer JOIN a table on itself?

 

[snowboardr]

Your statements don't help me come to a conclusion, you are contradicting yourself.

[highlight]
So how can I make it only return results that match both criteria?
[/highlight]

[highlight]
The color is one or the other.
[/highlight]

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Jeremy21b]

each row has only one color....but for a particular size there are multiple colors. Genimuse understands though. I just need the syntax from him.

 

[Genimuse]

My SQL is kinda rusty (shameface), and I'm not in a position to test it, so off the top of my rusty head

SELECT * FROM myTable t1 OUTER JOIN myTable t2 WHERE t1.Color = 'Red' AND t2.Color = 'Blue'

Though don't hold your breath.

 

[Genimuse]

Wait, that's definitely wrong.

 

[snowboardr]

well your database structure isnt very good to be straigh forward with you. You should have all your colors in one table and just put the id of the color in the inventory row your waisting space and causing yourself a headache trying to Outer Inner and middle join ....



Jason

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Genimuse]

Like this:

SELECT *  FROM myTable t1 LEFT OUTER JOIN myTable t2 ON t1.IDfield = t2.IDField WHERE t1.Color = 'Red' AND t2.Color = 'Blue'

 

[Genimuse]

Even if the database was more normalized, you'd still need that kind of join. Separating the colors into a separate table (one with each product ID, one with product IDs as foreign keys and rows for each color) wouldn't simplify this query -- it would be identical, but on the color table. If you wanted the other non-color item data then you'd have to join it with the item table, complicating this query.

Doesn't mean it's not a good idea to normalize for other reasons, but it doesn't help this query at all.

 

[Jeremy21b]

Genimuse thank you very much. I think there is a problem with my table joining though. Using the widgets example, it is retrieving all the red widgets in that size that are also available in blue. I want to retrieve both the red & blue widgets though. Think of the red & blue widget as a package that is sold together. Thanks.

 

[Jeremy21b]

Nevermind, I think I figured it out now. Thanks a lot.

 

[Turkbear]

Hi,
Just in case you still wanted another way:

Select widgetname from widget_table where size = '10' and
color = 'red'
INTERSECT
Select widgetname from widget_table where size = '10' and
color = 'blue';

Will return all widgetnanes that are in size 10 and have
both red and blue colors in that size.