SQL Help needed

1185 pts.
Tags:
AS/400
Deduplication
SQL
In what way, using SQL could a statement be written in that it would not allow any duplications of a particular text in a column? How would it be set up? Also if you have three different tables, lets call them A, B, and C., and there may be multiple matches in each of the tables. I need to accomplish (first) in which table may there be matches and secondly in the three tables where might there be wholes or matches too. For instance, B and C may have the same values in them and A wouldn't? A and B may have matches that are not in C. How could a SQL statement be derived that would show those that are common to all and also those that are common to only each groups of two? Is this possible?

How to do away with duplications in my query?

How to find wholes and matches in three tables?

How to find the same in-groups of two table?

I need to create new numbers and I have to be sure that there are no existing numbers that have been generated in the past.and to do this i need to look at three different files. So I need to be able to develop a system that would look at each file compare the three to a forth (which is used to create the new numbers)

Looking for help...Thanks



Software/Hardware used:
Access, AS400, MS2000, or MSXP

Answer Wiki

Thanks. We'll let you know when a new response is added.

Way to much in one question

Duplications

Select distinct colTxt from myfile

This requires that the entire returned row (all the columns selected) are unique

Phil

——————-
SELECT distinct [5000ChyPhantoms].Application, [5000ChyPhantoms].OE1, [5000ChyPhantoms].PN, [IN0140 79 wOE].PN, [IN0140 79 wOE].ComprsOE
FROM 5000ChyPhantoms LEFT JOIN [IN0140 79 wOE] ON [5000ChyPhantoms].PN = [IN0140 79 wOE].PN;

This would eliminate duplicate rows where all 5 fields matched the 5 fields of another row.

Phil
———————

Thanks
We appreciate the feedback.
A lot of this comes with time.

Phil
———————–
Here’s a discussion of learning resources including some SQL resources

http://www.rpgiv.com/rpgivforum/view?SID=20090903105344465443&NRL=11

Phil

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CompEng
    so if this is the statement SELECT [5000ChyPhantoms].Application, [5000ChyPhantoms].OE1, [5000ChyPhantoms].PN, [IN0140 79 wOE].PN, [IN0140 79 wOE].ComprsOE FROM 5000ChyPhantoms LEFT JOIN [IN0140 79 wOE] ON [5000ChyPhantoms].PN = [IN0140 79 wOE].PN; What you are saying is to add in the (Select distinct colTxt from myfile) where? On the Select line after [IN0140 79 wOE].ComprsOE,distinct colTxt from myfile Is this right or not?
    1,185 pointsBadges:
    report
  • BigKat
    Unless you need to know that it is in all three tables vs any of the two table pairings, simply testing the two table pairings will get you all of the matches.
    select fielda from filea inner join fileb on fielda = fieldb
                            exception join filec on fielda = fieldc
    union
    select fielda from filea inner join filec on fielda = fieldc
                            exception join filec on fielda = fieldb
    union
    select fieldb from fileb inner join filec on fieldb = fieldc
                            exception join filec on fieldb = fielda
    
    hope this helps
    7,935 pointsBadges:
    report
  • CompEng
    I want to say thanks very much - Phil and BigKat! How do you guys acquire all this information and know all this stuff? What is the fastest and must comprehensive way to get to the place you are at? What a great job and a wealth of information you have! Thanks again!
    1,185 pointsBadges:
    report
  • CompEng
    I am trying to find something out there that is the top of the line when it comes to a book, an online learning and an institution that would add to my quality and speed to acquire the amount of knowledge. I don't what to mislead you. I find this site to be the best in terms of learn and having experts like you to bounce me struggles off of and again I want to say thanks a lot. Yet at the same time it would be good to have the resource of each of the three mentioned above to be able to learn faster and be able to be a giver rather then a taker to this knowledge site. So any input would be greatly appreaciated.
    1,185 pointsBadges:
    report
  • BigKat
    You are most welcome CompEng, and as Phil said it takes time, and in my case, my company has a baby box that we can play on and try things. All of the developers have a developer signon and a SECOFR signon. We can go in and try out new code, new configurations, etc. If we would crash it (and the system i is so tough, we haven't (yet)) no big deal. Just restore and try again. As far as SQL, you can get a MySQL database to run on a PC, and you could practice on there without risking your systems. Have fun and Happy Learning! BigKat
    7,935 pointsBadges:
    report
  • BigKat
    I just noticed a couple of small typos in my code earlier
    select fielda from filea inner join fileb on fielda = fieldb
                            exception join filec on fielda = fieldc
    union
    select fielda from filea inner join filec on fielda = fieldc
                            exception join fileb on fielda = fieldb
    union
    select fieldb from fileb inner join filec on fieldb = fieldc
                            exception join filea on fieldb = fielda
    
    :)
    7,935 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following