Sql?

Alodar · 08-03-2006, 06:23 PM

Anyone really good with SQL? I have a serious problem that I've been beating my head against here today.

Azuin · 08-06-2006, 01:06 PM

Have you tried asking SQLTeam.com? First browse the articles there, though, in case the articles have already answered your question. I use it frequently at work; the site has very good information.

Lord_Vyper · 08-08-2006, 04:59 AM

I'm fair with it... though my quesries usually make legit database programmers cry

What's the question?

Giftmacher · 08-08-2006, 09:05 AM

Same here, I use SQL fairly regularly at work, I'm not all knowing but if you let me know the problem I might know an answer.

Gift.

Alodar · 08-08-2006, 12:21 PM

I'll post what I posted in various forums around the internet...

I have a table that I need 7 things from. So if I a table that i'm retrieving RecordID, UserDate, Ticket#, Description, EnterDate, Area, and Type from (not the real names, but it doesn't matter.

Here's the problem. I need to retrieve records where there are 3+ instances of type that are the same in an area, and then those three+ corresponding ticket# fields should each be different. So it could be like

Code:

Type     |Ticket#| Area
------------------------
Computer | 1b    | MN
Computer | 1c    | MN
Computer | 1c    | LA
Computer | 1e    | MN
Computer | 1a    | LA
Laptop   | GE    |
Laptop   | 2b    | TI
Laptop   | 2b    | TI
Laptop   | 1a    | UT
Monitor  | 3g    | NV
Monitor  | 3p    | NV
Monitor  | 3m    | NV

etc. Of this, I would want a result that returned

Code:

Type     | Ticket# | Area
----------------------
Computer | 1b      | MN
Computer | 1c      | MN
Computer | 1e      | MN
Monitor  | 3g      | NV
Monitor  | 3p      | NV
Monitor  | 3m      | NV

Because they were the only ones with three unique ticket# fields for 3+ possible Type's of the same type.

On top of that, I need to display the corresponding information of RecordID, UserDate, Description, EnterDate, and Area.

I believe the closest thing I can do with it involves a self join for ticket# and Type, but so far, for the life of me, i can't figure out how to make sure that there are at least 3 listed (in the area) and that the three types are different

Giftmacher · 08-08-2006, 05:19 PM

Yesh, you don't want much... hmm first thought would be to incorporate something like SELECT COUNT(Area) into the query, but you want so many things done at once I wonder if any machine could take it.

Can you not create another field to keep tabs on the number of times an entry is repeated? If you index that you should get a faster search than asking for the information on the fly. Are you querying the DB directly or can you do this using a script (e.g. perl)?

Anyway just some initial thoughts, I'll keep scratching the grey matter.

Gift.

Alodar · 08-08-2006, 05:38 PM

The closest I came sort of was to select Area, count(Type), count(distinct Ticket#)

which gave me the NUMBERS I would need...but not the data...and then of course on top of that if you tried to pull in the other fields it screws it up cause grouping requires like data

Lord_Vyper · 08-08-2006, 08:53 PM

Hmm, this could be interesting...what form of SQL are you using? MySQL, MSSQL, Oracle, DB2, PostGRES?
Does it all need to be in 1 query?
Any other fun stuff involved? PHP, Perl, ASP, etc?
Need more input! /johnny 5 voice off

Alodar · 08-08-2006, 11:59 PM

I can use A) SQL Server's Query Analyzer, or B) Access (with linked tables to the database).

The database is SQL Server though.

It basically needs to be one 'step' for a user.

Miron · 08-09-2006, 07:39 AM

Your problem (in the example) is the Type and Area field. How do you decide which Type and which Area to select for a given Ticket#? Do you really need to select them?

SELECT MAX(Type), Ticket, MAX(Area)
FROM Table
GROUP BY Ticket

Code:

Type     |Ticket#| Area
------------------------
Computer | 1b    | MN
Computer | 1c    | LA          <-- LA got selected before MN.
Computer | 1e    | MN
Computer | 1a    | LA
Laptop   | 2b    | TI
Laptop   | GE    |
Monitor  | 3g    | NV
Monitor  | 3p    | NV
Monitor  | 3m    | NV

Sakkath · 08-09-2006, 07:49 AM

I don't have time now but you should look up the HAVING keyword - e.g. HAVING COUNT(...)

Silverbrook · 08-09-2006, 08:58 AM

*scratches head* been a while since I used SQL, need to dust off brain.

Isn't there a "WHERE (fieldname) is UNIQUE" in some sql languages?

Also you said you could use MS Access?
Pull your table as a linked ODBC source, create a new Query, pop in your table into the Query. Drag your three fields to the rows on teh bottom (in query analizer view I think) Right cick on the ticket# collum, and should be a menu for unique or somethin to that affect. Change your view to run the query and see what you got.

Good thing about access is taht is lets you visually change your queries and run them very quickly. I would sometimes drag the tables into Access just to help map and visualize my DBA and what not. Also you can switch to "Code view" which lets you see the actuall SQL commands.

Giftmacher · 08-09-2006, 09:46 AM

Yeah Skakath could have something with the Having clause.

Thinking out loud again, maybe something like:

SELECT tablex.Type, tablex.Ticket#, tablex.Area FROM tablex HAVING COUNT(*) > 3 FROM Area WHERE ...;

