Antz
Member
Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline
|
Hey,
If this was my table:
+--------+--------------------+
| Name | Phone Number |
+--------+--------------------+
| Ant | 123 |
+--------+--------------------+
| Ant | 456 |
+--------+--------------------+
And I wanted a query that shows me the people in my talbe what would I add to SELECT * FROM tblmain to make it only show Ant once?
Thanks for any help.
Ant.
|
Laney
Member
Registered: 6th May 03
Location: Leeds
User status: Offline
|
SELECT * FROM tblmain WHERE name=Ant
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
SELECT DISTINCT * FROM tblMain WHERE Name='ant'
|
Laney
Member
Registered: 6th May 03
Location: Leeds
User status: Offline
|
LIMIT 0, 1?
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
I think the sample data is flawed 
Why would you only want to find one "Ant" and his phone number?
Surely your data isn't in 3NF
|
Antz
Member
Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline
|
Joff, I have wroiten the table but for every line of data you add it adds your user name to it too..... so when you log in it filters by username..... only showing your results... not everyone else's..... I need to get a list of all user names for the admin tools..... but obviously it's adding the username to more than one row I need to use the distinct funtion to only have it show each user name once
I always struggle with the easyest functions.... the more advanced ones.... no trouble lol.
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
Ok, try:
code:
SELECT DISTINCT Name FROM tblMain
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
Would make more sense in the long term to have a second table called ooh... "Users"
code:
tblUsers
userid [INT, PK, AutoNumber]
username [VARCHAR(20)]
tblMain
entryid [INT, PK, AutoNumber]
userid [INT, FK tblUsers.userid, AutoNumber]
phonenumber [VARCHAR(30)]
That way, if you have two users called "Ant" there's no confusion - the system uses their UserID rather than the name itself.
|
Antz
Member
Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline
|
My way worksd ok now 
I only wanna keep it easy for my little tiny brain lol.
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
Doesn't have to be complicated to be efficient 
|
Mo
Member
Registered: 29th Jan 03
Location: Bolton, Drives Q3 S-line +
User status: Offline
|
i hate SQL!!!!
|
Antz
Member
Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline
|
I love SQL, I'm still learning alot of it though... Thanks you guys for your help.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
SQL is OK. You have this thread to thank for it, and indeed every other around here 
Your data is flawed in that example. If you want to return one record, SELECT on a primary key. If you pick a name and SELECT on that, don't be surprised if more than one person share a name.
And Joff - that table could be fully normalised - you're assuming its the same Ant with two phone numbers which of course if he only wants one number may not be the case.
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
Yes it was based on an assumption
...but an educated one 
...and you have to agree
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
quote: Originally posted by Ant D
I need to use the distinct funtion to only have it show each user name once
Or maybe store the information the correct number of times?
Give us the whole scenario. I'm sure Joff can Google for the rest of the answer
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
Google? Dude that's straight off the MySQL.com searchable reference - with comments!

  
|
Joff
Member
Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline
|
Do you think this syntax was the result of a Google query??
code: tblUsers
userid [INT, PK, AutoNumber]
username [VARCHAR(20)]
I had to painstakingly run batch sql scripts across telnet to a Unix box running Oracle 6 - that's from my (warped) mind!!
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
I was impressed that you designed in the referential integrity rather than just doing a few ad hoc JOINs about the place 
Phone number isn't an INT as well. Psuedo'd like a true professional
|
Antz
Member
Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline
|
quote: Originally posted by Ian
SQL is OK. You have this thread to thank for it, and indeed every other around here 
Your data is flawed in that example. If you want to return one record, SELECT on a primary key. If you pick a name and SELECT on that, don't be surprised if more than one person share a name.
And Joff - that table could be fully normalised - you're assuming its the same Ant with two phone numbers which of course if he only wants one number may not be the case.
That was an example, looking back now I km#now it was a silly one.... it's the username field I'm using, not a name field, and when you register it adds your details and no one else can use that name.
|
Antz
Member
Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline
|
OK... here's the whole thing...
User registers... got that sussed...
User adds a contact to a table... the data is added but there is also a field there for user names... just so I know who added what contact... the filter then filters by username and only displays contacts that that username has added... not other peoples.
What I wanted to know is how to pull a list of users from the database but only have them listed once on the list... the DISTINCT thingy worked and I'm happy with it.... http://80.0.40.25/Public Address Book/
|
Gavin
Premium Member
Registered: 3rd Apr 02
Location: West Midlands
User status: Offline
|
as above ^^^^^^^^^
pew pew pew pewwwww
|