Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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?)
0
sciber_dude
Asked:
sciber_dude
  • 6
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
MourdekaiCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now