CODEWITHSUNDEEP
phpmysql
BlekStena
BlekStena

Reputation: 345

How to check multiple rows in the database

Say I have 2 columns, sizes and colors, and the content of the table went like this:

sizes  colors

large  red
large  green
small  green

If I wanted to retrieve a record which had large in sizes table and green in colors table I would do something like this:

$q = "SELECT * FROM tablename WHERE sizes='large' and colors='green'";

But what if I wanted to retreive large ONLY if it had both red and green in the rows, as above?

Upvotes: 0

Views: 80

Answers (1)

Kickstart
Kickstart

Reputation: 21533

You appear to need to check 2 rows that match. A self join is probably the easiest way to do it:-

SELECT *
FROM SomeTable a
INNER JOIN SomeTable b
ON a.sizes = b.sizes
WHERE a.sizes = 'large'
AND a.colors = 'red'
AND b.colors = 'green'

Avoiding using a JOIN, but this only returns the size (do you have other columns that you want to return?):-

SELECT sizes, COUNT(DISTINCT colors) AS colour_count
FROM SomeTable
WHERE sizes = 'large'
AND colors IN ('red', 'green')
GROUP BY sizes
HAVING colour_count = 2

Upvotes: 3

Related Questions