Creating queries for one-to-many tables

[m.k.ball@btinternet.com]

I have three tables, containing information about three people and the
cars that they own.
One table lists people, one lists cars, and one uses the id's from the
other two to compile a list of which models of car each person owns.
Bob owns a Ford, Paul owns a Toyota, and Dave owns both a Ford and a
Toyota.

Simple.

How do I create a query that will tell me 'which people own a Ford and
do not own a Toyota' - i.e. how do you get a result of just Bob?

table: person
fields: person_id, person_name
data: 1 - Bob, 2 - Paul, 3 - Dave

table: car
fields: car_id, car_name
data: 1 - Ford, 2 - Toyota

table: person_car
fields: person_id, car_id
data: 1 - 1, 2 - 2, 3 - 1, 3 - 2

[Nandakishore]

Re: Creating queries for one-to-many tables

This lists which people own a only a Ford,
SELECT P.person_name FROM person P,car C, person_car PC
WHERE PC. person_id=P.per son_id and PC.car_id=C.car _id and
C.car_name='For d';

[Nandakishore]

Re: Creating queries for one-to-many tables

This lists which people own a only a Ford,

SELECT P.person_name,c ount(PC.car_id) MyNo FROM person P,car C,
person_car PC
WHERE PC. person_id=P.per son_id and PC.car_id=C.car _id and
C.car_name='For d' and MyNo=1

[replay]

Re: Creating queries for one-to-many tables

m.k.ball@btinte rnet.com wrote:[color=blue]
> I have three tables, containing information about three people and the
> cars that they own.
> One table lists people, one lists cars, and one uses the id's from the
> other two to compile a list of which models of car each person owns.
> Bob owns a Ford, Paul owns a Toyota, and Dave owns both a Ford and a
> Toyota.
>
> Simple.
>
> How do I create a query that will tell me 'which people own a Ford and
> do not own a Toyota' - i.e. how do you get a result of just Bob?
>
> table: person
> fields: person_id, person_name
> data: 1 - Bob, 2 - Paul, 3 - Dave
>
> table: car
> fields: car_id, car_name
> data: 1 - Ford, 2 - Toyota
>
> table: person_car
> fields: person_id, car_id
> data: 1 - 1, 2 - 2, 3 - 1, 3 - 2
>[/color]

select person.person_n ame from person inner join person_car on
person.person_i d = person_car.pers on_id inner join car on car.car_id =
person_car.car_ id where car.car_name='f ord' and car.car_name!=' toyota';

--

visit: http://www.h4xx0r.ch

[Rich Ryan]

Re: Creating queries for one-to-many tables

Did the other solutions work? They don't.. Here's how I would do it:

SELECT p.Person_Name
FROM PERSON p
INNER JOIN
(SELECT pc.Person_ID, pc.Car_ID,c.Car _Name
FROM PERSON_CAR pc
INNER JOIN CAR c
ON c.Car_ID = pc.Car_ID
WHERE c.Car_Name = 'FORD') ford
ON ford.Person_ID = p.Person_ID
LEFT JOIN
(SELECT pc.Person_ID, pc.car_ID,c.Car _Name
FROM PERSON_CAR pc
INNER JOIN CAR c
ON c.Car_ID = pc.Car_ID
WHERE c.Car_Name = 'Toyota') toyota
ON toyota.Person_I D = p.Person_ID
WHERE toyota.Car_ID IS NULL
<m.k.ball@btint ernet.com> wrote in message
news:1134355490 .559802.11510@g 14g2000cwa.goog legroups.com...[color=blue]
> I have three tables, containing information about three people and the
> cars that they own.
> One table lists people, one lists cars, and one uses the id's from the
> other two to compile a list of which models of car each person owns.
> Bob owns a Ford, Paul owns a Toyota, and Dave owns both a Ford and a
> Toyota.
>
> Simple.
>
> How do I create a query that will tell me 'which people own a Ford and
> do not own a Toyota' - i.e. how do you get a result of just Bob?
>
> table: person
> fields: person_id, person_name
> data: 1 - Bob, 2 - Paul, 3 - Dave
>
> table: car
> fields: car_id, car_name
> data: 1 - Ford, 2 - Toyota
>
> table: person_car
> fields: person_id, car_id
> data: 1 - 1, 2 - 2, 3 - 1, 3 - 2
>[/color]

[Bill Karwin]

Re: Creating queries for one-to-many tables

