| Author | Topic |
|---|
I Supported Toymods
Location:
melbourne.vic.au
Registered:
May 2002
|
|
HELP! SQL Query for Uni...
|
Tue, 28 October 2003 03:24
|
 |
Hey All,
My teacher said that I could not use MS Access as my Query Generator and I have to use my brain 
Query is:
SELECT RepairLog.*, Techicians.chargeperhour FROM Techicians INNER JOIN (PCDetails INNER JOIN RepairLog ON PCDetails.pc_id = RepairLog.pc_id) ON Techicians.techicians_id = RepairLog.techicians_id
Can anyone be able to convert it to standard SQL (Without mentioning Inner Join)?
Cheers.
|
|
|
Location:
ballarat
Registered:
April 2003
|
|
Re: HELP! SQL Query for Uni...
|
Tue, 28 October 2003 06:06
|
|
rough gestimate, but ill give it a try...
select repairlog.*, technicians.chargeperhour from technicians, (select * from pcdetails, repairlog where pcdetails.pc_id = repairlog.pc_id) where technicians.technicians_id = repairlog.technicians_id
im not big on databases, but maybe it'll steer you in the right direction 
|
|
|
Location:
Cabramatta, NSW
Registered:
May 2002
|
 Re: HELP! SQL Query for Uni...
|
Tue, 28 October 2003 09:40
|
|
Getting others to do your assignments for you? 
You're only cheating yourself.
|
|
|
I Supported Toymods
Location:
melbourne.vic.au
Registered:
May 2002
|
|
Re: HELP! SQL Query for Uni...
|
Tue, 28 October 2003 21:35
|
|
ehendrikd,
Thanks for the Query... I will try it out...
Nark, thanks for your opinion and it's true. I am cheating myself but after all, if the program is nearly finished, by completely changing the query means I would have to change the function of the program, it's too late to alter the program
Cheers.
|
|
|
I Supported Toymods
Location:
melbourne.vic.au
Registered:
May 2002
|
|
Re: HELP! SQL Query for Uni...
|
Tue, 28 October 2003 21:38
|
|
PS: In TAFE, I never learnt how to do a nested SQL Statement  and in Uni I got excempted from the SQL subject
Cheers
|
|
|
Location:
Sydney
Registered:
July 2002
|
|
Re: HELP! SQL Query for Uni...
|
Tue, 28 October 2003 23:55
|
|
how do you do an inner join in "Standard SQL"? I can write it in sql for oracle.
also, always structure your SQL statements nicely. Start the Select/from/where/and on a new line.
SELECT RepairLog.*, Techicians.chargeperhour
FROM Techicians, RepairLog, PCDetails
WHERE RepairLog.technicians_id = Technicians.technicians_id
AND PcDetails.pc_id &= RepairLog.pc_id
note: some databases use *= or other ways defining innner joins.
I don't understand the statement though as why are you inner joining PCDetails if your not using it to link to another table and your not using any of the details in there. Is it just selecting the ones that actually have some PCDetails? If so then the above statement should work (without a database to test it on who knows??).
|
|
|
I Supported Toymods
Location:
melbourne.vic.au
Registered:
May 2002
|
|
Re: HELP! SQL Query for Uni...
|
Wed, 29 October 2003 06:14
|
|
Thanks HKSPete,
I thought of doing it but hell... I didn't know why I didn't use that simple statement you wrote up  .
Thanks for that...
Cheers.
|
|
|
Location:
Sydney
Registered:
July 2002
|
|
Re: HELP! SQL Query for Uni...
|
Wed, 29 October 2003 06:45
|
|
No probs
Just remember to set it out nicely for a start, it is SOOOO much easyer to see whats happening and so quicker to find whats wrong. Especially when you start getting SQL statements that are stupidly long (I've seen a few that were 1 and a half pages long BEFORE being set out nicely!  )
|
|
|
Location:
Perth
Registered:
November 2002
|
|
Re: HELP! SQL Query for Uni...
|
Thu, 30 October 2003 02:41
|
|
|
you'd have to be some kind of masochist to write an sql statement that long
|
|
|
Location:
Sydney
Registered:
July 2002
|
|
Re: HELP! SQL Query for Uni...
|
Thu, 30 October 2003 02:49
|
|
|
Or want something specific done on a large database.
|
|
|
Location:
ballarat
Registered:
April 2003
|
|
Re: HELP! SQL Query for Uni...
|
Thu, 30 October 2003 05:32
|
|
|
was i close hkspete? we use an application framework, so i havent had to do any sql queries in a while.
|
|
|
Location:
Sydney
Registered:
July 2002
|
 Re: HELP! SQL Query for Uni...
|
Thu, 30 October 2003 05:53
|
|
| ehendrikd wrote on Tue, 28 October 2003 17:06 |
select repairlog.*, technicians.chargeperhour from technicians, (select * from pcdetails, repairlog where pcdetails.pc_id = repairlog.pc_id) where technicians.technicians_id = repairlog.technicians_id
|
You didn't format it nicely 
I don't think you need another select though (and why are you selecting *?), your not assigning the values to anything.
I think the whole inner statement is in the wrong spot. I think it should be after the where clause.
You havn't got a from for repairlog in your first select statement
I don't know what database its written for and don't think it will work.
SQL is relative to the database its to be run on, so your code (and my code) could run on some databases and not on others.
|
|
|
