Combining multiple mysql queries into one result

Question

I'm using php my get results from a mysql table. I want to run multiple conditional statements to return a list of unique results. let's say I have a table about houses on my street and my table looks like this:

House Number | Attribute | Value
-------------------------------
23           | Colour    | White
23           | Stories   | 2
24           | Stories   | 1
25           | Colour    | Blue

Notice house number 23 appears twice How would I word a mysql query to return all houses that are white AND have two stories? in this case, it would return just one result - 23.

I hear what you're saying - why don't i just make 'colour' and 'stories' the column names. well, the reason is because in my example, a house can have two different colours: two different values for the same attribute name. A house could have two rows, one where attribute is colour and value is white, and another where attribute is also colour but the value is purple. As long as a house has a row with colour:white AND a row with stories:2 it will return positive in the query and get included in the result

Now, once solution would be to run two different queries: one query that matches white houses and returns an array, and a second query that matches houses with two stories and returns an array, then I can use php to compare the two arrays and see what entries appear in both arrays, pull them out and put them into a final array. But this involves calling two mysql queries. Is there a way of combining the queries on the mysql end?

Eugen Rieck · Accepted Answer · 2015-02-05 02:35:13Z

4

You want a self-join:

SELECT
  A.`House Number` AS House
FROM
  Houses AS A
  INNER JOIN Houses AS B ON A.`House Number`=B.`House Number`
WHERE
  A.Attribute='Colour' AND A.Value='White'
  AND B.Attribute='Stories' AND B.Value='2'

answered Feb 5, 2015 at 2:35

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CragMonkey · Accepted Answer · 2015-02-05 03:18:19Z

2

You can nest your SELECT statements like this:

SELECT DISTINCT (`House_Number`) AS  `House_Number` 
FROM  `table` 
WHERE  `House_Number` 
IN (
    SELECT DISTINCT (`House_Number`) AS  `House_Number` 
    FROM  `table` 
    WHERE  `Attribute` =  'Colour'
    AND  `Value` =  'White'
)
AND  `Attribute` =  'Stories'
AND  `Value` =  '2';

Edit: Not quite as pretty as using an INNER JOIN, but still effective.

To build upon the INNER JOIN method @Eugen posted while I was typing up my original response, you may consider including DISTINCT, like this:

SELECT DISTINCT(A.`House_Number`) AS  `House_Number`
FROM `table` AS A
INNER JOIN `table` AS B ON A.`House_Number` = B.`House_Number` 
WHERE A.Attribute =  'Colour'
AND A.Value =  'White'
AND B.Attribute =  'Stories'
AND B.Value =  '2'

The reason being that in case the same attribute were to be recorded twice, say like this:

House Number | Attribute | Value
-------------------------------
23           | Colour    | White
23           | Colour    | White
23           | Stories   | 2
24           | Stories   | 1
25           | Colour    | Blue

...then you would wind up with "23" being returned twice, unless you used DISTINCT

edited Feb 5, 2015 at 3:18

answered Feb 5, 2015 at 3:00

CragMonkey

8482 gold badges11 silver badges23 bronze badges

4 Comments

Eugen Rieck Over a year ago

While this query certainly works, please understand, that using a subquery on a large table in MySQL still leads to a massive performance penalty. This is not the users's fault, but a quirk in the current implementation of subqueries in MySQL, that is good to know.

Eugen Rieck Over a year ago

This is not given in the OQ, but I assume having two completely identical rows, such as (23, Colour, White) is not in the best interest of the application - it should be rejected either by a unique key or at least by the application.

CragMonkey Over a year ago

Agreed on both points. Left the subquery version in case it helps someone who isn't comfortable with joins yet, who then would also me more likely to be working with smaller tables. Also, while it is certainly ideal to ensure that no duplicate records ever enter the data table, coding mistakes happen and bad data can be imported so I believe in adding little tweaks like that for preventing potential downstream issues.

Eugen Rieck Over a year ago

Your input about duplicate rows is really usefull! +1 - I would have assumed that this is ruled out, while it certainly is not!

Sameeraa4ever · Accepted Answer · 2015-02-05 02:38:36Z

1

Try this

  select id from table 
    where Attribute='Colour' and Value='White' 
    and id in (select id from table where Attribute='Stories' and Value='2')

answered Feb 5, 2015 at 2:38

Sameeraa4ever

5687 silver badges20 bronze badges

1 Comment

Eugen Rieck Over a year ago

While this query certainly works, please understand, that using a subquery on a large table in MySQL still leads to a massive performance penalty. This is not the users's fault, but a quirk in the current implementation of subqueries in MySQL, that is good to know.

Collectives™ on Stack Overflow

Combining multiple mysql queries into one result

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related