School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 300,459
  • Replies: 826,087
  • Topics: 137,467
  • Snippets: 4,420
  • Tutorials: 1,148
  • Total Online: 1,621
  • Members: 88
  • Guests: 1,533

Welcome to Dream.In.Code
Become an Expert!

Join 300,459 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,621 people online right now. Registration is fast and FREE... Join Now!




Querying Multiple Tables

 

Querying Multiple Tables, Assistance with FULL JOIN please!
SpeeDemon

23 Jun, 2009 - 08:42 PM
Post #1

New D.I.C Head
*

Joined: 18 Mar, 2008
Posts: 44


My Contributions
Hello, I am trying to query several tables for output into 1 grid. The problem is that one of the "SELECT" is NOT always there. Not every workorder has a subcategory selected. When I run the following query, only the entries with ALL of the SELECT conditions as true will show up. How can I make it show ALL results? (IE: If a category/subcategory field is blank, show the workorder anyway)

CODE
SELECT wo.WORKORDERID AS 'ID',
u.FIRST_NAME AS 'Requester Name',
FROM_UNIXTIME(wo.CREATEDTIME/1000) AS 'Created Date',
wo.TITLE AS 'Subject',
cd.CATEGORYNAME AS 'Category',
scd.SUBCATEGORYID AS 'Sub Category'

from workorder wo,
aaauser u,
workorderstates wos,
categorydefinition cd,
subcategorydefinition scd

where
wo.requesterid=u.user_id
and wos.workorderid=wo.workorderid
and cd.categoryid=wos.categoryid
and scd.subcategoryid=wos.subcategoryid

ORDER BY wo.CREATEDTIME DESC;


I am including a picture of the database schema to help any of you further visualise why I am doing it this. Perhaps you can provide a completely different way as well!

Thank you!

Edit: Adjusted code text to significantly better fit the small window

This post has been edited by SpeeDemon: 23 Jun, 2009 - 08:47 PM


Attached thumbnail(s)
Attached Image
User is offlineProfile CardPM
+Quote Post


CamoDeveloper

RE: Querying Multiple Tables

24 Jun, 2009 - 04:11 AM
Post #2

D.I.C Head
Group Icon

Joined: 12 Jun, 2009
Posts: 204



Thanked: 12 times
Dream Kudos: 200
My Contributions
In your statement, you were telling the database to look at all the tables and select only the data that matches the WHERE clause. You need to join the tables together so you can get the desired results you're looking for. You can't go from wo.requesterid to aaauser.userid. You have to join sduser so you can use the aaauser table. Here is something that will get you started, I don't know what database you're using so it might need to change accordingly.

CODE

SELECT
  wo.WORKORDERID AS 'ID'
  , u.FIRST_NAME AS 'Requester Name'
  , wo.CREATEDTIME/1000) AS 'Created Date',
  , wo.TITLE AS 'Subject'
  , cd.CATEGORYNAME AS 'Category'
  , scd.SUBCATEGORYID AS 'Sub Category'

FROM
  workorder wo
  LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
  LEFT JOIN categorydefinition cd ON wos.categoryid = cd.categoryid
  LEFT JOIN subcategorydefinition scd ON wos.subcategoryid = scd.subcategoryid
  LEFT JOIN sduser ON wos.ownerid = sduser.userid
  LEFT JOIN aaauser u ON sduser.userid = u.userid

WHERE
  wo.requesterid = sduser.userid
  AND sduser.userid = u.userid
  AND wos.workorderid = wo.workorderid
  AND cd.categoryid = wos.categoryid
  AND scd.subcategoryid = wos.subcategoryid

ORDER BY wo.CREATEDTIME DESC;


Hope this helps!

~Camo
User is offlineProfile CardPM
+Quote Post

SpeeDemon

RE: Querying Multiple Tables

24 Jun, 2009 - 07:12 AM
Post #3

New D.I.C Head
*

Joined: 18 Mar, 2008
Posts: 44


