1

What methods are recommended for Selecting multiple columns within a nested subquery? It's been a while since I've coded any queries and I'm having some difficulty wrapping my head around this. The specific challenge is on Line 2 of the code below. The IN operand doesn't quite work here (see error message below), and I'm not sure if it's simply a matter of the syntax I'm using, and/or there is a much better way to go about this (i.e. using the HAVING operand or a JOIN statement)

SELECT * FROM Rules WHERE Rules.LNRule_id 
    IN(SELECT LNRule_id1,LNRule_id2,LNRule_id3,LNRule_id4 FROM Silhouette
    WHERE Silhouette.Silhouette_Skirt=(SELECT Silhouette_Skirt FROM Style
        WHERE Style.Style_Skirt='$Style_Skirt')
    )

The purpose of this query is to SELECT all the relevant rows in table Rules for a particular value in table Style (i.e. $Style_Skirt), which it does by matching it to one of several factors - in this case the garment's Silhouette. What I am therefore trying to do in this portion of the query is SELECT all rows in table Rules who's ID (LNRule_id) matches values in any of the specified columns in table Silhouette

(SELECT LNRule_id1,LNRule_id2,LNRule_id3,LNRule_id4 FROM Silhouette WHERE Silhouette.Silhouette_Skirt=(...))

Edit There is a many-to-many relationship (each Silhouette has several applicable Rules, and each Rule can apply to several Silhouettes). All the rules reside in the table 'Rules' (one per row), and each rule has an id ('LNRule_id'). The table 'Silhouette' has columns which tell it which rows need to be called from 'Rules' by 'LNRule_id' (LNRule_id1,2,3,4 indicate which Rules should be called, and store the values of the id's for the relevant rows in table 'Rules')

The error message currently being generated by the IN Operand is:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

Improve this question

2 Answers 2

Reset to default
1

I think you want this query

SELECT * FROM  Rules r
JOIN Silhouette s
(ON r.LNRule_id=s.LNRule_id1
OR r.LNRule_id=s.LNRule_id2
OR r.LNRule_id=s.LNRule_id3
OR r.LNRule_id=s.LNRule_id4)
JOIN Style st
ON s.Silhouette_Skirt=st.Silhouette_Skirt
WHERE st.Silhouette_Skirt = '$Style_Skirt'
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That looks like it should be a much better way to go :-) I'm assuming that it'll increase the performance speed, but can you clarify for me the benefit of using the 2nd JOIN statement?
@ChayaCooper Well mysql is bad with subqueries,but its the same logic as your attempt WHERE Silhouette.Silhouette_Skirt=(SELECT Silhouette_Skirt FROM Style
It's took me a bit of playing around with the syntax, but It's working now if I remove the () in the 1st JOIN statement and used the WHERE statement instead of the 2nd JOIN statement. I'm wondering though if you have any other suggestions that would allow me to use either or both of those?
1

mysql is complaining that on one side of IN you have a single column, and on the other side you have a multi-column rowset. In order for the IN operator to work, the rowset on the right side of IN must have the exact same number of columns as the left side; in this case, one column.

What you are trying to accomplish could perhaps be achieved if you did something like WHERE LNRule_id IN( SELECT LNRule_Id1 ...) OR LNRule_id IN( SELECT LNRule_Id2 ...) OR ... OR ... but the resulting query would be a monstrosity, and its performance would be horrendous. There may be other ways to go about it too, but anything you try will probably be similarly atrocious.

I do not have enough information to be absolutely sure about what I am saying, but it seems to me that the reason why you have this problem is that your database schema is not normalized. Generally, whenever you see a table with a group of columns having names that all begin with the same prefix and end with a number, it means that someone, somewhere, did not normalize their data.

To address the edit in your question, what you have implemented might conceptually be a many to many relationship, but as far as relational databases are concerned, (you know, the science, the theory, the established practices, the approaches necessary to get things to actually work,) it is definitely not a many to many relationship. Many to many relationships are most certainly not implemented with column1, column2, column3, ... columnN. To be sure that I am not making this stuff up, you can read what others say about many to many relationships here:

Many-to-many relations in RDBMS databases

So, my suggestion, if I correctly understand what is going on, would be to introduce a new table, called SilhouetteRules, which contains two columns, silhouette_id and rule_id. This table will implement a many-to-many relationship between silhouettes and your rules. Then of course you get rid of all the rule1, rule2, rule3, etc. columns from Silhouette.

Once you have done that, you can obtain all silhouettes and all rules associated with them using a query like this:

SELECT * FROM Silhouette 
    LEFT JOIN SilhouetteRules ON 
        Silhouette.id = SilhouetteRules.silhouette_id 
    LEFT JOIN Rules ON 
        SilhouetteRules.rule_id = Rules.id

The above query will yield multiple rows for each silhouette, where the silhouette fields will be identical from row to row, and only the rule fields will differ. Do not be surprised by this, that's how relational databases work.

Given a given_silhouette_id, you can retrieve all rules associated with it using a query like this:

SELECT * FROM Rules
    LEFT JOIN SilhouetteRules ON 
         Rules.id = SilhouetteRules.rule_id 
WHERE 
    SilhouetteRules.silhouette_id = given_silhouette_id

So, you are going to be using this query as a subquery in queries like the one in the question.

Now, regarding the query in the question, I am unable to tell you exactly how you would need to modify it to get it to work with the normalization that I proposed, because I cannot make sense of it. You see, even if you fix the problem that you currently have with SELECT * FROM table WHERE single-column IN multi-column-rowset, there is another problem further down: the WHERE Silhouette.Silhouette_Skirt=(SELECT ... part would not work either, because you cannot compare the value of a column against the result of a select statement. So, I do not know what you are trying to do there. Hopefully, once you normalize your schema and fix the first problem with your query, then the solution to the second problem will become obvious, or you can ask another question on stackoverflow.

P.S. did Mihai's answer work?

Improve this answer

9 Comments

That's exactly what I'm doing :-) Each rule has it's own row in table 'Rules', and I'm trying to select all the rows with rules that relate to this Silhouette => Style.
No, that's not exactly what you are doing. You have a table called rules which contains a number of additional sub-entities which you also (unfortunately) call rules. Essentially, you have SomeKindOfRule and SomeOtherKindOfRule with a one-to-many relationship between them. You have folded them all into one table, hoping to keep things simple this way, but this is not normalized, so it is causing you problems.
(Or at least that's what it seems like you are doing, to an external observer like me, who sees columns named rule1, rule2, rule3 etc.)
I'm actually trying to normalize this, so it would be great to know if there's something I'm missing ;-) Just to clarify: There is a many-to-many relationship (each Silhouette has several applicable Rules, and each Rule can apply to several Silhouettes). All the rules reside in the table 'Rules' (one per row), and each rule has an id ('LNRule_id'). The table 'Silhouette' has columns which tell it which rows need to be called from 'Rules' by 'LNRule_id' (the id indicating the Rule to be called is what's stored in LNRule_id1,2,3,4)
Okay, let me try to understand: does a Silhouette always have to have 4 rules? Are there no Silhouettes with only 3 rules? Can the need never arise for a Silhouette to have 5 rules?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.