Discuss "AND" select on single column in the MySQL Help forum on Dev Shed. "AND" select on single column MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Receive the tools necessary to be the rock star of your field. Our 12-month program teaches you the evolving world of multi-channel marketing as well as the complex issues and opportunities found in the industry.
ASP Free and Iron Speed Designer are giving away $5,500+ in FREE licenses. Iron Speed's RAD CASE toolset can save up to 80% of your coding time. One free license per week, one perpetual license per month! Download and Activate to enter!
Web development can be a daunting task, even for specialists. There is a lot of information to absorb and a lot of technologies to learn in order to manage a superior website. When trying to learn the ropes, developers need a reliable source to introduce new ideas that can be easily implemented. When working on large projects, even web veterans may run into a technology or an aspect of a technology that they are unfamiliar with.
Posts: 63
Time spent in forums: 16 h 37 m 11 sec
Reputation Power: 9
"AND" select on single column
I am trying to write a select that finds all objects that match a set of values for a column. In using the sample tables below, an "object" has "color" values that are stored in the table "object_color". An object can have zero to many colors.
I would want to select all objects that have a combination of colors. For example, I want to select all objects that are yellow, red, and green. Is such an "and" search possible in one select?
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
the full query:
Code:
select OBJECT.object_id
, OBJECT.other_attributes
from COLOR
inner
join OBJECT_COLOR
on OBJECT_COLOR.color_id = COLOR.color_id
inner
join OBJECT
on OBJECT.object_id = OBJECT_COLOR.object_id
where COLOR.color_name in ( 'yellow' , 'red' , 'green' )
group
by OBJECT.object_id
having count(distinct OBJECT_COLOR.color_id) = 3
if the color_ids are known, as they would be if they were selected from a dropdown with the MULTIPLE attribute, you can simplify to:
Code:
select OBJECT.object_id
, OBJECT.other_attributes
from OBJECT_COLOR
inner
join OBJECT
on OBJECT.object_id = OBJECT_COLOR.object_id
where OBJECT_COLOR.color_id in ( 14, 9, 37 )
group
by OBJECT.object_id
having count(distinct OBJECT_COLOR.color_id) = 3
Posts: 63
Time spent in forums: 16 h 37 m 11 sec
Reputation Power: 9
Would I be able to merge that query with another "or" criteria. For example, a "SIZE" table structured the same as "COLOR". Would the following be correct?
select O.OBJECT_ID
from OBJECT as O
left join OBJECT_COLOR as OC on OC.OBJECT_ID=O.OBJECT_ID
left join OBJECT_SIZE as OS on OS.OBJECT_ID=O.OBJECT_ID
where OC.COLOR_ID in (1,3,9) and OS.SIZE_ID in (2,7)
group by O.OBJECT_ID having count (distinct OC.COLOR_ID)=3
Also, I tend to use left join. Would there be a difference compared to using inner join as you showed?
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
Quote:
Originally Posted by tabbycat
Would the following be correct?
it might be -- try it and see
Quote:
Originally Posted by tabbycat
Also, I tend to use left join. Would there be a difference compared to using inner join as you showed?
ordinarily, yes, there is a big difference between LEFT OUTER JOIN and INNER JOIN
however, in a LEFT OUTER JOIN, if you have a condition in the WHERE clause on any column from the right table, then all unmatched left table rows will be tossed out, consequently the query will actually behave as an inner join
test your query, then test this one and tell me if there is any difference --
Code:
select O.OBJECT_ID
from OBJECT as O
left join OBJECT_COLOR as OC
on OC.OBJECT_ID=O.OBJECT_ID and OC.COLOR_ID in (1,3,9)
left join OBJECT_SIZE as OS
on OS.OBJECT_ID=O.OBJECT_ID and OS.SIZE_ID in (2,7)
group by O.OBJECT_ID
having count (distinct OC.COLOR_ID)=3
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
