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 |
|
awarner20
Starting Member
5 Posts |
 Posted - 2006-09-07 : 23:33:07
|
| Hello everyone,Obviously, I'm new here so I'd like to start by thanking anyone for their help with this very newbie SQL search query question.I have been tasked with trying to figure out how to get search results from several different columns of two different tables within a database. I have successfully accomplished this, but one column has many many duplicates. I have read on these forums about using the DISTINCT command to eliminate this, but so far have been unsuccessful. Here is the search I'm using...(columns 1, 2, and 3 are from one table and columns 4 and 5 are from another. The results need to be in ascending order (alphabetical) of column1.select column1, column2, column3, column4, column5 from table1, table2 order by column1;The result of this is great except that column1 has many duplicates.I tried entering the search like this...select distinct column1, column2, column3, column4, column5 from table1, table2 order by column1;...but that didn't seem to help.Can someone steer me in the right direction?Thanks for any and all assistance. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-08 : 00:03:22
|
how do you join table1 & table2 ?select column1, column2, column3, column4, column5 from table1 inner join table2 on table1.col = table2.col
KH |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-08 : 00:05:40
|
Maybe you can also post your table structure with some sample data and the result that you need.
KH |
|
|
|
awarner20
Starting Member
5 Posts |
Posted - 2006-09-08 : 00:28:01
|
| khtan,thanks for your help with this. Here's the list of tables and columns I'm working with...adambook and adamauthor are the tables. adambook contains these columns...title, isbn, and pubdate.adamauthor contains these columns...firstname and lastname.The result I'm trying to acheive is to have the columns from both tables listed in the results like this...title, isbn, pubdate, firstname, lastname. (with the title column listed in alphabetical order and the firstname and lastname columns matching the titles)When I tried with the original query, I get everything I need, but the title column has many many duplicates and the authors don't match up with the titles...does that make sense?I apologize, but I am a complete novice with SQL. I will be going to the bookstore tomorrow for studying material;)Again, any help is greatly appreciated:) Please let me know if I've left out any important info relating to the problem.p.s. I thought I was joining table1 (adambook) and table2 (adamauthor) by using this...select title, isbn, pubdate, firstname, lastname from adambook, adamauthor order by titleThis gives me all the columns I need, but with the above mentioned title duplicates.Thanks again. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-08 : 02:59:30
|
"When I tried with the original query, I get everything I need, but the title column has many many duplicates and the authors don't match up with the titles...does that make sense?"Yes. You are performing a cross join between the 2 tables in your original query.You query should be something like thisselect b.title, b.isbn, b.pubdate, a.firstname, a.lastnamefrom adambook b inner join adamauthor a on b.author_id = a.author_idorder by b.title What you missing is the part in red. You also did not mention in your post how adambook is related to adamauthor. So I assume there is an column author_id that is common in these 2 tables.
KH |
|
|
|
awarner20
Starting Member
5 Posts |
Posted - 2006-09-08 : 08:24:32
|
| Thanks for the info, I have tried, and it hasn't worked. I'm afraid I'm a bit confused by the "b.title" and "a.firstname" language. How do the "a" and "b" letters relate to my columns?The common column to both tables is the authorid column. Here are the columns in adambookTitleID Title AuthorID ISBN RetailPrice NarratorID Abridged PubDate8758 Around 112 978- 2495 38 ABR 11/2/2003Here are the columns in adamauthorauthorID FirstName LastName3 Elizabeth LowellDoes that help? Again, I sincerely apologize for my ignorance here. I know I'm close to a solution. |
|
|
|
awarner20
Starting Member
5 Posts |
Posted - 2006-09-08 : 08:33:20
|
| It worked!!! Thank you sooo much!!! I copy and pasted and had some extra spaces the first time I tried:)I'm reading up on the "ON" command now.Thank you!!!!! |
|
|
|
awarner20
Starting Member
5 Posts |
Posted - 2006-09-08 : 08:58:41
|
| I'm sorry, I do have one more question.I am now trying to add two more columns from a third table called adamnarrator. The columns are narratorid, nfname, nlname. I'm trying to add the nfname and nlname columns to the previous results. This table does not have a common column with the adambook and adamauthor tables.I put in the search as... select b.title, b.isbn, b.pubdate, a.firstname, a.lastname, nfname, nlname from adamnarrator, adambook b inner join adamauthor a on b.authorid = a.authorid order by b.titleI am getting duplicates in the title column again. I also tried putting in the search by adding "c" to the nfname and nlname columns like this...select b.title, b.isbn, b.pubdate, a.firstname, a.lastname, c.nfname, c.nlname from adamnarrator c, adambook b inner join adamauthor a on b.authorid = a.authorid order by b.title...but I still get the duplicates. Do I have this in the correct order? |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-09-12 : 15:22:30
|
| You forgot to JOIN the extra table (C) on one of the other tables. What's the field in adamnarrator that connects it to either adambook or adamauthor? IOW, how do you know which book the narratorid connects to, or which author?Ken |
|
|
|
|
|
|
|
|
Search Query Help with duplicates?