Simple Select Query Problem 1

[digimortal]

I'm trying to run a select query on 5 tables but it seems my code is not efficient enough please help... BTW it gives me a timeout error. I don't know but maybe the problem is that there are 2500 records on my card table...

SELECT     CARD.LNAME, CARD.MNAME, CARD.CNUM, CARD.CNAME, (CASE WHEN Card.GNUM = C_GRP.NUM THEN C_GRP.NAME ELSE NULL END) AS Departman,
(CASE WHEN AGRECS.AGNUM = CARD.ACGRP THEN AGRECS.AGNAME ELSE NULL END) AS AGN, 
(CASE WHEN PersonelWG.WGID = WorkGroups.WGID THEN WorkGroups.WGName ELSE NULL END) AS WG
FROM         CARD CROSS JOIN
                      C_GRP CROSS JOIN
                      AGRECS CROSS JOIN
                      WorkGroups CROSS JOIN
                      PersonelWG

 

[Catadmin]

A Cross Join???? GAH! Is that necessary?

Cross Joins are BAD. It's no wonder you're getting timed out. It's trying to pull all records from all five tables.

What exactly is it that you need from this query? Write it in English in your reply and include any conditionals or "must match" thoughts in that. Then we can re-write your code for you to help avoid the time-out problem.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[digimortal]

There are five tables; CARD, AGRECS, PersonelWG, WorkGroups, C_Grp

I have a DataGrid like this

CARD.LNAME | CARD.MNAME | CARD.CNUM | CARD.CNAME | Deparment | Access Group | Workgroup

if there are no Department, Access Group or WorkGroup then these fields will be empty and as you can see from the query I'm trying to get their names from corresponding table by the IDs I get from the card table. But there is one different condition:

I have two tables called PersonelWG and WorkGroups :
PersonelWG
CNUM (CardNumber) | WGID (WorkGroupID)
WorkGroups
WGID (WorkGroupID) | WGName (WorkGroupName)

First I read a card number from card table and check if it exists in PersonelWG table's CNum field if so get the WGID and bind it with the name form WorkGroups Table. If the card number does not exist in PersonelWG table then the Workgroup field will be left blank on the DataGrid...

BTW I "must" to use one DataGrid... for five tables... huh

 

[Catadmin]

I have a DataGrid like this

CARD.LNAME | CARD.MNAME | CARD.CNUM | CARD.CNAME | Deparment | Access Group | Workgroup

So you're saying the last three columns are in the Card table, but the values can be NULL and need to be pulled from other tables, right?


Let me do a little working and see if I can come up with something.





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[digimortal]

There are there fields that holds the IDs of Department, Acc. Group and Workgroups in the Card table and I use them to get the Department, Acc. Group, WorkGroup names from other tables
and if a DeparmentID or AccID or WorkgroupID is NULL then there will be a Null "NAME" field on the DBGrid..

I did not know my english is that bad

 

[Catadmin]

Okay, give this a shot. Let me know if you don't understand what I did with the code.

SELECT C.LNAME, C.MNAME, C.CNUM, C.CNAME, Case When CG.GName Is Not Null Then CG.Name Else Null End as Department, Case When A.AgName Is Not Null Then A.Agname Else Null End as AGN, Case When WG.WGName Is Not Null Then WG.WGName Else Null End as WG

From Card C left outer join C_Grp CG on C.Gnum = CG.Gnum
left outer join Agrecs A on C.Acgrp = A.Agnum
left outer join PersonelWG P on C.Cnum = P.Cnum 
left outer join WorkGroups WG on P.WGID = WG.WGID

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[digimortal]

Mate I don't know how to thank you but.. THANK YOU..
Now It works like a charm..

 

[Catadmin]

Glad I could help. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???