Topic: MYSQL penetrance from more subqueries

Hi anybody,

Please can you help where is the problem.

MYSQL

Select NameList_name from NameList where NameList.id In 
(Select NameList.id from NameList where id_company =
(Select company.id From company where company_company Like "XY"))
And
(Select id_NameList from NameListGoods where id_NameListGoodsKind =
(Select NameListGoodsKind.id from NameListGoodsKind where NameListGoodsKind_NameListGoodsKind LIKE "XX"))
And
(Select id_NameList from NameListStateFrom where id_StateFrom =
(Select StateFrom.id from StateFrom where StateFrom_state Like "XXX"))
And
(Select id_NameList from NameListStateTo where id_StateTo =
( Select StateTo.id from StateTo where StateTo_state Like "XYX"));   

This returned me ""SQL Server Subquery returned more than 1 value""

I want to search in:
Select NameList_name from NameList where NameList.id
The right NameList.id penetrance
what is returned from others subquery.

Example:
Table Namelist:
ID   Name
1    a
2    b
3    c
4    d
5    e

subquery1 :
(Select NameList.id from NameList where id_company =
(Select company.id From company where company_company Like "XY"))
Return me : numbers : 1,4,5

subquery2 :
(Select id_NameList from NameListGoods where id_NameListGoodsKind =
(Select NameListGoodsKind.id from NameListGoodsKind where NameListGoodsKind_NameListGoodsKind LIKE "XX"))
Return me : numbers 2,4,3

subquery3 :
(Select id_NameList from NameListStateFrom where id_StateFrom =
(Select StateFrom.id from StateFrom where StateFrom_state Like "XXX"))
Return me : numbers 3,4,1

subquery4 :
(Select id_NameList from NameListStateTo where id_StateTo =
( Select StateTo.id from StateTo where StateTo_state Like "XYX"));
Return me : numbers 5,4,2,1

Head Query
Penetrance from this results is number 4.Then i need to show me the Name from Namelist where Namelist.id=4;

Thx for help,

Re: MYSQL penetrance from more subqueries

Unfortunately in this matter I am weak )


I think you should ask help on the MySQL forum
http://www.codingforums.com/mysql/
http://stackoverflow.com/questions/tagged/mysql

Dmitry.

Re: MYSQL penetrance from more subqueries

Hello Krokodil,


I'd love to help you. Can you provide the full database attached, and a example of which data should give which data ? This looks to me like nested sub-queries and it's not that complicated, but I need the "raw material" to work on it smile


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: MYSQL penetrance from more subqueries

I already make it :

this is the solution:

SELECT a.id
FROM (SUBQUERY 1) a
JOIN (SUBQUERY 2) b
ON b.id = a.id
JOIN (SUBQUERY 3) c
ON c.id = a.id
JOIN (SUBQUERY 4) d
ON d.id = a.id;

Brgds,

Krokodil