Solved

Difficult one: SQL statement.

Posted on 2004-08-04
12
337 Views
Last Modified: 2008-02-01
Table ALLDATA:
* oldID               [Number]
* URL                 [Text]
* IsDuplicateURL  [Yes/No]

Only non-duplicate records were picked up and looked for relevancy. This information was stored in a different table.

Table RELEVANTDATA:
* newID                           [Number]
* oldID                             [Number]
* newURL                         [Text]
* WhoCheckedRelevance   [Text]
* WhenCheckedRelevance [Data/Time]

The table I want to create is an intersection table with newID and OldID including those oldIDs of IsDuplicateURL being True.

* newID                           [Number]
* oldID                             [Number]

How do I structure the SQL statement? (does this make sense?)
0
Comment
Question by:sciber_dude
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 3

Expert Comment

by:lmklam
ID: 11720529
like this?

SELECT newID, oldID
FROM ALLDATA LEFT JOIN RELEVANTDATA ON ALLDATA.oldID = RELEVANTDATA.oldID
WHERE IsDuplicateURL = true OR not isnull(newID);

If they are the information that you want, but change it into an make table action query and run it.

lmklam
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11720642
well, its slightly more complicated than that. let me give u an example.

Table: ALLDATA
-----------------------------------------------------------------------------------------------------
OldID   |     IsDuplicateURL    |    URL
-----------------------------------------------------------------------------------------------------
1         |          False             | www.google.com
2         |          False             | www.hotmail.com
3         |          False             | www.yahoo.com
4         |          False             | www.EE.com
5         |          True              | www.yahoo.com
6         |          True              | www.hotmail.com
7         |          True              | www.google.com
-----------------------------------------------------------------------------------------------------

Now let us assume that Only Google, hotmail and EE are relevant pages.
Table: RELEVANTDATA
-----------------------------------------------------------------------------------------------------
NewID   |     OldID   |     newURL
-----------------------------------------------------------------------------------------------------
1          |      1         |     www.google.com/index.htm
2          |      2         |     www.hotmail.com/index.htm
3          |      4         |     www.EE.com/index.htm
-----------------------------------------------------------------------------------------------------


This is what I want to create as a table:

-----------------------------------------------------------------------------------------------------
NewID   |     OldID   |     URL
-----------------------------------------------------------------------------------------------------
1          |      1         |     www.google.com
2          |      2         |     www.hotmail.com
3          |      4         |     www.EE.com
1          |      7         |     www.google.com
2          |      6         |     www.hotmail.com
-----------------------------------------------------------------------------------------------------

Makes sense?

I am really stuck writing SQL and i really appreciate any input.
:) SD
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11720779
Not 100% sure I understand.  Joining on the URL would yield results like that:

SELECT RelevantData.NewID, RelevantData.OLDID, AllData.URL
FROM RelevantData INNER JOIN AllData ON RelevantData.URL = AllData.URL;

Of course you need to be careful joining on text.

Walt
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 14

Expert Comment

by:ragoran
ID: 11720811
Slight variation on Walt suggestion because the URL in RelevantData is not the same as in AllData, so you need three joins (you need table alias because you are using the same table twice in the query)

select RD.NewID, AD.OldID, AD.URL
from RelevantData RD inner join on
       AllData T on RD.oldID = T.OldID inner join on
       AllData AD on AD.URL = T.URL
0
 
LVL 14

Expert Comment

by:ragoran
ID: 11720815
Oups, too many "on"

select RD.NewID, AD.OldID, AD.URL
from RelevantData RD inner join
       AllData T on RD.oldID = T.OldID inner join
       AllData AD on AD.URL = T.URL

0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11720941
lemme try all this out and get back to you

:) SD
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11720993
@ ragoran -

Syntax error (missing operator) in query expression

Do I need parenthesis? If so where?

:) SD
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11721076
I have recreated the database and uploaded it here.

http://www33.brinkster.com/sbpress/Q_21082573.zip 

:) SD
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11721295
@ walterecook -

> SELECT RelevantData.NewID, RelevantData.OLDID, AllData.URL
> FROM RelevantData INNER JOIN AllData ON RelevantData.URL = AllData.URL

This approach wont work as the RelevantData.URL and AllData.URL are not similar. we need to use sub-queries and aliases here.

The problem is ... I am not able to solve it :|

I have uploaded a database (link in my earlier post) that would help solve this. Please bear with me through this.

Thank you
SD
0
 
LVL 8

Accepted Solution

by:
Mourdekai earned 500 total points
ID: 11721481
I downloaded your db.  Here are two queries for you:

[qryAlias]
SELECT ALLDATA.oldID, ALLDATA_1.oldID, ALLDATA_1.URL
FROM ALLDATA INNER JOIN ALLDATA AS ALLDATA_1 ON ALLDATA.URL = ALLDATA_1.URL;

and this should give you what you want:
[qryResults]
SELECT RelevantData.NewID, qryAlias.ALLDATA_1.oldID, qryAlias.URL
FROM RelevantData INNER JOIN qryAlias ON RelevantData.OldID = qryAlias.ALLDATA.oldID;
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 11722425
Whoa! Mourdekai! You are the dude! or is it Dudette?

Anyway, thanks a lot. That works perfectly.

:) SD

0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11722545
Definitely a dude!

Thanks for the points!
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question