CODEWITHSUNDEEP
mysqlsql
user2170090
user2170090

Reputation: 49

SQL querying multiple tables for information

I have a little dilemma in joining data from 4 tables in one SQL query, I am using MySQL for the DB part and would appriciate any help you can give me.

Here is the task...

I have for tables with columns and data

Sale            Items        Owner        Salesman
--------------  -----------  -----------  --------------
*Salesman_id    Item_type    *Owner_id    *Salesman_id
*Owner_id       Item_color   Owner_name   Salesman_name
Buyer_id        *Owner_id
Price

I want to query these tables on the columns I have marked with emphases text. So I can get result like

Item type, Item color, Owner name, Salesman name, Salesman number.

I have gone through a countless number of iteration trying to achieve this both with JOIN and nested queries without sufficient result.

Upvotes: 0

Views: 64

Answers (3)

mohsenJsh
mohsenJsh

Reputation: 2108

try this

SELECT Item_type, Item_color, Owner_name, Salesman_name, Sale.Salesman_id FROM Items
  INNER JOIN Owner USING(Owner_id)
  INNER JOIN Sale USING(Owner_id)
  INNER JOIN Salesman ON Salesman.Salesman_id=Sale.Salesman_id

why doesn't the Items table have a primary key?

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

If there is a one-to-one relation you can use inner join 

SELECT i.Item_type , i.Item_color ,o.Owner_name,sm.Salesman_name,sm.Salesman_id
FROM Salesman sm 
INNER JOIN Sale s ON (s.Salesman_id = sm.Salesman_id )
INNER JOIN Owner o ON (s.Owner_id=o.Owner_id)
INNER JOIN Items i ON (i.Owner_id=o.Owner_id)

If there is one -to- many try with Left join

Upvotes: 1

ʰᵈˑ
ʰᵈˑ

Reputation: 11365

A solution when we're not joining, and you want it them to just display their values, you can do something like (I know this doesn't directly answer OP's question, but I'm getting there...)

   SELECT sale.`salesman_id`,sale.`owner_id`,
          items.`order_id`,
          owner.`owner_id`,
          salesman.`salesman_id`
   FROM `Sale` sale,
        `Items` items,
        `Owner` owner,
        `Salesman` salesman

And that should return everything.

However, your question states that we are joining. Could you put some data into something like SQLFiddle so I have some visual representation? and a brief summary of what you're trying to accomplish - as in where you want the joins?

Upvotes: 0

Related Questions