SQL HELP!!!

[phamtum]

SQL HELP!!!

Here is my query below, I?m simply try to pull the Distinct vac_group but not
having any luck. I have attached my DB diagram, any suggestions would be
greatly appreciated!


<CFQUERY datasource="#db#" name="getvacationWKrequest">
SELECT Distinct(t.vac_group), t.*, r.rtype, e.fname, e.lname, e.mgr,
o.otype
FROM TO_Request t
INNER JOIN employees e ON t.uuid = e.uuid
INNER JOIN request_type r ON t.request_type = r.id
INNER JOIN off_type o ON t.off_type = o.id
Where t.vac_group <> '0'
AND (t.status = 0) or (t.status = 4)
</CFQUERY>

[phamtum]

SQL HELP!!!

Here is my query below, I?m simply try to pull the Distinct vac_group but not
having any luck. I have URL LINK TO my DB diagram, any suggestions would be
greatly appreciated!


<CFQUERY datasource="#db#" name="getvacationWKrequest">
SELECT Distinct(t.vac_group), t.*, r.rtype, e.fname, e.lname, e.mgr,
o.otype
FROM TO_Request t
INNER JOIN employees e ON t.uuid = e.uuid
INNER JOIN request_type r ON t.request_type = r.id
INNER JOIN off_type o ON t.off_type = o.id
Where t.vac_group <> '0'
AND (t.status = 0) or (t.status = 4)
</CFQUERY>

[url]HTTP://PHAMLLC.COM/DIAGRAM.GIF[/url]

[Dan Bracuk]

What's the problem? crash? no records? too many records? wrong records?

Originally posted by: phamtum
SQL HELP!!!

Here is my query below, I?m simply try to pull the Distinct vac_group but not
having any luck. I have URL LINK TO my DB diagram, any suggestions would be
greatly appreciated!


<CFQUERY datasource="#db#" name="getvacationWKrequest">
SELECT Distinct(t.vac_group), t.*, r.rtype, e.fname, e.lname, e.mgr,
o.otype
FROM TO_Request t
INNER JOIN employees e ON t.uuid = e.uuid
INNER JOIN request_type r ON t.request_type = r.id
INNER JOIN off_type o ON t.off_type = o.id
Where t.vac_group <> '0'
AND (t.status = 0) or (t.status = 4)
</CFQUERY>

[url]http://PHAMLLC.COM/DIAGRAM.GIF[/url]

[mxstu]

phamtum,

Typically, that is not how "distinct" is used (ie. it is not a function).
The syntax is usually:

SELECT DISTINCT someColumnName
FROM someTableName

However, it probably won't have much effect in your current query since
you're already returning all columns from the TO_Request table and SELECT
DISTINCT operates on all columns in the SELECT list.

SELECT Distinct(t.vac_group), t.*, r.rtype, e.fname, e.lname, e.mgr, o.otype
FROM TO_Request t
......

If you could explain more about what information you're trying to pull, and
how it is used, I'm sure someone can assist you.

[phamtum]

:) sorry, it return all the records from the DB instead of just one distinct record.

[paross1]

DISTINCT works on the entire set of columns selected in a query, not just one
column. In other words, each row tht you SELECT will have a DISTINCT or UNIQUE
set of values, no duplictes where ALL of the fields are the same in more than
one row. If you want only a list of DISTINCT vac_group, then you must select
this column ONLY.

SELECT Distinct t.vac_group
FROM TO_Request t
INNER JOIN employees e ON t.uuid = e.uuid
INNER JOIN request_type r ON t.request_type = r.id
INNER JOIN off_type o ON t.off_type = o.id
Where t.vac_group <> '0'
AND (t.status = 0) or (t.status = 4)

Phil

[CF_Oracle]

Also, looks like at the end line you need AND (t.status = 0 or t.status = 4) otherwise OR has precedence and all records where t.status = 4 disregard of values t.vac_group will be selected.

[phamtum]

Thank you all for you expertise?s, I apologize for not providing more details.

What I?m trying to do is just pull one of the vac_group instead of showing all
record from ID 1 ? 5. I hope this clarify things a bit.

Table: TO_Request:
Id Uuid Request_type Off_type Req_date Vac_group Status
1 BP123 5 5 12/5/05 123GH
0
2 BP123 5 5 12/6/05 123GH
0
3 BP123 5 5 12/7/05 123GH
0
4 BP123 5 5 12/8/05 123GH
0
5 BP123 5 5 12/9/05 123GH
0
6 ED456 4 2 11/21/05 0
1
7 CS987 2 4 11/23/05 0
1
8 LL659 1 2 12/9/05 0
1

Thanks again for everyone?s help!!!

[paross1]

Well, which "one" do you want? Since they all have different dates, eacy row
selected is "unique". Take vac_group 123GH for instance. There are 5 records
with this vac_group, and each is distinct, since each has a different req_date.
If you only need distinct vac_group values, then only select them, as in my
previous post. What gets you into trouble is if you want or need the other
fields selected, in which case you probably will need two different queries,
one that gives you the distinct vac_groups, and one that gives you the other
data. Using the data that you provided, if you left the req_date out of your
query, you would probably get your distinct records. Does that make sense now?

Phil

[phamtum]

Thanks Paross1 and all that contributed....