SQL Statement Help Pls

[Ben_P]

Hi all, I am creating a MySQL database and creating the database based around a DVD Company and just wondering if anyone could help me out with a little SQL statement problem I have (First time using MySQL)

I want to List the first and last name of all the staff (for example) working at the New York Outlet in alphabetical order.

I created tables for each relationship i.e. staff information, outlet etc

Staff Information​

Staff Number
First and Last Name
Home Address
Home Phone Number
DOB
Sex
National Insurance Number
Date Joined Company
Job Title
Salary
Location (Outlet)​

If anyone could help me out with this would be much appreciated.

Regards Ben.

 

[Pat Hartman]

First and Last names should be in separate fields. There are many occasions where you want just the first or last name by itself so they should NEVER be stored in a single attribute. Once they are stored together, it is significantly more trouble to split them because of the variability of names. Some people have compound first names - Mary Ann or compound last names St James and parsing them becomes a serious problem.

Change your table design to solve your problem.

 

[georgedwilkinson]

Be happy to help, but I hope you didn't post the actual names of your objects. If so, consider renaming them without special characters/spaces. I don't know MySQL syntax for object names with spaces (could be [] or "", or something else--just fix it right if you can). If you're doing it from Access, you can use [].

The general SQL syntax will be:

select * from [Staff Information] where
Outlet = 'New York Outlet'
order by [Last Name], [First Name];

If the string for the outlet is stored in a separate table, you can do a join without the fancy MS join syntax (depends on your MySQL version):

select * from [Staff Information], [Location] where
[Staff Information].Outlet = Location.LocationID
and
Location.Location = 'New York Outlet'
order by [Last Name], [First Name];

Don't leave out the where condition for the join or you'll get a cartesian product (that's bad).

 

[Ben_P]

Thats awesome guys thanks a lot!! Would that statement list the names in alphabetical order aswell?

 

[georgedwilkinson]

Thats awesome guys thanks a lot!! Would that statement list the names in alphabetical order aswell?

Hmmm...do you understand the implications of this line:

order by [Last Name], [First Name];

I tried to be really cautious about what I was saying and Pat touched on a subject that I had some reservations about.

The answer is, if you have a field called [Last Name] (which I said you shouldn't) and a field called [First Name] (which I said you shouldn't), yes, it will be in alphabetical order.

 

[Ben_P]

Thanks for explaining much appreciated, its my first time using SQL and I am just trying to get the ball roling

If I wanted to list the daily rate hire for example for three differant movies how well do you think the following statement would do?


select DailyHireRate as Remuneration
from DVDData
where DVDName = ‘DVD1’, ‘DVD2’, ‘DVD3’;


From table:

DVDData
DVDName
ReleaseDate
AgeCertificate
DailyHireRate

 

[georgedwilkinson]

Replace:

where DVDName = ‘DVD1’, ‘DVD2’, ‘DVD3’;

with:

where DVDName in (‘DVD1’, ‘DVD2’, ‘DVD3’);

You should be able to do a lot of this stuff in the query designer much faster than asking on the forum. But I'm always glad to help...just maybe not as fast as you could do it using the tools.

 

[Ben_P]

Thats great thanks a lot for your help! I'll check out the query designer didn't know it even existed in mysql yog.

 

[georgedwilkinson]

I misunderstood you. I was talking about the Access query designer.

You can still use the Access tool to design your queries for MySQL though. Just link to your MySQL tables and copy/paste the code Access creates. There are some major differences you have to take into account, though.

Yell if you need help converting from Access SQL to MySQL SQL.