The exact syntax to use for count still isn't quite right there though.

(Or alternatively use COUNT(*) AS Number with GROUP BY) I'll keep thinking about it.

However, I still think a seperate indexed field keeping count of the number of shared entries could save processing time. There's only so much you can realistically fit into one query.

Gift.

Alodar · 08-09-2006, 11:09 AM

Miron, the Area needs to be selected because there are different regions, each region holding a number of areas. For example, the west region holds 18 areas, and the South region holds like 12 or something, etc. I want ALL the types, but they have to be of the same. Additionally, there can be multiple Ticket#'s for each type/area. So

Code:

Type     |Ticket#| Area
------------------------
Computer | 1b    | MN
Computer | 1c    | LA          
Computer | 1e    | MN
Computer | 1b    | MN

could exist with two 1b Computer MN's.

Having is a great tool...as long as you're not worrying about having distinct. You can use Distinct as a line command, or as a count(distinct *) command (at least in SQL Server or Oracle, you can't use count(distinct *) in Access or anything like that). But that works only if you have something else to count off of. Having count is great for counting the TABLE...but not for counting the results of what you've pulled, so you can't say having count(type) > 2 and having count(distinct ticket#) > 2. Because distinct Ticket# in that instance will be how many distinct ticket#'s exist in quantities greater than two....which is none, because it's distinct. Can't use that in Access, and you can't use having count(distinct Ticket#) AS CDTicket# and then filter that, in a having expression.

Remember, these three fields are just the main. Still need to show RecordID, UserDate, Description, and EnterDate also.

So the select statement has to actually look something like

SELECT Area, Type, Ticket#, RecordID, UserDate, Description, and EnterDate

RecordID is a unique field, so grouping with it chosen is worthless, UserDate and enterdate fields are pretty much worthless also. See how much fun I'm having with this? :P

Azuin · 08-10-2006, 12:02 AM

Code:

Type     |Ticket#| Area
------------------------
Computer | 1b    | MN
Computer | 1c    | MN
Computer | 1c    | LA
Computer | 1e    | MN
Computer | 1a    | LA
Laptop   | GE    |
Laptop   | 2b    | TI
Laptop   | 2b    | TI
Laptop   | 1a    | UT
Monitor  | 3g    | NV
Monitor  | 3p    | NV
Monitor  | 3m    | NV

Code:

SELECT Type, TicketID, Area FROM
(
SELECT Type, TicketID, Area, COUNT(DISTINCT Area) AS count
FROM dbo.Orders
)
WHERE count >= 3
GROUP BY Type

I'm thinking something like this. It won't work if you copy and paste it, there'll probably be some error. Just fool around with it

The idea is to get a derived table which has the count of each area, and then select rows from the derived table which have a count of 3 or more.

Alodar · 08-10-2006, 12:35 AM

that works, EXCEPT if you add the other fields I need. Tried that, believe me :/

Giftmacher · 08-10-2006, 04:53 AM

Interesting...

Just to confirm these other fields, they are in the same table? And when you say it doesn't work do you get an error or do you kill the db?

Also could you post an example of the non-working query?

Gift.

Alodar · 08-10-2006, 01:19 PM

Well, I don't have a single example of a non-working query, because I've written like a dozen versions trying to get it to work.

Yes, the other fields are in the same table.

It doesn't work in that I get a lot of extra fields. Also, you can't say As MCount and then use that field in sql server as a field you can do stuff to....you have to use the equation again. (i.e. count(distinct area)) Which means that count(distinct area) will always return 1 for it, because you're counting distincts

Giftmacher · 08-10-2006, 02:33 PM

Quote:

Originally Posted by Alodar

count(distinct area) will always return 1 for it, because you're counting distincts

Yes I noticed that (I've been playing with a test set up), and I have to say if there is a solution it's not within my knowledge (sorry). I just can't seem to get it to return every record... The one solution I have left is the work around I suggested earlier; create a new field to keep track of replicate entries. E.g.:

Code:

Type     |Ticket#| Area| #Area
------------------------
Computer | 1b    | MN  | 3
Computer | 1c    | LA  | 1         
Computer | 1e    | MN  | 3
Computer | 1b    | MN  | 3

Sorry I'm not more use, but GL finding a solution (maybe someone else here will know a solution). If you do solve the problem I'd be interested to see the answer.

Gift.

Alodar · 08-10-2006, 03:20 PM

My best guess involved a self join, using the table to discern whether they were equal or not.

i.e.

SELECT t1.Area, t1.Type, t2.Ticket#, t1.RecordID, t1.UserDate, t1.Description, and t1.EnterDate
from Table T1, Table T2
where (T1.ticket# <> t2.ticket#
and t1.area = t2.area
and t1.type = t2.type)
and t1.area in ('SP', 'MN', 'LV')
group by t1.Area, t1.Type, t2.Ticket#, t1.RecordID, t1.UserDate, t1.Description, and t1.EnterDate
having count(t1.type) > 2

---------------
I DID finish it, but only by automation.

I used an OLEDB call to run a query that got pretty much everything, and then it placed the data into excel and then I parsed it with that. So it's basically just a button in Excel for the user to push.

I'm sort of thinking this isn't doable otherwise. I posted on a bunch of SQL sites, and no one had an answer on any of those either.