MySQL Query - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » MySQL Query

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

Antz

Member

Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline

15th Feb 04 at 14:48

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

15th Feb 04 at 14:53

SELECT * FROM tblmain WHERE name=Ant

Joff

Member

Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline

15th Feb 04 at 14:54

SELECT DISTINCT * FROM tblMain WHERE Name='ant'

Laney

Member

Registered: 6th May 03
Location: Leeds
User status: Offline

15th Feb 04 at 14:56

LIMIT 0, 1?

Joff

Member

Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline

15th Feb 04 at 14:58

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

15th Feb 04 at 15:12

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

15th Feb 04 at 15:45

Ok, try:

code:


SELECT DISTINCT Name FROM tblMain

Joff

Member

Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline

15th Feb 04 at 15:50

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

15th Feb 04 at 17:11

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

15th Feb 04 at 17:25

Doesn't have to be complicated to be efficient

Member

Registered: 29th Jan 03
Location: Bolton, Drives Q3 S-line +
User status: Offline

15th Feb 04 at 17:28

i hate SQL!!!!

Antz

Member

Registered: 28th Jul 03
Location: Leeds Drives: Myself Insane!
User status: Offline

15th Feb 04 at 19:32

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

15th Feb 04 at 21:04

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

15th Feb 04 at 21:08

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

15th Feb 04 at 23:48

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

15th Feb 04 at 23:52

Google? Dude that's straight off the MySQL.com searchable reference - with comments!

Joff

Member

Registered: 17th Oct 00
Location: Cambridgeshire
User status: Offline

15th Feb 04 at 23:54

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

16th Feb 04 at 00:23

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

20th Feb 04 at 18:04

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

20th Feb 04 at 18:11

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

20th Feb 04 at 18:14

as above ^^^^^^^^^

pew pew pew pewwwww

New Topic

New Poll

Corsa Sport » Message Board » Off Day » MySQL Query

24 database queries in 0.1203489 seconds