EasyCFM.COM ColdFusion Forums / Coding Help! / "Combining" SQL subqueries?

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: "Combining" SQL subqueries?

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

edge
09-05-2005 @ 4:09 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 140
Joined: May 2005

Wasn't sure if this belonged in the database forum or not, feel free to move it (my apologies if so)...

I have our User table which breaks the user name into two db fields, firstname and lastname.

Problem is, when I enter in the "last modified by" or "entered by" field data into the tables for various sections, I go off of the userid.

Now, when I do my "get" query, I was curious if there was a way to do this (within in the cfquery itself) or not.

I'm down to:


<cfquery ...>
    SELECT field1,field2,field3,
    (select firstname from usertable where userid = enteredbyid) as enteredByFirst,
    (select lastname from usertable where userid = enteredbyid) as enteredByLast,
    (select firstname from usertable where userid = lastmodifiedbyid) as lastModifiedByFirst,
    (select lastname from usertable where userid = lastmodifiedbyid) as lastModifiedByLast
    WHERE ...

</cfquery>


Just seems that 4 separate subqueries is a waste when I just want to get the firstname + lastname of the enteredby id and the firstname + lastname of the lastmodifiedby id...

It doesn't seem to affect the page load as much (been counting tick counts), but it does sure seem redundant and I wasn't sure if you could combine query data on a CF page like you could in a stored proc..

This message was edited by edge on 9-5-05 @ 4:10 PM

nmiller
09-09-2005 @ 4:56 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 773
Joined: Apr 2003

how many tables are you using here...can you post the whole query and/or table structure?

Nathan Miller
NM Consulting

edge
09-16-2005 @ 6:38 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 140
Joined: May 2005

Just one...

Essentially say it's a news article:

SELECT newsSubject,newsDesc
FROM NewsTable
...

The subqueries are to associate the numeric values in the "enteredby" and "lastmodifiedby" fields in the News table with the user table so I can get the full name of the last person to modify it, enter it, etc.

Only problem is that it just seems like a join or something would better tie it up.  I've experienced degraded performance from subqueries in other larger queries and 4 subqueries just to pull 2 values seemed like over kill Frown

This message was edited by edge on 9-16-05 @ 6:38 PM

nmiller
09-19-2005 @ 10:54 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 773
Joined: Apr 2003

try something like this:

SELECT
     n.newsSubject
     ,n.newsDesc
     ,e.firstname
     ,e.lastname
     ,m.firstname
     ,m.lastname
FROM
     NewsTable n
     INNER JOIN usertable e on n.enteredbyid = e.userid
     INNER JOIN usertable m on n.lastmodifiedbyid = m.userid



Nathan Miller
NM Consulting

This message was edited by nmiller on 9-19-05 @ 10:54 AM


Website Designed and Developed by Pablo Varando.