Re: [Q] SQL question
In article <47m4nt$n7s@tin.monsanto.com> Saqib Mausoof <ssmaus@musctn.monsanto.com> writes:
>From: Saqib Mausoof <ssmaus@musctn.monsanto.com>
>Subject: [Q] SQL question
>Date: 6 Nov 1995 23:11:57 GMT
>Is there a way for me to get a select statement to bring fields from two
>tables by a common select criteria?
>e.g
>Select lname, fname, status
>from contractors, visitors
>where status ="IN"
>I know this does not work. It gives me a cartesian product. I have been
>exploring different methods but I don't get the required result. The two
>tables have the same for the columns, and the criteria, which is STATUS
>="IN" is common to both. I just want to combine the info from the two
>tables so that I can report it.
I assume that what you want are people who are in both tables with status =
"IN".
Firstly you must qualify each column name by the table name so you will
actually need to say:
Select
contractors.lname,
visitors.lname,
contractors.fname,
visitors.fname
from
contractors,
visitors
where
contractors.status ="IN" and
visitors.status = "IN"
I have omitted selecting status because it's redundant. This is now
acceptable because the names are properly qualified but we have the Cartesian
product you referred to. This is because you haven't specified what the
relationship is between the tables so the system will pair each set of
visitors names with each pair of contractors names. To do this you must add
extra terms to the where clause:
Select
contractors.lname,
visitors.lname,
contractors.fname,
visitors.fname
from
contractors,
visitors
where
contractors.status ="IN" and
visitors.status = "IN" and
contractors.lname = visitors.lname and
contractors.fname = visitors.fname
Finally, you have redundant pairs of names in the output so you could reduce
it to:
Select
contractors.lname,
contractors.fname
from
contractors,
visitors
where
contractors.status ="IN" and
visitors.status = "IN" and
contractors.lname = visitors.lname and
contractors.fname = visitors.fname
Ian