Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

SQL Query with subqueries


  • Please log in to reply

#1
funtmachine

funtmachine

    Member

  • Member
  • PipPip
  • 69 posts
Hey all..

Iam having trouble writing this SQL query.....see if anyone can help...


I have 3 Tables: Equipment, Task and Quantity.

-------------------------------
Equipment
-------------------------------
ID | Description | Category
-------------------------------
1 | ss9 2.1 | Bolts
2 | ss9 3.5 | Bolts



---------------------
Task
---------------------
ID | Description
---------------------
1 | Drilling
2 | Installing
3 | Testing



-----------------------------------
Quantity
-----------------------------------
Date | EquipID | TaskID | Qty
-----------------------------------
04/12| 1 | 2 | 10
05/12| 2 | 3 | 5

Basically what I have is a bunch of equipment that can have cerain tasks performed on it. At the end of each day, I want to be able to enter the number of bolts that have had each task applied to them in the quantity table, and produce a report from this table. I need to be able to show the following table in a result set..

--------------------------------------------------------------
equipment.description | drilling | installing | testing
--------------------------------------------------------------
ss 2.1 | 0 | 10 | 0
--------------------------------------------------------------

to do this, I need to use relationships from all three tables, and need a correlated subquery. I need to have sub querys that can access values from the outer query to do this....


can anyone help??

TIA

Dave
  • 0

Advertisements


#2
Ctrl_Alt_Del

Ctrl_Alt_Del

    Member

  • Member
  • PipPip
  • 74 posts
In Oracle

Select E.Description,
nvl((Select QTY from quantity q where q.task_id = 1 and q.equipid = e.id),0) as drilling, 
nvl((Select QTY from quantity q where q.task_id = 2 and q.equipid = e.id),0) as installing, 
nvl((Select QTY from quantity q where q.task_id = 3 and q.equipid = e.id),0) as testing
from equipment e

  • 0

#3
funtmachine

funtmachine

    Member

  • Topic Starter
  • Member
  • PipPip
  • 69 posts
mm.....

MySQL said:

#1305 - FUNCTION shiftreporting.nvl does not exist .


shiftreporting is the database name by the way...

also....I need the sum of the quantities in the sub queries, so would this suffice in mysql?

Select e.Description,
(Select sum(quantity) from quantity q where q.taskID = 4 and q.equipmentID = e.equipmentID) as drilling, 
(Select sum(quantity) from quantity q where q.taskID = 5 and q.equipmentID = e.equipmentID) as installing, 
(Select sum(quantity) from quantity q where q.taskID = 6 and q.equipmentID = e.equipmentID) as testing
FROM equipment e, quantity q WHERE q.jobID = 0


I have tried it, but it gives me a syntax error near line 1, incorrect syntax to use near ' '.....

What does the nvl() part do in your query??

TIA :whistling:

Dave

Edited by funtmachine, 13 December 2006 - 11:27 PM.

  • 0

#4
Ctrl_Alt_Del

Ctrl_Alt_Del

    Member

  • Member
  • PipPip
  • 74 posts
The nvl function returns the specified value if the return value is null. Not really needed. Yes the sum should work but add a group by on description. According to a tiny amount of research MySql uses ifnull in place of nvl.
  • 0

#5
funtmachine

funtmachine

    Member

  • Topic Starter
  • Member
  • PipPip
  • 69 posts
Thanks Ctrl_Alt_Del,

Everything works after a little research. cheers....
  • 0

#6
Ctrl_Alt_Del

Ctrl_Alt_Del

    Member

  • Member
  • PipPip
  • 74 posts
You're welcome. Thanks for letting me know that it worked for you.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP