CODEWITHSUNDEEP
phpmysqlsqldatabase
iOSBee
iOSBee

Reputation: 229

Sql select multiple values from another query

I have three tables in php-mysql products,product_fact,fact schema is like this

products

 id , name (1000 records)

Eg-

id     |    name
-------+--------
1125   | key chain
1135   | bikes
1145   | cars

id = has different id numbers of the products every product has a unique id,

name = name of products

product_fact

product_id, fact_id

product_id = this is the same id that is in products table

fact_id = this is fact id every products have some facts, many of the products has more than one fact.

Eg-

product_id  |  fact_id
------------+----------
1125        |      5
1125        |      7
1125        |      6
1135        |      8
1145        |      9
1145        |      2

fact

id , name

id = this is fact_id same as in table product_fact

name = this is the name of fact.

Eg-

id      |    name
--------+---------
 2      |   black
 8      |   free
 5      |   sold
 6      |   coming soon
 9      |   new

Now i want to select particular fact name related to the product, but when i execute this query -->

SELECT name
FROM fact
Where id = (SELECT fact_id
FROM product_fact
Where product_id='1125');

It says Subquery returns more than 1 row

But when i run this query -->

SELECT name
FROM fact
Where id = (SELECT fact_id
FROM product_fact
Where product_id='1135');

It gives me correct output : free

What should i do now it should display fact name's for other products any help, what else should i have to include in my query.. any help

Upvotes: 5

Views: 3454

Answers (1)

John Woo
John Woo

Reputation: 263723

to be more safe with subquery, use IN instead of =

SELECT name 
FROM   fact 
Where id IN (SELECT fact_id 
             FROM   product_fact 
             WHERE  product_id='1125');

or using JOIN

SELECT  DISTINCT a.name
FROM    fact a
        INNER JOIN product_fact b
            ON a.ID = b.fact_ID
WHERE   b.product_ID = '1125'

Upvotes: 14

Related Questions