SQL join

[DirtyHairy]

Hello,

I am trying to run this on SQL Server 2000. I have 5 tables set up with these fields, in addition to others:

[tt]table ug
--------
ugid


table u
-------
uid
fname
lname


table s
-------
uid
cid
total_score
completed
counter


table uga
---------
ugid
uid


table ugl
---------
ugid
uid[/tt]



What I need in the recordset is ug.ugid, u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, a field indicating if the ugid/uid combo is in the uga table, and a field indicating if the ugid/uid combo is in the ugl table. The constraints are that I need the entire list of ugids (possibly indicating an outer join), and the s.cid will be a constant for all rows (where s.cid='cid'). I also need the recordset ordered by ugid. There might be more then one row for each ugid, so it will need to be ordered by ugid. The only rows I need returned are ones where the ugid/uid combo appears in either the uga or ugl table, and I also need to know which it appears in.

I can do this with 2 queries, I can get the list of ugids first and then the information for each one, but with the size of the data I am working with, that report takes around 30 minutes to complete. So, it's not exactly the best choice.

Does anyone know how to get all of the information with 1 query, or would it be better to rethink the way the database is set up in the first place? There are certain constraints on the database that make a complete redesign impossible, but I would probably be able to redo the way I am storing the ugl information.

Right now this is how I have the code set up, which is written in ASP/VBscript:

ugcon.open("SELECT ugid FROM usergroups ORDER BY ugid ASC")

...

  for i = LBound(uglist) to UBound(uglist)
    if ereg(uglist(i), "[A-Z]{2}[0-9]{3}", true) = true then 

      s = "SELECT u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, 'Y' AS ugadmin FROM "
      s = s & "(users u LEFT OUTER JOIN content_session s ON "
      s = s & "u.uid=s.uid AND s.cid='BOB-MAN') "
      s = s & "WHERE u.uid IN (SELECT uid FROM usergroup_admins WHERE ugid='" & uglist(i) & "') "
      s = s & "UNION "
      s = s & "SELECT u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, ' ' AS ugadmin FROM "
      s = s & "(users u LEFT OUTER JOIN content_session s ON "
      s = s & "u.uid=s.uid AND s.cid='BOB-MAN') "
      s = s & "WHERE u.uid NOT IN (SELECT uid FROM usergroup_admins WHERE ugid='" & uglist(i) & "') AND u.uid IN (SELECT uid FROM user_grouplist WHERE ugid='" & uglist(i) & "')"

      usercon.open(s)

...

Obviously, having the query inside the loop is destroying efficiency.

 

[dnoeth]

This should be close:

SELECT
...
CASE WHEN uga.ugid IS NOT NULL THEN 'Y' ELSE 'N' END,
CASE WHEN ugl.ugid IS NOT NULL THEN 'Y' ELSE 'N' END
FROM ug LEFT JOIN u ON ...
(LEFT?) JOIN s ON ...
LEFT JOIN uga ON ug.ugid = ugl.ugid AND u.uid = uga.uid
LEFT JOIN ugl ON ug.ugid = ugl.ugid AND u.uid = ugl.uid
WHERE ...

Dieter

 

[DirtyHairy]

Thanks for the reply. After staring at it for a while I actually just built some temporary tables and used a series of simple queries to put the data into a table, and then selected everything from that final table to display. The report time is now about 11 seconds, 4 or 5 of which are the queries, so it's manageable now.

DELETE FROM report_bob_table1

INSERT INTO report_bob_table1 SELECT ugid, uid, ' ' AS ugadmin FROM user_grouplist

DELETE FROM report_bob_table1 WHERE EXISTS
(SELECT * FROM usergroup_admins WHERE usergroup_admins.uid=report_bob_table1.uid AND usergroup_admins.ugid=report_bob_table1.ugid)

INSERT INTO report_bob_table1 SELECT ugid, uid, 'Y' AS ugadmin FROM usergroup_admins

DELETE FROM report_bob_table2

INSERT INTO report_bob_table2
SELECT r.ugid, r.uid, r.ugadmin, u.fname, u.lname, s.total_score, s.completed, s.counter
FROM report_bob_table1 AS r
INNER JOIN users AS u ON r.uid=u.uid
LEFT OUTER JOIN content_session AS s ON s.uid=r.uid AND s.cid='BOB-MAN'

INSERT INTO report_bob_table2 SELECT ugid, null, null, null, null, null, null, null FROM usergroups WHERE ugid NOT IN (SELECT ugid FROM report_bob_table2)

SELECT * FROM report_bob_table2 ORDER BY ugid, uid