How to do multiple SQL joins so that I get back entities that match certain criteria?

Question

I have the following table (it's a list of bannerids and available sizes for each):

bannerid, bannersize
--------------------
1, 16
1, 32
1, 128
2, 16
2, 32
2, 128
2, 256
2, 512
3, 16
3, 32
3, 64
3, 128
3, 256
3, 512
3, 1024

I need to be able to generate a list of all bannerid that have all the possible sizes (16, 32, 64, 128, 256, 512, 1024)

So in this example, the result should be:

bannerid
--------
3, 16
3, 32
3, 64
3, 128
3, 256
3, 512
3, 1024

Since bannerid 3 has all the possible sizes as mentioned above (1 and 2 were missing few sizes).

What's a good way to achieve this? I assume there's a better way than doing a join 7 times on the same table and checking that every possible size combination exists.

I also want a way to control which bannerid get displayed: (a) those matching all sizes, or (b) matching some sizes as I specify.

have you considered using a `where` clause that uses `in` to accept several possible values? http://www.w3schools.com/sql/sql_in.asp — ian, Oct 21 '12 at 00:21
I believe this would be more complicated than using a simple IN clause. — James, Oct 21 '12 at 00:23
I see, pardon me, I missed that you wanted results that only matched all the conditions. — ian, Oct 21 '12 at 00:47
According to this question it either takes multiple joins or an aggregate, but the aggregate is slower http://stackoverflow.com/questions/4763143/sql-for-applying-conditions-to-multiple-rows-in-a-join — ian, Oct 21 '12 at 00:51

score 4 · Accepted Answer · answered Oct 21 '12 at 12:32

4

Try this :

select b.* from banners b inner join
( select bannerid,count(bannersize) bl from banners
group by bannerid
having count(bannersize) = (select count(distinct(bannersize)) cl from banners) ) ab
on b.bannerid=ab.bannerid

Also you can try it on Sqlfiddle

answered Oct 21 '12 at 12:32

Ertunç

819
1
9
21

Excellent!, +1 for introducing me to Sqlfiddle. – James Oct 21 '12 at 12:41
@Maverick thanks to stackoverflow then, cause I learnt sqlfiddle here. Also there is jsfiddle you may be interested in – Ertunç Oct 21 '12 at 12:43

score 1 · Answer 2 · answered Oct 21 '12 at 02:49

You haven't posted DB you're using, if you are using SQL Server 2005+ (or Oracle 11g+) you can do it using PIVOT

Query:

select * from banner
pivot (count(bannersize) for bannersize in ([16], [32], [64], [128], [256], [512], [1024])) as bann
where [16] > 0 and [32] > 0 and [64] > 0 and [128] > 0 and [256] > 0 and [512] > 0 and [1024] > 0

gives you output:

BANNERID    16  32  64  128 256 512 1024
3           1   1   1   1   1   1   1

you can achieve '(b) matching some sizes as I specify' by modyfing where clause.

This output differs from requested, but you can 'fix' it by using this query as a subquery:

select * from banner
where bannerid in
(
select bannerid from banner
pivot (count(bannersize) for bannersize in ([16], [32], [64], [128], [256], [512], [1024])) as bann
where [16] > 0 and [32] > 0 and [64] > 0 and [128] > 0 and [256] > 0 and [512] > 0 and [1024] > 0
)

Looks like something that would work, but would need this modified to work with MySQL. — James, Oct 21 '12 at 10:27
@Maverick here is article that shows how to do pivot in mysql: http://www.artfulsoftware.com/infotree/queries.php#78 — Piotr Sobiegraj, Oct 21 '12 at 11:55

score 0 · Answer 3 · answered Oct 21 '12 at 00:46

0

Q: My table ... is a list of bannerids and available sizes for each. I need to be able to generate a list of all bannerid that have all the possible sizes .

A: it sounds like you just want everything in the table :)

select * from mytable order by bannerid

If there are other columns in the table you wish to exclude, then:

select bannerid, bannersize from mytable order by bannerid

If you just want the available sizes for one bannerid (e.g. "3"), then

select bannerid, bannersize from mytable where bannerid = 3

If you want the available sizes from some specific bannerids (e.g. "1" and "3"), then

select bannerid, bannersize from mytable where bannerid in (1, 3)

answered Oct 21 '12 at 00:46

paulsm4

114,292
17
138
190

If this is a stored procedure, don't use '*'. List out the field names. – Yatrix Oct 21 '12 at 10:26
I think you misunderstood my question. I don't want to select all rows where bannerid = 3. I want to select all "complete" rows, ie, all rows where that have complete sizes. So, for instance, bannerid 3 should show up only if it has all sizes available. – James Oct 21 '12 at 10:49

How to do multiple SQL joins so that I get back entities that match certain criteria?

3 Answers3