Solved

Difficult one: SQL statement.

Posted on 2004-08-04
12
336 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 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