Join/Group By/Having combo giving me trouble

bunner_bob

I'm trying to query three tables and find results where the primary table matches ALL the criteria rows in the second table, and ANY of the criteria rows in the third table.

The tables (simplified):

// table entries

entryId
   1
   2

// table entriesEthics

entryId  ethicsId
   1		 1
   1		 2
   2		 1

//table entriesCategories

entryId	categoryId
   1		 1
   1		 2
   2		 1
   2		 2

I'm trying to find all entries that have ethicsId 1 AND ethicsId2, and have categoryId 1 OR categoryId 2 (or both).

My query:

SELECT entries.*, entriesEthics.ethicsId AS entryEthicsId 

FROM entries JOIN entriesEthics ON entries.entryId=entriesEthics.entryId 
JOIN entriesCategories ON entries.entryId=entriesCategories.entryId 

WHERE (entriesEthics.ethicsId=1 OR entriesEthics.ethicsId=2) AND
(entriesCategories.categoryId=1 OR entriesCategories.categoryId=2) 

GROUP BY entryId, entryEthicsId 

HAVING count(entryEthicsId) =2

The result:

Array
(
    [0] => Array
        (
            [entryId] => 1
            [hidden] => 0
            [entryEthicsId] => 1
        )

[1] => Array
    (
        [entryId] => 1
        [hidden] => 0
        [entryEthicsId] => 2
    )
 )

As you can see, I'm getting two copies of entry 1, which I'm pretty sure is because I'm creating the field entryEthicsId (which I need in order to limit my result to only those entries that have BOTH ethicsIds).

I tried using DISTINCT but again, since the rows are different (thanks again to my adding entryEthicsId) it was no help.

I'd love to avoid having to post-process the results to get rid of the duplicates, but I'm having trouble figuring out how.

Anyone who lives, breathes and/or dreams SQL queries have a tip for me?

leatherback

Hi there,

First off.. What I try to do to easy my life.. I make unique fieldnames throughout the database.

So a primary identifier from table a becomes a nr in table B:
Table a
Ta_id

Table b
Tb_ta_nr

This way, you know from the table straight which col is referiing to which table. You do not have to:
- Use table names in the field identifiers anymore
- Rename fields

you can just select * from tableA left join TableB on ta_id = ta_ta_nr

That brings me to the second problem: You link using 'where columa=columb'. Try to have a look at joins. They were made for this.

After you have done this, please post again, I think it may have solved many of your issues...

Lars_Berg

select *
  from entries
 where exists
      (select * 
         from entriesEthics 
        where entries.entryId = entriesEthics.entryId
          and entriesEthics.ethicsId in (1,2)
        group by entryid
       having count(*) = 2)
   and exists
      (select *
         from entriesCategories
        where categoryId in (1,2)
          and entryid = entries.entryid)

bunner_bob

Lars - that appears to work great, but doesn't it use correlated subqueries, which according to the manual can be slow? This site is going to see some heavy traffic, and I want to avoid anything that might be slow.

I admit I have a limited understanding of what makes a subquery correlated.

bunner_bob

leatherback wrote:
So a primary identifier from table a becomes a nr in table B:
Table a
Ta_id

Table b
Tb_ta_nr

This way, you know from the table straight which col is referiing to which table. You do not have to:
- Use table names in the field identifiers anymore
- Rename fields

you can just select * from tableA left join TableB on ta_id = ta_ta_nr

On the other hand, keeping foreign keys the same name as the primary key in the original field makes it a lot easier to keep track of what is connecting to what. In any case the result is the same - I end up with multiple results since the renamed field is still different in value. Same result, different field names.

leatherback wrote:
That brings me to the second problem: You link using 'where columa=columb'. Try to have a look at joins. They were made for this.

Um - are you sure you're READING this post? I'm using nothing BUT joins, and no 'where columna=columnb'.