"Rich Ryan" <ryanrj@sbcglob al.net> wrote in message
news:9ynnf.507$ kW.427@newssvr3 0.news.prodigy. com...[color=blue]
> Did the other solutions work? They don't.. Here's how I would do it:[/color]

I agree, the other solutions proposed fail in certain cases.

Here's another solution similar to Rich's:



SELECT DISTINCT p.*

FROM person AS p

INNER JOIN person_car AS pc ON p.person_id = pc.person_id

INNER JOIN car AS c ON pc.car_id = c.car_id AND c.car_name = 'Ford'

LEFT OUTER JOIN person_car AS pc2 ON p.person_id = pc2.person_id

INNER JOIN car AS c2 on pc2.car_id = c2.car_id AND c2.car_name = 'Toyota'

WHERE c2.car_id IS NULL;



Regards,

Bill K.

[m.k.ball@btinternet.com]

Re: Creating queries for one-to-many tables

This is the nested query solution that I originally came up with. I'm
concerned that using a nested query would mean more work for the
database, so that's my reason for wanting a solution that just uses
joins. My knowledge of such things is limited - am I right to assume
that a nested query will automatically use more resources to process
than a regular non-nested query?

Michael


SELECT p.person_name
FROM person p, person_car pc
WHERE pc.car_id =1
AND p.person_id = pc.person_id
AND pc.person_id NOT
IN (
SELECT pc.person_id
FROM person_car pc
WHERE pc.car_id =2
)





CREATE TABLE `car` (
`car_id` mediumint(9) NOT NULL auto_increment,
`car_name` varchar(20) NOT NULL default '',
PRIMARY KEY (`car_id`)
);

INSERT INTO `car` VALUES (1, 'Ford');
INSERT INTO `car` VALUES (2, 'Toyota');

CREATE TABLE `person` (
`person_id` mediumint(9) NOT NULL auto_increment,
`person_name` varchar(20) NOT NULL default '',
PRIMARY KEY (`person_id`)
);

INSERT INTO `person` VALUES (1, 'Bob');
INSERT INTO `person` VALUES (2, 'Paul');
INSERT INTO `person` VALUES (3, 'Dave');

CREATE TABLE `person_car` (
`pc_id` mediumint(9) NOT NULL auto_increment,
`person_id` mediumint(9) NOT NULL default '0',
`car_id` mediumint(9) NOT NULL default '0',
PRIMARY KEY (`pc_id`)
);


INSERT INTO `person_car` VALUES (1, 1, 1);
INSERT INTO `person_car` VALUES (2, 2, 2);
INSERT INTO `person_car` VALUES (3, 3, 1);
INSERT INTO `person_car` VALUES (4, 3, 2);

[Rich Ryan]

Re: Creating queries for one-to-many tables

Does the solution run too slow? One way or another you're going to need
tables that contain ownership information. There is no other way to solve
this problem. So you can create intermediate tables or just do the same
thing in your SELECT.

My approach is to write a query in the "most SQL" way, so that others can
understand what's going on. If performance becomes an issue, then I do an
"EXPLAIN" to see just what's going on.

Remember, SQL is a set-based (really bag-based) language, so you want to use
it properly.

I'd like to see your original "nested query"

Rich


<m.k.ball@btint ernet.com> wrote in message
news:1134531021 .786853.162300@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> This is the nested query solution that I originally came up with. I'm
> concerned that using a nested query would mean more work for the
> database, so that's my reason for wanting a solution that just uses
> joins. My knowledge of such things is limited - am I right to assume
> that a nested query will automatically use more resources to process
> than a regular non-nested query?
>
> Michael
>
>
> SELECT p.person_name
> FROM person p, person_car pc
> WHERE pc.car_id =1
> AND p.person_id = pc.person_id
> AND pc.person_id NOT
> IN (
> SELECT pc.person_id
> FROM person_car pc
> WHERE pc.car_id =2
> )
>
>
>
>
>
> CREATE TABLE `car` (
> `car_id` mediumint(9) NOT NULL auto_increment,
> `car_name` varchar(20) NOT NULL default '',
> PRIMARY KEY (`car_id`)
> );
>
> INSERT INTO `car` VALUES (1, 'Ford');
> INSERT INTO `car` VALUES (2, 'Toyota');
>
> CREATE TABLE `person` (
> `person_id` mediumint(9) NOT NULL auto_increment,
> `person_name` varchar(20) NOT NULL default '',
> PRIMARY KEY (`person_id`)
> );
>
> INSERT INTO `person` VALUES (1, 'Bob');
> INSERT INTO `person` VALUES (2, 'Paul');
> INSERT INTO `person` VALUES (3, 'Dave');
>
> CREATE TABLE `person_car` (
> `pc_id` mediumint(9) NOT NULL auto_increment,
> `person_id` mediumint(9) NOT NULL default '0',
> `car_id` mediumint(9) NOT NULL default '0',
> PRIMARY KEY (`pc_id`)
> );
>
>
> INSERT INTO `person_car` VALUES (1, 1, 1);
> INSERT INTO `person_car` VALUES (2, 2, 2);
> INSERT INTO `person_car` VALUES (3, 3, 1);
> INSERT INTO `person_car` VALUES (4, 3, 2);
>[/color]

