Difficult one: SQL statement.

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?)
LVL 11
sciber_dudeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MourdekaiConnect With a Mentor Commented:
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
 
lmklamCommented:
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
 
sciber_dudeAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
walterecookCommented:
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
 
ragoranCommented:
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
 
ragoranCommented:
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
 
sciber_dudeAuthor Commented:
lemme try all this out and get back to you

:) SD
0
 
sciber_dudeAuthor Commented:
@ ragoran -

Syntax error (missing operator) in query expression

Do I need parenthesis? If so where?

:) SD
0
 
sciber_dudeAuthor Commented:
I have recreated the database and uploaded it here.

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

:) SD
0
 
sciber_dudeAuthor Commented:
@ 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
 
sciber_dudeAuthor Commented:
Whoa! Mourdekai! You are the dude! or is it Dudette?

Anyway, thanks a lot. That works perfectly.

:) SD

0
 
MourdekaiCommented:
Definitely a dude!

Thanks for the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.