CODEWITHSUNDEEP
phpmysqljoin
rtuner
rtuner

Reputation: 2410

Retrieving data from two tables at once with one table referencing the other

I have two table as follows:

TABLE 1:

ID Code Detail
1  45   Yes
2  46   No

AND TABLE 2:

Code Detail1 Detail2
45   No    23
22   Yes   34

Is it possible to select all FROM TABLE 2 where detail = YES AND where TABLE 1 contains Code and says Yes?

i.e. query result should be:

Code Detail Detail
45   No     23
22   Yes    34

Thanks.

Upvotes: 2

Views: 109

Answers (2)

crush
crush

Reputation: 17013

SQL Fiddle: http://sqlfiddle.com/#!2/6f583/9

SELECT
    Table2.Code,
    Table2.Detail1,
    Table2.Detail2
FROM
    Table1,
    Table2
WHERE
    Table1.Detail = "Yes" OR
    (Table2.Detail1 = "Yes" AND
    Table1.Code = Table2.Code)

Although, my above query seems to give you the correct resultset for the example, I think that the following query better satisfies your criteria:

SQL Fiddle: http://sqlfiddle.com/#!2/6f583/12

SELECT
    Table2.Code,
    Table2.Detail1,
    Table2.Detail2
FROM
    Table1,
    Table2
WHERE
    (Table1.Code = Table2.Code AND
    Table1.Detail = "Yes") OR
    Table2.Detail1 = "Yes"
GROUP BY
    Table1.ID

Upvotes: 3

ejrowley
ejrowley

Reputation: 503

I don't follow your question very well but this will create the table of results that you want.

select table2.* from table2 left join table1 on table2.code = table1.code where table2.Detail1 = 'Yes' or table1.Detail = 'Yes'

Upvotes: 1

Related Questions