SQL Help needed
Home > IT Answers > Development > SQL Help needed
CompEng
600 pts.
0
Q:
SQL Help needed
SQL, AS/400, Deduplication
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
ASKED: Sep 2 2009  1:16 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Philpl1jb
24510 pts.
0
A:
 RATE THIS ANSWER
+3
Click to Vote:
  •   3
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Sep 4 2009  1:55 PM GMT by Philpl1jb   24510 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

CompEng   600 pts.  |   Sep 2 2009  3:41PM GMT

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?

 

BigKat   2540 pts.  |   Sep 3 2009  2:59PM GMT

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

 

CompEng   600 pts.  |   Sep 3 2009  4:44PM GMT

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!

 

CompEng   600 pts.  |   Sep 4 2009  10:25AM GMT

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.

 

BigKat   2540 pts.  |   Sep 8 2009  12:03PM GMT

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

 

BigKat   2540 pts.  |   Sep 8 2009  12:08PM GMT

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

:)

 
0