Advanced Query

[Dubya008]

I have a table that has numeric values in it

id, Product, Attribute

 

It is a "cross-over" table that stores attribute values for products. There can be more than 1 attribute per product:

 

id    Product    Attribute

1      234          45

2      234          23

3      896          45

4      896          33

5      568          23

6      568          45

7      790          23

 

I need to create a Query that will return all of the products that have the same attributes.

I've tried the following which returns nothing

 

SELECT * FROM ProdAts

WHERE Attribute='45'

AND Attribute ='23'

 

This returns nothing. I believe that I understand why(No row contains both numbers)

 

If i use an OR instead of an AND i get some Products that have only 1 of the attributes.

 

I'm attempting to pull all products that have the same attributes.

 

Is this possible via SQL?

[eMonk]

Sorry, I read the question wrong.

[mikosiko]

options:

 

- If you know the attributes that you are looking for the select should be:

SELECT * FROM ProdAts
WHERE Attribute IN = (45,23, etc..etc);

 

- if you want to know which attributes are in more than one product:

SELECT attribute, count(id)
  FROM ProdAts
  GROUP BY attribute
  HAVING count(id) > 1

 

- if you want to get a list of the products associated to each attribute (list could be exploded at display time) :

SELECT attribute, GROUP_CONCAT(product) AS productlist
  GROUP BY attribute

 

- if you don't want a list:  (no tested in my side but should work)

SELECT a.attribute, a.product
  FROM ProdAts a
  WHERE a.attribute IN (SELECT attribute FROM ProdAts GROUP BY attribute HAVING count(attribute) > 1)
ORDER by a.attribute;

 

[Dubya008]

Those were all very helpful however none of them returned what I'm looking for. Given the example above here is what I want returned.

 

products 234 & 568 because they have both attributes 23 and 45

 

[Dubya008]

I'm thinking that this may be impossible from the same table. Would anyone with more experience point me in the right direction to even see if this is possible?

[DaiLaughing]

I know you can JOIN a table to itself to compare two different fields but can't quite see how to apply that (or even if you can).  It's first thing in the morning here so my brain is not yet turned on (and I mean really not working).  I'll have a look later if I get chance.

[mikosiko]

Those were all very helpful however none of them returned what I'm looking for. Given the example above here is what I want returned.

 

products 234 & 568 because they have both attributes 23 and 45

 

are you looking to return the product that have exactly the same attributes (nothing more, nothing less)?

[Dubya008]

yes exactly the same attributes nothing more nothing less.

So that I can create a dynamic query that will give me the products that have attribute 25 and 45. Or any other attributes that I need to add

[mikosiko]

in that case, if you are going to pass the attributes that you want as parameters to the query a simple modification to the third select that I gave you previously should work

 

SELECT product, GROUP_CONCAT(attribute ORDER BY attribute) AS gp
  FROM ProdAts
  GROUP BY product
  HAVING gp = CONCAT_WS(',',23,45);  // or replace here for the attributes that you want ordered asc.

 

that select produce this results:

234, '23,45'
568, '23,45'

[fenway]

You need to cross join, once for each attribute.

[ebmigue]

SELECT
    DISTINCT
    product, attributes
FROM
(
    SELECT DISTINCT *
    FROM
    (
         SELECT
              DISTINCT  attributes, COUNT(*) AS 'num_of_products'
         FROM
         (
               SELECT
                      DISTINCT product,  GROUP_CONCAT(attribute ORDER BY attribute ASC) AS 'attributes'
              FROM
                    prodats
             GROUP BY
                   product
         )w
         GROUP BY attributes
      )x
     WHERE num_of_products > 1
)y
NATURAL JOIN (
       SELECT
          DISTINCT product, GROUP_CONCAT(attribute ORDER BY attribute ASC) AS 'attributes'
       FROM
           prodats
       GROUP BY
           product
)z

 

I strongly suspect that there is an alternate way of expressing what you want.

 

Beware that this is might not be standard SQL and will not cross-compatible with other DBMS.

 

Just express your desire if you want this query explained, if it is correct in the first place.

 

Hope it helps.

[fenway]

No need for subquery -- really, just a CROSS-JOIN for each attribute -- it's not a complex query.

[ebmigue]

@fenway

 

I am interested to see your CROSS JOIN solution, w/o any subquery.

[fenway]

SELECT 
p.*
FROM Products AS p
CROSS JOIN ProdAts AS a1
CROSS JOIN ProdAts AS a2
WHERE 
( a1.attribute = '23' AND a1.product = p.id )
AND 
( a2.attribute = '45' AND a2.product = p.id )

[ebmigue]

SELECT 
p.*
FROM Products AS p
CROSS JOIN ProdAts AS a1
CROSS JOIN ProdAts AS a2
WHERE 
( a1.attribute = '23' AND a1.product = p.id )
AND 
( a2.attribute = '45' AND a2.product = p.id )

 

Well, that is for the OP to determine if that will satisfy what he wants.

[fenway]

@ebmigue: How so? That will do precisely what the OP requested -- find all products that have 2 matching attributes.

[mikosiko]

@ebmigue: How so? That will do precisely what the OP requested -- find all products that have 2 matching attributes.

 

@fenway: Yes your query works... but in Reply#7 the OP said:

"yes exactly the same attributes nothing more nothing less.

