SELECT statement help needed

25 Nov 2005 8:09 AM

Petar Popara

I have this case

Table1 (ID, DoSomething, ...)
Table2 (ID, ID_Table1, ...)

I need to select all records from table 2 where ID=Table1.ID (JOIN) and
Table2.DoSomething = 1:

SELECT a.ID FROM Table2 a, Table1 b WHERE a.ID_Table1 = b.ID AND
b.DoSomething = 1

The problem is that Table2.ID_Table1 column is optional. It doesn't have to
exists in Table1. It is not FK on Table1. What should I do? :(

25 Nov 2005 8:14 AM

Uri Dimant

>I need to select all records from table 2 where >ID=Table1.ID (JOIN) and
>Table2.DoSomething = 1:

SELECT <column lists? FROM Table2 INNER JOIN
Table1 ON Table1.ID =Table2.ID
WHERE Table2.DoSomething = 1:

Show quote

"Petar Popara" <my.f***@mail.net> wrote in message
news:OONILeZ8FHA.3484@TK2MSFTNGP14.phx.gbl...
>I have this case
>
> Table1 (ID, DoSomething, ...)
> Table2 (ID, ID_Table1, ...)
>
> I need to select all records from table 2 where ID=Table1.ID (JOIN) and
> Table2.DoSomething = 1:
>
> SELECT a.ID FROM Table2 a, Table1 b WHERE a.ID_Table1 = b.ID AND
> b.DoSomething = 1
>
> The problem is that Table2.ID_Table1 column is optional. It doesn't have
> to exists in Table1. It is not FK on Table1. What should I do? :(
>

25 Nov 2005 8:17 AM

Jens

Hi,

try this one:

SELECT t2.*
FROM Table2
LEFT JOIN Table1 T1
ON T2.ID_Table1 = T1.ID
WHERE Table2.DoSomething = 1

HTH, jens Suessmeyer.

25 Nov 2005 8:38 AM

Petar Popara

Guys, I think I made a mistake in my question. Sorry.

> try this one:
>
> SELECT t2.*
> FROM Table2
> LEFT JOIN Table1 T1
> ON T2.ID_Table1 = T1.ID
> WHERE Table2.DoSomething = 1

Yes, it should be something like that, *but* it should return records (t2.*)

--> *if* T2.ID_Table1 *doesn't* exists in Table1 <--

OR

--> *if* T2.ID_Table1 exists in Table1 AND and Table2.DoSomething = 1 <--

Is it possible?

Thank you!

25 Nov 2005 8:58 AM

Jens

HI,

ok think I got it:

SELECT t2.*
FROM Table2
WHERE
T2.ID_Table1 NOT IN
(
    SELECT ID From Table1
)
OR
(
    T2.ID_Table1 IN
    (
        SELECT ID From Table1
    )
    AND Table2.DoSomething = 1
)

HTH, jens Suessmeyer.

25 Nov 2005 9:40 AM

Petar Popara

Thank you very much for your help.

Show quote

> SELECT t2.*
> FROM Table2
> WHERE
> T2.ID_Table1 NOT IN
> (
> SELECT ID From Table1
> )
> OR
> (
> T2.ID_Table1 IN
> (
> SELECT ID From Table1
> )
> AND Table2.DoSomething = 1
> )

Yes, that's it. Just one more question: Whar would happen if DoSomething
column would be in Table1 and not in Table2? Please help.

25 Nov 2005 10:01 AM

Jens

Hi Petar,

no differnence because we take the

Table2 prefix --> Table2.DoSomething for that, so that column is
identified clearly.

HTH, jens Suessmeyer.

25 Nov 2005 10:09 AM

Petar Popara

> no differnence because we take the
>
> Table2 prefix --> Table2.DoSomething for that, so that column is
> identified clearly.

In that case this:

SELECT t2.* FROM Table2
WHERE T2.ID_Table1
NOT IN (SELECT ID From Table1)
OR (T2.ID_Table1 IN (SELECT ID From Table1) AND Table1.DoSomething = 1)

should work? But, Table1 is not inside select (select t2.* from table2...)?
How can that work?

25 Nov 2005 10:22 AM

Jens

Ok, now I (assume) that I know what you mean:

SELECT t2.* FROM Table2 T2
WHERE T2.ID_Table1
NOT IN (SELECT ID From Table1)
OR EXISTS
(
        SELECT * FROM Table1
        WHERE Table1.ID = T2.ID_Table1
        AND Table1.DoSomething = 1
)

HTH, Jens Suessmeyer.

25 Nov 2005 10:46 AM

Petar Popara

Yes, that's exactly what I need. :)

Thank you very very much.