Last post Aug 13, 2009 11:15 PM by Drackie
Aug 13, 2009 07:37 PM|Drackie|LINK
Ok, let me start by saying I can't do complex SQL queries :P I am using Access to generate my queries!
Here is what I am stuck with:
I have 6 tables which need joining, I'll only list the keys to be linked within the tables so as to simplify (I can do the other bit!)
The issue is this. Access generates:
SELECT Incident.IncidentID, Incident.DateAndTime, Incident.UserID, Usertable.UserName, Incident.PrinterID, Incident.CartridgeID, Incident.CartridgeBought, Incident.CartridgeQty, [Make].[MakeName] & ' ' & [Cartridge].[CartridgeNumber] AS CartridgeNumber1, [Printer].[PrinterName] & [Printer].[PrinterModel] & [Printer].[Location] & [Make].[MakeName] & [Type].[TypeName] AS PrinterName1
FROM Type INNER JOIN (Make INNER JOIN ((Usertable INNER JOIN (Cartridge INNER JOIN Incident ON Cartridge.CartridgeID = Incident.CartridgeID) ON Usertable.UserID = Incident.UserID) INNER JOIN Printer ON Incident.PrinterID = Printer.PrinterID) ON (Make.MakeID = Printer.MakeID) AND (Make.MakeID = Cartridge.MakeID)) ON (Type.TypeID = Printer.TypeID) AND (Type.TypeID = Cartridge.TypeID)
This means that
Printer.TypeID MUST equal Cartridge.TypeID
Printer.MakeID MUST equal Cartridge.MakeID
This is not true of my data, I think the answer is probably a LEFT or RIGHT JOIN but I can't seem to work out how to put that into this query as I am confused by the nesting.
Aug 13, 2009 08:21 PM|SGWellens|LINK
Use the Query Designer in Access. You just drag and drop the linking fields from/to each table.
When finished, switch to SQL view and the SQL will be there for you to copy and paste into your program.
Aug 13, 2009 08:24 PM|Drackie|LINK
I did that but thre is an issue with the query as stated above.
The Printer.TypeID is not always the same as the Cartridge.TypeID but values are pulled from the same table.
The same is true of the make.
Aug 13, 2009 09:36 PM|Drackie|LINK
ok, I now have this:
SELECT Printer.PrinterID, Printer.PrinterName & Printer.PrinterModel & Printer.Location & Make.MakeName & Type.TypeName AS PrinterName1 FROM Type RIGHT JOIN (Make RIGHT JOIN Printer ON Make.MakeID = Printer.MakeID) ON Type.TypeID = Printer.TypeID
How do I join the two in .NET? I can't concatenate because of the joins :s
Aug 13, 2009 09:45 PM|krishnanellutla|LINK
You can try joining above two queris using datasets, Bind those two queries in each dataset and then merge them using key columns
Aug 13, 2009 10:00 PM|Drackie|LINK
I tried combining with a WITH clause but the query is too long even without the WITH and with shortened names
SELECT MakeName & ' ' & CartridgeNumber AS CN1, IncidentID, DateAndTime, I.UserID, I.PrinterID, I.CartridgeID, CartridgeBought, CartridgeQty, PQ.PrinterName1 FROM PQ RIGHT JOIN (Usertable U INNER JOIN (Type T RIGHT JOIN (Make M RIGHT JOIN (Cartridge C INNER
JOIN Incident I ON C.CartridgeID = I.CartridgeID) ON M.MakeID = C.MakeID) ON T.TypeID = C.TypeID) ON U.UserID = I.UserID) ON PQ.PrinterID = I.PrinterID WITH PQ AS (SELECT PrinterID, PrinterName & PrinterModel & Location & Make!MakeName & Type!TypeName AS PN1
FROM Type T RIGHT JOIN (Make M RIGHT JOIN Printer P ON M.MakeID = P.MakeID) ON T.TypeID = P.TypeID;)
is my current statement
Aug 13, 2009 11:15 PM|Drackie|LINK
I'm an idiot but then we knew that!
I've put the 2nd query in Access and it calls it just fine...
Maybe pulling an all nighter for this wasn't such a good idea