php - select statement that skips certain rows

158

I need to select data from two tables. However there are two columns, where if one or both rows read NO, I want to skip it.

Table 1

a--b--c-
1  r  l
2  t  f
3  d  c

Table 2

d--e--f--g-
1  r NO NO
2  r YES NO
3  r YES YES

QUERY:

 SELECT
    talbe1.a,
    table1.b,
    table1.c,
    table2.d,
    table2.e,
    table2.f,
    table2.g,
    FROM table1 INNER JOIN 
    table2 on table1.b = table2.e
    WHERE 'no' NOT IN (SELECT table2.f, table2.g FROM table2)

488

Answer

Solution:

Should be as simple as this:

  SELECT
    talbe1.a,
    table1.b,
    table1.c,
    table2.d,
    table2.e,
    table2.f,
    table2.g,
  FROM table1 INNER JOIN 
        table2 on table1.b = table2.e
  WHERE table2.f <> 'NO' AND table2.g <> 'NO'

649

Answer

Solution:

Try this:

SELECT
  table1.a,
  table1.b,
  table1.c,
  table2.d,
  table2.e,
  table2.f,
  table2.g,
FROM table1 
LEFT JOIN table2 ON table1.b = table2.e
WHERE table2.f <> 'NO' AND table2.g <> 'NO'

Also not sure about the structure of your tables, but is there a reason you're joining on table1.b = table2.e?

729

Answer

Solution:

SELECT
    talbe1.a,
    table1.b,
    table1.c,
    table2.d,
    table2.e,
    table2.f,
    table2.g,
    FROM table1 INNER JOIN 
    table2 on table1.b = table2.e
        //if any of the f or g are not eqaul to "NO" only then select the result
        WHERE (table2.f != 'NO' AND table2.g != 'NO')

does not return if 
f = no and g = no
f = no and g = yes
f = yes and g = no

does return if
f = yes and g = yes

866

Answer

Solution:

Why not say that f=g and g='YES'?

872

Answer

Solution:

How about:

WHERE (table2.f <> 'no' AND table2.g <> 'no')

318

Answer

Solution:

You have to UseLEFT JOIN notINNER JOIN

SELECT
    talbe1.a,
    table1.b,
    table1.c,
    table2.d,
    table2.e,
    table2.f,
    table2.g,
  FROM table1 LEFT JOIN 
        table2 on table1.b = table2.e
WHERE table2.f <> 'NO' AND table2.g <> 'NO'

People are also looking for solutions to the problem: php - How to resolve "Invalid signature. Expected signature base string" in OAuth 1.0

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question