[thelist] SQL: looking for resources, have one question

Chris W. Parker cparker at swatgear.com
Tue Oct 22 11:08:01 CDT 2002


> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
>
> chris, those two subqueries in your union query do look like they are
> unrelated
>
> could you please say how the columns in all three tables relate?

this goes back to the other thread that you were helping me with a few
days ago. (SQL: COUNTing and SUMming and whatnot). the difference this
time is that i'm not trying to do any COUNTing or SUMming.

i have one table (productTracking) that contains the answers to a survey
our sales people ask customers when talking on the phone.

SQL = "SELECT a.ppName " _
	& "FROM PossibleProducts a " _
		& "INNER JOIN productTracking b " _
		& "ON b.ptProduct = a.id " _
& "UNION " _
	& "SELECT c.paName "_
		& "FROM PossibleAdverts c " _
			& "INNER JOIN productTracking b " _
			& "ON b.ptReferralSource = c.id"

maybe it would help to show you a query that gives me the results i'm
looking for but is not one i'd like to use. (why? well it's too simple
and it doesn't give me practice using JOINs.)

SELECT
  (SELECT ppName
	FROM PossibleProducts
	WHERE id = ptProduct),
  (SELECT paName
	FROM PossibleAdverts
	WHERE id = ptReferralSource),
  ptComments, ptUsername, ptDate
  FROM productTracking"

this gives me...

name of product | advertisement | comments | username | date

i'd like to use a JOIN statement to do this instead of two sub SELECTs.
i thought using a UNION in conjuction with two JOIN statements would do
the trick.


am i being clear?


chris.



More information about the thelist mailing list