Paul, It appears that your database design is not normalized. Once it is normalized, you should be able to extract any information you want, just about any way you want it.
If you will step away from the db design perspective for a moment and explain what it is you are trying to accomplish in real world terms, I will try to help. Dina ----- Original Message ----- From: Paul Giesenhagen To: SQL Sent: Friday, April 05, 2002 10:37 AM Subject: SQL Multiple tables query This is a multi-part message in MIME format. ------=_NextPart_000_018E_01C1DC8D.D25686F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have been using a different database layout for years now .. and ready = to go back to the database layout that many of you use.. So I am almost = relearning how to do certain tasks .. Here is what I have and what I need to do.. relationship table id,owner,subobject,type, sort content table id,title,state... department table id,title,state... product table id,title,state... ID's and SubObjects in the relationship table are from the content, = department and product tables .. these id's can be in either column in = the relationship table. so I have=20 department id's ID SubObject Type A B Department A D Content B A Department B C Product B F Content C G Department Now If I want to show all types of SUBOBJECTS associated with ID B in = the relationship table which would be A,C,F and group them by their Type = and then order by the sort column, can this be done? Or do I have to write three queries using the where type =3D = 'department' and then another query where type=3D'content' and a third = query where type=3D'product'? Can I combine and then extract the groupings? so I can say Departments = (output output output) Content (output output output) Products (output = output output). Thanks in advance Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector v2.0 - Commerce Builder ------=_NextPart_000_018E_01C1DC8D.D25686F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>I have been using a different database = layout for=20 years now .. and ready to go back to the database layout that many of = you=20 use.. So I am almost relearning how to do certain tasks = ..</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Here is what I have and what I need to=20 do..</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>relationship table</FONT></DIV> <DIV><FONT face=3DArial size=3D2>id,owner,subobject,type, = sort</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>content table</FONT></DIV> <DIV><FONT face=3DArial size=3D2>id,title,state...</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>department table</FONT></DIV> <DIV><FONT face=3DArial size=3D2>id,title,state...</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>product table</FONT></DIV> <DIV><FONT face=3DArial size=3D2>id,title,state...</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>ID's and SubObjects in the relationship = table are=20 from the content, department and product tables .. these id's can be in = either=20 column in the relationship table.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>so I have </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>department id's</FONT></DIV> <DIV><FONT face=3DArial size=3D2>ID = SubObject =20 Type</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>A B &n= bsp; &nb= sp;=20 Department</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>A D &n= bsp; &nb= sp;=20 Content</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>B A &n= bsp; &nb= sp;=20 Department</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>B C &n= bsp; =20 Product</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>B F &n= bsp; =20 Content</FONT></DIV> <DIV><FONT face=3DArial = size=3D2>C =20 G = =20 Department</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Now If I want to show all types of = SUBOBJECTS=20 associated with ID B in the relationship table which would be A,C,F = and=20 group them by their Type and then order by the sort column, can this be=20 done?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Or do I have to write three queries = using the where=20 type =3D 'department' and then another query where = type=3D'content' and a=20 third query where type=3D'product'?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Can I combine and then extract the = groupings? so I=20 can say Departments (output output output) Content (output output = output)=20 Products (output output output).</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks in advance</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Paul Giesenhagen</FONT></DIV> <DIV><FONT face=3DArial size=3D2>QuillDesign</FONT></DIV> <DIV><FONT face=3DArial size=3D2><A=20 href=3D"http://www.quilldesign.com";>http://www.quilldesign.com</A></FONT>= </DIV> <DIV><FONT face=3DArial size=3D2>SiteDirector v2.0 - Commerce = Builder</FONT></DIV> <DIV><FONT face=3DArial size=3D2> </DIV> <DIV><BR></DIV></FONT> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2> </DIV></FONT></BODY></HTML> ------=_NextPart_000_018E_01C1DC8D.D25686F0-- Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
