SQL Puzzler 2

[BlindPete]

Hello All,

I'm actually working in MS Access today but thought I would give this forum a go anyhow.

I have a table. I want to write a query that looks for values of field1 in field2. In VFP I'd use the INLIST, but MS Access doesn't have it. I figure one of you SQL guru's might have an alternate methodology.

Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)

 

[foxdev]

Access, huh? <shudder>

I'm probably not fully comprehending what you are trying to do, but wouldn't it be:

[tt]select * from MyTable where MyField2 like '%MyField1%'[/tt]


Robert Bradley

http://www.foxdev.com/

 

[BlindPete]

Hmmm no...

In VFP the Query would look something like this...

SELECT * FROM Table1 A WHERE !INLIST(A.field1,SELECT DISTINCT field2 FROM Table1)

or something like that... I don't have VFP in front of me to try it out but it would go something like that.

Essentially a list of all records in Table1 where Field1's value can not be found anywhere in Table1.field2

Yuck this is tricky just to explain.

Thks!


Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)

 

[foxdev]

You didn't mention the not part in your OP, but its &quot;not&quot; a big deal. Hows 'bout:

SELECT * FROM Table1 WHERE Field1 IS NOT IN (SELECT field2 FROM Table1)

Robert Bradley

http://www.foxdev.com/

 

[JDurbin]

Pete, does UnAccess support sub queries?
SELE field1 FROM table 1 ;
INTO CURSOR cResults ;
WHERE NOT EXISTS | IN ;
(SELE DIST field2 FROM table2)

John Durbin
john@johndurbin.com
MCP Visual FoxPro

ICQ VFP ActiveList #73897253

 

[BlindPete]

Sorry Robert,

Close but still no dice.
unAccess no like
SELECT * FROM Table1 WHERE Field1 IS NOT IN (SELECT field2 FROM Table1)

John, Yes unAccess allows sub queries but unAccess does not like your syntax either....

BUT SUCCESSS CAME WITH a melding of both your concepts

SELECT A.HID, A.ParentID
FROM Hierarchy AS A
WHERE (((A.ParentID)<>0) AND (((select HID FROM Hierarchy B WHERE B.HID = A.ParentID) Is Not Null)=0))

Thanks Guys! This Forum is the best! A star for both of you!



Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)

 

[JDurbin]

Pete that looks more like Rocket Science than SQL. Man am I glad I dont work with Access

John Durbin
john@johndurbin.com
MCP Visual FoxPro

ICQ VFP ActiveList #73897253

 

[JimmyK]

Pete,
Why dont you use MS Acess query wizards, and then look at the SQL string it just generated

Jimmy K

 

[BlindPete]

Hello JimmyK!

Actually I tried that too. The wizard lacked sufficient flexability to even get close. After about 15 minutes of puzzling over what in mind sounded simple. It kind of took on a life of its own. I had it working as two seperate queries right away but I wanted it all in one. It was just one of the challenges my ego couldn't leave behind.

Thanks for the tip!

Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)

 

[JimmyK]

Hi Pete,
Try this one. It looks for field1 in Table1 that exists in field2 of Table2

SELECT DISTINCTROW Table1.field1
FROM Table1 LEFT JOIN Table2 ON Table1.field1 = table2.field2
WHERE (((table2.field2) Is Not Null));

Hope it helps

Jimmy K

 

[jonscott8]

Hi Pete,

SELECT MyTable.*, MyTable.ID
FROM MyTable
WHERE (MyTable.ID In (58,59))

or

SELECT MyTable.*, MyTable.ID
FROM MyTable
WHERE (MyTable.ID In (SELECT [ID] FROM [MyTable2]))

Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,

When All We Want Is Unity. - Creed