CODEWITHSUNDEEP
phpjoinmysqli
bhamner
bhamner

Reputation: 447

Whats wrong with my join?

simplified db setup

table:clients fields: clientId(autoinc/primary), customerId, clientName

table:projects fields: projectId(autoinc/primary), customerId, projectName

table:items fields: itemId(autoinc/primary), customerId, itemName

query:

include('includes/conn.inc.php'); 
$query = "SELECT customerId 
         FROM items, projects, clients 
         WHERE customerId= 135";
$stmt = $mysql->prepare ($query);
$stmt->execute();
$stmt->bind_result($customerId);

while($row = $stmt->fetch()) : 
      echo $customerId;
endwhile; $stmt->close();

question: whats wrong with the query? Be gentle, Im trying to figure out joins for the first time. Ive tried a bunch of different ways but cant get any of them to work, this one seemed the simplest and most explanatory for what i want to do. There is an entry on each table that corresponds (customerId=135) so if the query works i would expect it to return 3 of them. error: calling execute on a non-object

Thanks to everyone in advance

------- update

Thanks everyone for your help! I actually got it to work by using this query:

        SELECT clients.customerId, projects.customerId, items.customerId 
        FROM clients, projects, items 
        WHERE  clients.customerId = projects.customerId AND   
        projects.customerId = items.customerId;

although that kinda seems clunky especially in the end. If anyone knows a shorthand for "WHERE table.column = table.column = table column that would be excellent considering they are all 3 the same value.

Upvotes: 1

Views: 77

Answers (4)

bhamner
bhamner

Reputation: 447

    SELECT clients.customerId, projects.customerId, items.customerId 
    FROM clients, projects, items 
    WHERE  clients.customerId = projects.customerId AND   
    projects.customerId = items.customerId;

Upvotes: 0

Farnabaz
Farnabaz

Reputation: 4066

you should specify join condition on your tables, like this 

SELECT customerId 
     FROM items i, projects p, clients c
     WHERE i.customerId = p.customerId AND p.customerId = c.customerId 
        AND customerId= 135

above merge rows from tables that have equal customerId

Upvotes: 0

shortstuffsushi
shortstuffsushi

Reputation: 2330

I might be missing something, but you're not explicitly joining anywhere in your query. Instead, each row of each column will be joined with each row of the the other tables.

If you had three tables containing abc, 123, and !@#, you would get

a   1   !
a   1   @
a   1   #
a   2   !
...

To properly join, you'd want to do something more like

select customerId
from clients c
join project p on p.customerId = c.id
join items   i on i.customerId = c.id
where customerId = 135;

However, given that you're giving a where clause for the customer ID and also selecting the customer ID, you're going to get back count(p) * count(i) rows, each containing only the customer ID 135.

Upvotes: 0

Kermit
Kermit

Reputation: 34054

Well, there is no join. You need to at the least define an implicit relation between those tables like so:

SELECT customerId 
FROM items, projects, clients 
WHERE clients.customerId = projects.customerId
AND items.customerId = projects.customerId
AND clients.customerId= 135

However, you should use explicit joins like so:

SELECT client.customerId
FROM clients
LEFT JOIN projects ON projects.customerId = clients.customerId
LEFT JOIN items ON items.customerId = clients.customerId
WHERE clients.customerId = 135

Upvotes: 1

Related Questions