So that I can create a dynamic query that will give me the products that have attribute 25 and 45. Or any other attributes that I need to add"

..

 

for that case the OP will have to modify your query adding another (or as many) CROSS JOIN, and the necessary adjustments also in the WHERE clause....

 

I wonder if anyone did test the solution that somebody gave to the OP in Reply #8... which cover exactly what the OP was asking for... and in case that the OP also want to get all the information from the "Products" table (he was not asking for according to his example) modify the provided working query is simple:

 

SELECT a.*, GROUP_CONCAT(b.attribute ORDER BY b.attribute) AS gp
  FROM Products a
  JOIN ProdAts b ON a.id = b.product
  GROUP BY a.id
  HAVING gp = CONCAT_WS(',',23,45); // or replace here for the attributes that you want ordered asc.

 

well.. and having in mind the the OP never came back... maybe Reply# 8 just worked for him.

[ebmigue]

@fenway, @mikosiko

The query you both gave works only for rows with attribute values 23 and 45.

 

As I interpreted the OP's requirements, he needs all products that exactly have the same attribute values for each record in w/c they appear.

 

For instance:

 

product attribute

A            1

A            2

B            1

B            2

C            2

D            1

E            1

F            1

F            3

 

As he said:

Those were all very helpful however none of them returned what I'm looking for. Given the example above here is what I want returned.

products 234 & 568 because they have both attributes 23 and 45

So the query ought to return products A, B, D, and E.

A and B, because the records in w/c they appear, they have the same attribute values (1 and 2).

The same is true for D and E (they have the same attribute value, 1).

 

F will not be returned. Even if it has an attribute value of 1, just as A, B, D, and E has, it has an attribute value of 3, w/c the others does not have.

 

As I interpreted it, that is the predicate for the records that he wants displayed. And the query I'd given will, I hope, gives that.

 

Regarding your answers, they are correct, no doubt.

 

But they will need adjustment when attributes 23 and 45 are not anymore involved.

 

What I've given was a more general approach, which will work (I hope), even if the user does not specify in a WHERE clause some value for the field named 'attribute'.

 

(That is why, I've also stated, it is for the OP to determine if what fenway and mikosiko had given was sufficient.)

 

Hope it helps.

[fenway]

Performance-wise, the GROUP BY is ill-advised -- but as you mentioned, if the query needs back more data, that would be the easy workaround.  Of course, it would be preferable to run two queries in that case.

[mikosiko]

@ebmigue

@fenway, @mikosiko

The query you both gave works only for rows with attribute values 23 and 45.

 

 

seems that you didn't read neither my first post (Reply # nor my last.... didn't the comment in this line doesn't ring a bell for you?

HAVING gp = CONCAT_WS(',',23,45); // or replace here for the attributes that you want ordered asc....

 

 

[ebmigue]

@ebmigue

@fenway, @mikosiko

The query you both gave works only for rows with attribute values 23 and 45.

 

 

seems that you didn't read neither my first post (Reply # nor my last.... didn't the comment in this line doesn't ring a bell for you?

HAVING gp = CONCAT_WS(',',23,45); // or replace here for the attributes that you want ordered asc....

 

Yeah I read your post many times, that is why I think that I have to provide another answer for the OP to choose from.

 

There is nothing inherently wrong with your solution.

 

However, what if the OP needs to display ALL such products? Obviously having a HAVING clause is not anymore tenable.

[mikosiko]

 

However, what if the OP needs to display ALL such products? Obviously having a HAVING clause is not anymore tenable.

of course not.. as you said... that is obvious.... selects must be constructed or modified based on the final objectives (and the OP's objectives were very clear)... and that is obvious too.

 

what if the OP needs to display all the products?  well... in that case modify my original query is fairly simple (take no more than 30 seconds maximum).

 

In any case, whatever working query is available (yours, fenway's, mine or from someone else) what the OP or anybody should chose to use is the one that shows the best EXPLAIN plan or PROFILING with a good and representative load of records... any other discussion/consideration is meaningless imho.

[ebmigue]

In any case, whatever working query is available (yours, fenway's, mine or from someone else) what the OP or anybody should chose to use is the one that shows the best EXPLAIN plan or PROFILING with a good and representative load of records... any other discussion/consideration is meaningless imho.

 

Right. As if implementation/performance is always the consideration.

 

Have people considered, that if a query is slow, it is not the query or the programmer that is always at fault?

 

The OPTIMIZER, the DBMS, in short the company whose product you used, could also be at fault.

 

Strive for correctness first; performance should be secondary.

 

Any consideration on performance is meaningless if what is attempted to be achieved is not achieved in the first place.

 

If you have a  program that is super fast but gives you 1 + 1 = 3, any discussion on making it faster is meaningless, IMHO.

[fenway]

Have people considered, that if a query is slow, it is not the query or the programmer that is always at fault?

It's always the programmer -- they wrote the query -- as long as you're not cheating with optimizer hints.

[ebmigue]

Right. As if people who are creating the DBMS - the software used by most programmers - are infallible persons.

 

For 40 years, we only have SQL, while in theory we could have more useful database technology.

 

Is that the fault of the programmer? Of the regular user of SQL?

 

Nope. That is the fault of dbms vendors.

 

They could be at fault too.

 

Marketing of course would tell otherwise.

 

We forget that what we almost always "learn" are their hacks, since in the first place they failed to implement RM properly.