CODEWITHSUNDEEP
mysqlsqldatabase
Ayush Bansal
Ayush Bansal

Reputation: 65

Combine 2 or more tables in sql

products table

products
productid | sellerid | productprice

sameproduct table

sameproduct
productid | sellerid | productprice

sellers table

sellers
sellerid | sellername | selleraddress

The sellerid is foreign key to both the tables products and sameproduct. I want to show the seller details for a particular productid which may be present in both tables products and sameproduct.

Upvotes: 0

Views: 104

Answers (7)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

Use MySQL to join three tables called products, sameproduct and sellers:

select s.*
from products p 
inner join sameproduct ps on p.sellerid = ps.sellerid
inner join sellers s on ps.sellerid = s.sellerid 
where productid =in_product_id

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

You want to show the seller for a product, so you'd select from the sellers table (no join). You get the seller IDs for the product in an IN clause (or an EXISTS clause if you prefer this).

select *
from sellers
where sellerid in (select sellerid from products where productid = 12345)
   or sellerid in (select sellerid from sameproduct where productid = 12345);

or

select *
from sellers
where sellerid in (select sellerid from products where productid = 12345
                   union all 
                   select sellerid from sameproduct where productid = 12345);

It may be preferrable to make products and sameproduct one table, though.

Upvotes: 0

RRajani
RRajani

Reputation: 409

This Might Help You.

SELECT * 
FROM sellers 
     INNER JOIN sameproduct ON sellers.sellerid = sameproduct.sellerid  
     INNER JOIN products ON sellers.sellerid = products.sellerid

Upvotes: 3

Jasper Risseeuw
Jasper Risseeuw

Reputation: 1256

I guess this is what you need? You left join the sellers table to both tables and select only the records where the productid is found in one of the two product tables.

declare @productid int
set @productid = 1

select s.*, p.productid, sp.productid
  from sellers s
    left join product p
      on s.sellerid = p.sellerid
    left join sameproduct sp
      on s.sellerid = sp.sellerid
  where p.productid = @productid 
      or sp.productid = @productid

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can JOIN products and sellers tables to get the desired output, e.g.:

SELECT s.sellerid, s.sellername, s.selleraddress
FROM sellers JOIN products p ON s.sellerid = p.sellerid
WHERE p.productid = ?

Please note that it will return multiple rows if a product has more than one seller.

Upvotes: 0

Roman Tkachuk
Roman Tkachuk

Reputation: 3266

You can UNION both tables, and after that do something:

SELECT *
  FROM (
        SELECT * FROM products 
        UNION ALL SELECT * FROM sameproduct
       ) AS p
  JOIN sellers AS s ON (s.sellerid = p.sellerid)
 WHERE productid = @productid

Upvotes: 0

AlbertoCh
AlbertoCh

Reputation: 120

Try something like:

select sellers.* from products
inner join sellers on products.sellerid =sellers.sellerid
where productid=@productid
UNION
select sellers.* from sameproduct
inner join sellers on sameproduct.sellerid =sellers.sellerid
where productid=@productid

Upvotes: 0

Related Questions