[Rich Ryan]

Re: Creating queries for one-to-many tables

Sorry, you did post your solution. Just didn't scroll down.

Rich
<m.k.ball@btint ernet.com> wrote in message
news:1134531021 .786853.162300@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> This is the nested query solution that I originally came up with. I'm
> concerned that using a nested query would mean more work for the
> database, so that's my reason for wanting a solution that just uses
> joins. My knowledge of such things is limited - am I right to assume
> that a nested query will automatically use more resources to process
> than a regular non-nested query?
>
> Michael
>
>
> SELECT p.person_name
> FROM person p, person_car pc
> WHERE pc.car_id =1
> AND p.person_id = pc.person_id
> AND pc.person_id NOT
> IN (
> SELECT pc.person_id
> FROM person_car pc
> WHERE pc.car_id =2
> )
>
>
>
>
>
> CREATE TABLE `car` (
> `car_id` mediumint(9) NOT NULL auto_increment,
> `car_name` varchar(20) NOT NULL default '',
> PRIMARY KEY (`car_id`)
> );
>
> INSERT INTO `car` VALUES (1, 'Ford');
> INSERT INTO `car` VALUES (2, 'Toyota');
>
> CREATE TABLE `person` (
> `person_id` mediumint(9) NOT NULL auto_increment,
> `person_name` varchar(20) NOT NULL default '',
> PRIMARY KEY (`person_id`)
> );
>
> INSERT INTO `person` VALUES (1, 'Bob');
> INSERT INTO `person` VALUES (2, 'Paul');
> INSERT INTO `person` VALUES (3, 'Dave');
>
> CREATE TABLE `person_car` (
> `pc_id` mediumint(9) NOT NULL auto_increment,
> `person_id` mediumint(9) NOT NULL default '0',
> `car_id` mediumint(9) NOT NULL default '0',
> PRIMARY KEY (`pc_id`)
> );
>
>
> INSERT INTO `person_car` VALUES (1, 1, 1);
> INSERT INTO `person_car` VALUES (2, 2, 2);
> INSERT INTO `person_car` VALUES (3, 3, 1);
> INSERT INTO `person_car` VALUES (4, 3, 2);
>[/color]

[Bill Karwin]

Re: Creating queries for one-to-many tables

<m.k.ball@btint ernet.com> wrote in message
news:1134531021 .786853.162300@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> This is the nested query solution that I originally came up with. I'm
> concerned that using a nested query would mean more work for the
> database, so that's my reason for wanting a solution that just uses
> joins. My knowledge of such things is limited - am I right to assume
> that a nested query will automatically use more resources to process
> than a regular non-nested query?[/color]

Not necessarily. In this case, the subquery is invariant with respect to
the rows of the outer query. The MySQL optimizer is smart enough (and this
is true for any reasonable RDBMS product) to perform an invariant subquery
once, and use the results as though it were a list of constant values, to
evaluate all the rows of the outer query.

I predict that the performance difference, whether positive or negative,
between this and getting the same results with a join, will be negligible.

But as always in these sorts of issues, both your assumption and mine need
to be measured for us to be sure. Do both types of queries, and time them.
Your particular database schema and data content, MySQL version, and even
your server host can potentially affect the results to some degree, so
there's no substitute for running tests with your data.

Regards,
Bill K.

[m.k.ball@btinternet.com]

Re: Creating queries for one-to-many tables

Bill and Rich - thank you both for your advice - it is greatly
appreciated.
Sometimes when you're struggling with some arcane piece of code, a word
from someone
who understands the subject can make all the difference.
Kind Regards,

Michael