🔒 Locked

simple SQL query

Started by cignox1 Sep 14, 2005 at 10:27 AM 3 replies 800+ views

Original Post

223

Sep 14, 2005 at 10:27 AM

Well, I never used SQL deeply, but I never thought that one day I would have a problem like this. I have a database with many tables. Two of them are as follows: #1: Session_ID Item Time ... where Session_ID is a number and Item is a string (I don't need Time). #2 FromFile Link where FromFile is a number and Link a string. #1 rows are a list of used links, and #2 rows are the complete list of available links and the files they belongs. I need to know the FromFile property for each row in #1, so I did the following queries: select FromFile,Link from #2,#1 where #2.Link = #1.Item select FromFile,Link from #2 where Link = any (select Item from #1 where 1) I tried even more queries but the results are always wrong (I think) because I espect to get 1532 rows in the result (same than #1) but I get always repetitions (T get > 20000 rows), and I don't know why.

Halo7

Sep 14, 2005 at 11:09 AM

How about this:

SELECT #2.link, #2.FromFile FROM #2 INNER JOIN #1 ON #2.link = #1.item

-Halo7

cignox1

Author

223

cignox1

Sep 14, 2005 at 1:02 PM

Thank you for replying, but even with your query I get 20252 rows (always the same number, I think) where I espect exactly 1537 entries (one for each entry in #1 table).
Is this strange or am I missing something?

If this can help, I'm using MySQL with SQLyog as the client.
If I add the keyword distinct, I get 9704 rows... But in those 1537 I espect there could be easily duplicates.

Halo7

Sep 14, 2005 at 2:43 PM

If you are getting back more records than exist in table #1, then you don't have a one-to-one relationship between the two tables, which seems to be what you're expecting. Either the #1.Item column or the #2.Link column has duplicate data.

-Halo7

cignox1

Author

223

cignox1

Sep 14, 2005 at 4:31 PM

I used the following queries:
select * from #1 where 1
select distinct * from #1 where 1

both return the same results, so I think there are no duplicates.
In the #2 table I'm sure there are no duplicates, because it is a list of available links.
For such a simple task it is becoming to much complex :-(

EDIT: Ok, solved. The guy that gave me the db didn't explain it correctly. #2 contains duplicated links, in different files. I didn'checked until now, so sorry for this. Thank you anyway!

[Edited by - cignox1 on September 15, 2005 2:31:20 AM]

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.