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 |
|
Luinearan
Starting Member
3 Posts |
 Posted - 2007-11-19 : 14:48:29
|
| I have the following SQL code...SELECT DISTINCT Unit.[Unit Name] AS Unit_Name, Cadet.[Cadet ID] AS Cadet_ID, Cadet.[Cadet First Name] AS Cadet_First_Name, Cadet.[Cadet Last Name] AS Cadet_Last_Name, Cadet.[Cadet Middle Initial] AS Cadet_Middle_Initial, Cadet.[MSL Level] AS MSL_Level, Cadet.GPA, Cadet.[How found out about SMP] AS How_found_out_about_SMP, Cadet.[BCT Date] AS BCT_Date, Cadet.[Planned to Contract] AS Planned_to_Contract, Cadet.[NG or AR] AS NG_or_AR, Cadet.[Cadet Email Address] AS Cadet_Email_Address, Cadet.AIT FROM (Unit INNER JOIN Cadet ON Unit.[Unit Name] = Cadet.[Unit Name])I have this connected with a database. There is a dropdown list associated with Unit.Unit Name. My problem is....When you click the drop down to view contents there are a number of items listed the same about of times listed in the Cadet Table. For instance... 101 Airborne is listed 6 times. I only want it listed once.Any one help me on this?I can give more info if needed. |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-11-19 : 16:07:20
|
No, you don't have that SQL, as Cadet.AIT FROM (Unit INNER JOIN Cadet ON Unit.[Unit Name] = Cadet.[Unit Name]) is missing a tablename in the FROM statement before the opening paren.If you want someone to help you fix the SQL, post the actual SQL. |
 |
|
|
Luinearan
Starting Member
3 Posts |
Posted - 2007-11-19 : 16:17:19
|
| That is all the SQL code I have. |
|
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2007-11-21 : 06:55:36
|
Hint: there are 6 DISTINCT cadets that join to that unit name. You are selecting lots of data and joining a table when you only require a single column for your listbox. This is a shameless waste of database resources. Your (I Guess inherited, everybody says they inherited poor design he he) column names show a lack of experience in database driven application development too :) I guess that's why you're here. Column names without spaces belong in the table design, names with spaces belong in the the presentation layer, i.e. where it's presented to the user.SELECT DISTINCT [Unit Name] As Unit_Name, --Comment this is topsy turvey!FROM Unit ;-]... Quack Waddle |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-21 : 10:32:30
|
| This sounds like a microsoft access database. You may have copied the sql from a property field which has a limited size, i.e. did not show everything. To see all the sql code, try to copy it from an editor window.You have duplicate values because your query returned duplicate values. That is due to the join and the many to one relationship between those two tables (cadet and unit).From the incomplete sql, it seems you could get rid of the unit table since unit_Name is already in cadet table... |
|
|
|
|
|
|
|
|
Return only uniqie?