MySQL query

[bdistler]

Need to make a query for:

database with 3 tabels

#1 `shipTo` has key "shipID" and column "address"

#2 `cust` has key "custID" and fk "shipTo.shipID"

#3 `parts` has key "partID" and fk "cust.custID"

list shipto.address
where [any]cust.shipID = shipTo.shipID WITH [any]parts.custID = cust.custID

or say it this way

if any group of `cust` have `parts` throw shipTo.address for that group

output S/B <= number of `shipTo` rows

Thanks

 

[diabolo]

are you using PDO to handle your db queries?
I ask because I would have to tweak the query if you did.

 

[bdistler]

are you using PDO to handle your db queries?
I ask because I would have to tweak the query if you did.

Doing debug in phpMyAdmin

work will be in PHP/MySQL (no PDO)

 

[misson]

There are a few point which are unclear. What do you mean by "group", since nothing else suggests the need for grouping? What is "S/B"?

If I understand the requirements, a (natural) inner join is what you need:

SELECT shipTo.address
    FROM parts
    JOIN cust ON parts.custID = cust.custID
    JOIN shipTo ON shipTo.shipID = cust.shipID

or

SELECT shipTo.address
    FROM parts
    NATURAL JOIN cust
    NATURAL JOIN shipTo

What's the full schema (CREATE statements) for these tables? There are a few aspects (such as parts referring to customers) that make me suspect the design is flawed.

If by "no PDO" you mean you're using the outdated mysql driver, don't. The only reason it's still around is to support legacy code.

 

[bdistler]

There are a few point which are unclear. What do you mean by "group", since nothing else suggests the need for grouping? What is "S/B"?

If I understand the requirements, a (natural) inner join is what you need:

SELECT shipTo.address
    FROM parts
    JOIN cust ON parts.custID = cust.custID
    JOIN shipTo ON shipTo.shipID = cust.shipID

or

SELECT shipTo.address
    FROM parts
    NATURAL JOIN cust
    NATURAL JOIN shipTo

What's the full schema (CREATE statements) for these tables? There are a few aspects (such as parts referring to customers) that make me suspect the design is flawed.

If by "no PDO" you mean you're using the outdated mysql driver, don't. The only reason it's still around is to support legacy code.

fk = foreign key

group as cust.custID = 1, cust.custID = 5, cust.custID = 9 All have the same
fk cust.shipID = 7
which would throw one row from shipTo (where shipTo.shipID = 7) to the output
I was saying grouping as a "group of" not as in MySQL

S/B = should be

output can be less then or the same as the number of rows in `shipTo`

I should have named "parts" as "partOrder"

it is the other way-around customers can have many "partOrder"s

PDO = PHP Data Objects

I made this small database outline to work on the 'query'
for a large database

Thanks

 

[misson]

"fk" is a fairly standard abbreviation, and "PDO" is more standard than the full name; you don't need to expand on those.

group as cust.custID = 1, cust.custID = 5, cust.custID = 9 All have the same
fk cust.shipID = 7
which would throw one row from shipTo (where shipTo.shipID = 7) to the output
I was saying grouping as a "group of" not as in MySQL
[..]
it is the other way-around customers can have many "partOrder"s

So (cust.shipID, shipTo.shipID) is a many-to-one relationship, as is (partOrder.custID, cust.custID). That would have been useful information to have from the start.

From that, it seems you do want SQL grouping. Add a "GROUP BY shipTo.shipID" clause to each of the previous queries. Note that this only works with MySQL, since shipTo.address isn't aggregated. It also assumes that shipTo.address is functionally dependent on shipTo.shipID, which should be the case, since shipTo.shipID should be a superkey.

I should have named "parts" as "partOrder"

A couple of the table and column names could use improvement.