My Contributions
I tried this and still only get 1 result :-(

CODE
SELECT
  wo.WORKORDERID AS 'ID',
  u.FIRST_NAME AS 'Requester Name',
  FROM_UNIXTIME(wo.CREATEDTIME/1000) AS 'Created Date',
  wo.TITLE AS 'Subject',
  cd.CATEGORYNAME AS 'Category',
  scd.SUBCATEGORYID AS 'Sub Category'

FROM
  workorder wo
  LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
  LEFT JOIN categorydefinition cd ON wos.categoryid = cd.categoryid
  LEFT JOIN subcategorydefinition scd ON wos.subcategoryid = scd.subcategoryid
  LEFT JOIN sduser ON wo.requesterid = sduser.userid
  LEFT JOIN aaauser u ON sduser.userid = u.user_id

WHERE
  wo.requesterid = sduser.userid
  AND sduser.userid = u.user_id
  AND wos.workorderid = wo.workorderid
  AND cd.categoryid = wos.categoryid
  AND scd.subcategoryid = wos.subcategoryid

ORDER BY wo.CREATEDTIME DESC;


I tried changing LEFT JOIN to FULL JOIN to try and include any results that may have null values (ones without category/subcategory IDs) and I get a syntax error.

QUOTE
I don't know what database you're using so it might need to change accordingly.
Oops sorry, this is MySQL!

This post has been edited by SpeeDemon: 24 Jun, 2009 - 07:14 AM
User is offlineProfile CardPM
+Quote Post

CamoDeveloper

RE: Querying Multiple Tables

24 Jun, 2009 - 09:33 AM
Post #4

D.I.C Head
Group Icon

Joined: 12 Jun, 2009
Posts: 204



Thanked: 12 times
Dream Kudos: 200
My Contributions
You can try using INNER JOIN

~Camo
User is offlineProfile CardPM
+Quote Post

SpeeDemon

RE: Querying Multiple Tables

24 Jun, 2009 - 11:34 AM
Post #5

New D.I.C Head
*

Joined: 18 Mar, 2008
Posts: 44


My Contributions
QUOTE(CamoDeveloper @ 24 Jun, 2009 - 09:33 AM) *

You can try using INNER JOIN

~Camo



LEFT/INNER/RIGHT all produce the same results (Missing the rows with a NULL category). I found this exerpt from the MySQL Reference Manual:

QUOTE
Thus, previously, the preceding query was transformed to the following equi-join:
CODE
SELECT ... FROM t1, t2, t3
  WHERE t1.b = t2.b AND t2.c = t3.c;

That join is missing one more equi-join predicate (t1.a = t3.a). As a result, it produces one row, not the empty result that it should. The correct equivalent query is this:
CODE
SELECT ... FROM t1, t2, t3
  WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;


I have tinkered with this a bit but I keep getting table could not be found after trying to make my statements match the example. Any suggestions?

Thank you.
User is offlineProfile CardPM
+Quote Post

CamoDeveloper

RE: Querying Multiple Tables

24 Jun, 2009 - 04:35 PM
Post #6

D.I.C Head
Group Icon

Joined: 12 Jun, 2009
Posts: 204



Thanked: 12 times
Dream Kudos: 200
My Contributions
Hmmm...I can't help you out too much with MySQL but can you do something like :
CODE

WHERE
  t1.b = t2.b
  AND t2.c = t3.c
  AND t1.a = t3.a
  AND table.columnname IS NOT NULL


~Camo
User is offlineProfile CardPM
+Quote Post

SpeeDemon

RE: Querying Multiple Tables

24 Jun, 2009 - 05:02 PM
Post #7

New D.I.C Head
*

Joined: 18 Mar, 2008
Posts: 44


My Contributions
CODE
SELECT
  wo.WORKORDERID AS 'ID',
  u.FIRST_NAME AS 'Requester Name',
  FROM_UNIXTIME(wo.CREATEDTIME/1000) AS 'Created Date',
  wo.TITLE AS 'Subject',
  cd.CATEGORYNAME AS 'Category',
  scd.SUBCATEGORYID AS 'Sub Category'

FROM
  workorder wo, sduser sdu
  left join aaauser u on u.user_id=sdu.userid
  left join workorderstates wos on wo.workorderid=wos.workorderid
  left join categorydefinition cd on wos.categoryid=cd.categoryid
  left join subcategorydefinition scd on wos.subcategoryid = scd.subcategoryid

WHERE
  wo.requesterid = sdu.userid

ORDER BY wo.CREATEDTIME DESC;


This seems to work. Thanks for your guidance Camo, it appears that my "WHERE" statements were conflicting with the LEFT JOIN.
User is offlineProfile CardPM
+Quote Post

CamoDeveloper

RE: Querying Multiple Tables

24 Jun, 2009 - 08:24 PM
Post #8

D.I.C Head
Group Icon

Joined: 12 Jun, 2009
Posts: 204



Thanked: 12 times
Dream Kudos: 200
My Contributions
Glad I could help the little bit I did lol.

~Camo
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/8/09 02:12AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month