SELECT t.groupid,
t.groupname
FROM @tblgroups t
INNER JOIN @tblgrouptypes gt ON gt.groupid = t.groupid
WHERE gt.grouptype in ('College','Jewish') -- This would be dynamic
GROUP BY t.groupid,
t.groupname
HAVING count(*) = 2 -- This would be dynamic
ORDER BY t.groupname
: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2008 6:27 PM
To: CF-Talk
Subject: Re: Need query help
No, that'll get one or the other - he wants ones that are both.
Aliasing will work, something like this:
James, I think I got this to work. My first tests are good. I didn't
mention
Could do a UNION to gather the results
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2008 10:56 AM
To: CF-Talk
Subject: RE: Need query help
If you query is highly dynamic, this way may not be best. Let's say the
user can specify groups
What would that look like? Unioned result sets are not mutually
dependant, but Will needs all his criteria to be met.
~Brad
-Original Message-
From: Adkins, Randy [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2008 10:00 AM
To: CF-Talk
Subject: RE: Need query help
Could do a UNION
This should work and is much clearer that your original statement.
SELECT t.groupid,
t.groupimage,
t.groupname,
t.groupcity,
t.state,
t.voicingid,
t.groupcontactperson,
t.country,
t.region,
x.groupid,
, 2008 10:50 AM
To: CF-Talk
Subject: Re: Need query help
This should work and is much clearer that your original statement.
SELECT t.groupid,
t.groupimage,
t.groupname,
t.groupcity,
t.state,
t.voicingid,
t.groupcontactperson,
t.country
Brad Wood wrote:
If you query is highly dynamic, this way may not be best. Let's say the
user can specify groups that are of type college, jewish, preschool,
catholic, and skinny. Now, you have to join to the type table 5 times
and that ain't looking too good.
Joining is what databases are
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2008 12:12 PM
To: CF-Talk
Subject: Re: Need query help
Joining is what databases are good at.
===
Flying is what birds are good at; but why send the whole
Will Tomlinson wrote:
Hey,
I've got groups that can have one or more types associated with them. I have
a linking table that I'm querying and filtering. But I'm not getting the
results I need.
Example: A group can be both 'College' and 'Jewish', as listed in the many
linking table -
To make your query more readable, can you alias your tables?
SELECT g.groupid, g.groupimage, g.groupname, g.groupcity, g.state,
g.voicingid, g.groupcontactperson, g.country, g.region,
gtx.groupid, gtx.grouptype, gt.grouptype
FROM tblgroups g, tblgrouptypes gt, tblgrouptypes_x
If you take out one or the other, does it return the results?
Yes. I get valid results if I remove one of the AND's. I can just pull jewish
groups for instance.
Originally, I used IN ('jewish',college').
But that returns ALL jewish groups, and ALL college groups. My client wants it
to
Try changing your AND statement to an OR statement:
AND (tblgrouptypes_x.grouptype = 'College' OR tblgrouptypes_x.grouptype =
'Jewish')
-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2008 5:39 PM
To: CF-Talk
Subject: Need query help
Hey,
: Monday, March 03, 2008 5:37 PM
To: CF-Talk
Subject: RE: Need query help
Try changing your AND statement to an OR statement:
AND (tblgrouptypes_x.grouptype = 'College' OR tblgrouptypes_x.grouptype
=
'Jewish')
-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: Monday
No, that'll get one or the other - he wants ones that are both.
Aliasing will work, something like this:
SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname,
tblgroups.groupcity, tblgroups.state, tblgroups.voicingid,
tblgroups.groupcontactperson, tblgroups.country,
I'm a little unsure of what your query is expected to return. If a
group can have multiple types, then I assume that there is a one-to-many
relationship between groups and types, where a single group record has
one or more type records.
Correct.
You are returning type in your query, so a
If there's a group that's jewish and college, I just need a single
record returned.
=
Then removed type from the select list, group by everything else with an
or equivalent in your where clause and add the having count(*) 1
to the end.
That will only return groups which
If you HAVE to have both jewish and college types and only one record
returned, try this:
SELECT tblgroups.*, !--- i wouldn't do this, just saving space here ;) ---
tblgrouptypes_x.groupid,
tblgrouptypes_x.grouptype !--- perhaps returning this value is misleading
as it can only tell you one of
No, that'll get one or the other - he wants ones that are both.
Aliasing will work, something like this:
James, I think I got this to work. My first tests are good. I didn't mention
this is a highly dynamic query. I have quite a few other filters in play, but
your example would work nicely
Jeasus man! I'm gonna show you something, it'll take me about an hour to
rewrite your query though!! Bare with me...
1. I might bear with ya dude, but I aint gonna BARE with you. Check with dave
for that one. lol!
2. Oh yeah, the query is lookin' sweet now! Ok, I'll start tryin' to use
Without spending a large amount of time looking at your sql, have to
tried grouping a field by doing a GroupBy?
-Aaron
On 2/6/06, Will Tomlinson [EMAIL PROTECTED] wrote:
I have a query that's returning too many shipoptionID's. Query analyzer shows
4 popping up.
Almost like I need another AND
Maybe it's just the fact that it's in an email, but that is one chunk of
SQL! Why don't you use table alias' to shorten it some?
Ade
-Original Message-
From: Aaron Roberson [mailto:[EMAIL PROTECTED]
Sent: 07 February 2006 01:29
To: CF-Talk
Subject: Re: Need query help
Without spending
Without spending a large amount of time looking at your sql, have to
tried grouping a field by doing a GroupBy?
I think I got it figured out aaron. The problem wasn't the query. It was just
running twice! heeheheee
Thanks,
WIll
Maybe it's just the fact that it's in an email, but that is one chunk of
SQL! Why don't you use table alias' to shorten it some?
I've just never liked the way they look. They kinda mix me up.
Will
~|
Message:
?! :OD
-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: 07 February 2006 00:40
To: CF-Talk
Subject: Re: Need query help
Maybe it's just the fact that it's in an email, but that is one chunk of
SQL! Why don't you use table alias' to shorten it some?
I've just never
Another good reason to at least stay friendly with table aliases is
that they can provide for very powerful subqueries. They may look
weird but they are very nice when you get to know them.
On 2/6/06, Adrian Lynch [EMAIL PROTECTED] wrote:
Jeasus man! I'm gonna show you something, it'll take me
The way I do it on a couple of sites is to store large images in one folder
and thumbs in another. I store one file name in the db table and just use:
#application.photos#\#table.jpeg_name#
or
#application.photos_thumb#\#table.jpeg_name#
Jenny
I just needed help making my query work. Matthieu is helping me with a self
join.
Thanks,
Will
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
why not just name the images something like img_large.jpg and img_thumb.jpg
this way you can just store img in the database (in one record), and
output #img#_large.jpg or #img#_thumb.jpg depending on which you need?
On 11/2/05, Will Tomlinson [EMAIL PROTECTED] wrote:
For my products, I have a
yeah I agree with Charlie.. just add an extra column called
thumbnail with the image name.
On 11/2/05, Charlie Griefer [EMAIL PROTECTED] wrote:
why not just name the images something like img_large.jpg and
img_thumb.jpg
this way you can just store img in the database (in one record), and
29 matches
Mail list logo
