CODEWITHSUNDEEP
mysql
Alesfatalis
Alesfatalis

Reputation: 777

problems with my mysql query

Hey guys i have this query:

SELECT Schwellwert
FROM lm_Schwellwert
WHERE (Typ = (SELECT Typ FROM lm_Artikel WHERE `Index` = (SELECT DISTINCT `Index` FROM lm_Warenkorb)))

But when I run it I'm getting this error:

1242 - Subquery returns more than 1 row

I don't know what is in my query wrong.

Example how lm_Schwellwert looks:

Typ     Schwellwert
Computer    78

Example how lm_Artikel looks:

Index   Artikelbezeichnung  Status  Bestand     Lieferant    Typ
1           HP Elitebook        OK      19         HP         Computer

Example how lm_Warenkorb looks:

Index
11
1

Upvotes: 0

Views: 60

Answers (5)

Robert
Robert

Reputation: 25753

Maybe in will help you:

SELECT Schwellwert
FROM lm_Schwellwert
WHERE (Typ in (SELECT Typ FROM lm_Artikel WHERE `Index` in (SELECT DISTINCT `Index` FROM lm_Warenkorb)))

you can also use aggregate function min for example:

SELECT Schwellwert
FROM lm_Schwellwert
WHERE (Typ in (SELECT min(Typ) FROM lm_Artikel WHERE `Index` in (SELECT min(`Index`) FROM lm_Warenkorb)))

Upvotes: 1

DonCallisto
DonCallisto

Reputation: 29912

Where condition could run only aginst one vale (so x=y and so on)
In your case, your subquery

(SELECT Typ FROM lm_Artikel WHEREIndex= (SELECT DISTINCTIndexFROM lm_Warenkorb))

is returning more than one row and this cause an error.

Try to modify your query as follow

SELECT Schwellwert
FROM lm_Schwellwert
WHERE (Typ IN (SELECT Typ FROM lm_Artikel WHERE `Index` IN (SELECT DISTINCT `Index` FROM lm_Warenkorb)))

The IN operator will help you, since accept a set of values and return true if Typ is equal to one of those

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query 

Select 
    lm_Schwellwert.Schwellwert
From lm_Schwellwert
Left join lm_Schwellwert as r 
    on r.Typ = (SELECT Typ FROM lm_Artikel WHERE `Index` = (SELECT DISTINCT `Index` FROM lm_Warenkorb))
Where r.id is not null  
group by lm_Schwellwert.Typ

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

SELECT DISTINCT `Index` FROM lm_Warenkorb

This sub query returns two rows.. it should have only one as you are using an equal to sign..

try this:

SELECT Schwellwert
FROM lm_Schwellwert
WHERE (Typ = (SELECT  Typ FROM lm_Artikel WHERE `Index` in (SELECT DISTINCT `Index` FROM lm_Warenkorb)) limit 1)

Upvotes: 1

SRIRAM
SRIRAM

Reputation: 1888

try this

SELECT Schwellwert
FROM lm_Schwellwert
WHERE (Typ = (SELECT Typ FROM lm_Artikel 
              WHERE `Index` in (SELECT DISTINCT `Index` FROM lm_Warenkorb)))

because u have two values in index

Upvotes: 1

Related Questions