CODEWITHSUNDEEP
mysqlsqljoinrelational-database
James
James

Reputation: 1689

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

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.

Upvotes: 0

Views: 152

Answers (3)

Ertunç
Ertunç

Reputation: 829

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

Upvotes: 4

Piotr Sobiegraj
Piotr Sobiegraj

Reputation: 1783

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
)

Upvotes: 1

paulsm4
paulsm4

Reputation: 121659

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)

Upvotes: 0

Related Questions