Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wolf_22
Starting Member
2 Posts |
 Posted - 2011-03-11 : 11:13:44
|
Hello everyone! I hope you're all doing well. This will be my first post, so please bear with me...I have 2 tables. In these, various pieces of information reside but the sole focus of TABLE 1 is basic user data (i.e. - ID, FIRST_NAME, LAST_NAME, SEX, etc.) whereas the sole focus of TABLE 2 is address information (i.e. - STREET_ADDRESS, ZIP, STATE, etc.)Fairly simple structure.What I need to do is retrieve the most current record containing 1 person's information using this person's special ID. So in my mind, I would believe the following would work: SELECT table1.first_name, table1.last_name, table2.street_line1 FROM table1, table2 WHERE id = 15513 AND table1.id = table2.id AND table1.first_name = 'Bart' AND table1.last_name = 'Simpson' AND SYSDATE BETWEEN table2.from_date AND NVL(table2.to_date,SYSDATE+1); The "SYSDATE portion" references the time in which "Bart Simpson" has lived at his lastest residence.My problem so far boils down to Cartesian crap and I'm having a hard time understanding why. I *think* I understand what a Cartesian product is (it's basically taking each row of the first table and retrieving it for every row of the second table, right?) and despite my best efforts at avoiding this, I just keep falling into the trap!So I'm thinking that this must have something to do with making better use of JOINs or else INDEX?Either way, I could really use some help right about now and appreciate your time.  And please let me know if you need more information. I'll try to supply everything I can. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-11 : 12:13:59
|
| This does not look like a T-SQL query; for one thing it would have complained about ambiguous id. Also about the NVL,SYSDATE functions. Are you using MS-SQL? This forum is exclusively MS-SQL and is infested(*) with people who have in-depth knowledge of MS-SQL. You may get better answers in other forums (dbforums?) if you are using Oracle, MySQL etc.That said, your query looks fine to me.SNM.PS: (*) I meant that in a very nice, polite and respectful way :--) |
 |
|
|
Wolf_22
Starting Member
2 Posts |
Posted - 2011-03-11 : 12:18:44
|
| My bad, sunitabeck. As you can tell, I'm obviously new to these forums.That said, I'll go check out those other forums and see what I can figure out over there. But yes, I'm using Oracle 11g (SQL). |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-12 : 04:25:19
|
| In oracle you can use ansi join syntax and it is highly recommended, because it is much easier to understand complex query using that syntax.Maybe instead of table1.id = table2.id you should use table1.addressId = table2.id or something like that?So the question is: What are primary key columns in table1 and table2 and what is/are foreign key column(s) in one table that matche primary key column(s) in the other?MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
|
|
|
|
|
Would this be an INNER JOIN issue?