Solved

Difficult one: SQL statement.

Posted on 2004-08-04
12
335 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 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