CODEWITHSUNDEEP
mysqljoin
Awais Qarni
Awais Qarni

Reputation: 18016

how to query through this tables structure

Hello I have a products table having fields

     products_id,products_number,products_status,products_price

where products_id is auto_increment and PRIMARY_KEY.

I have other table products_description having fields

 products_id,language_id,products_description,products_name

Where products_id and language_id or composite primary key. Remember products_id is the same as in products table. Now in products_description table there are more than one record against one products_id with respect to different language_id.

My Problem

I have to get all columns from products table and products_name column from products_description table. I have applied simple join query like this

SELECT p.products_id,p.products_model,p.products_price,p.products_status,pd.products_name FROM products p, products_description pd WHERE p.products_id=pd.products_id

but I know this query is wrong as it is returning more than one rows against one product if it is having more than one record in products_description table.

What I Want

I want to fetch one record from products_description against each products_id of productstable table if nolanguage_idis given in query. I know that it can be done if I applylanguage_id` condition in query. But is there any way to get the records

Upvotes: 0

Views: 56

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29639

select p.*, 
       pd.products_name
from products   p,
      products_description pd
where p.products_id = pd.products_id
and   pd.language_id = 
        (select min(language_id)
          from products_description pd2
          where pd.products_id = pd2.products_id)

should do the trick.

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Assumes that any description returned will be fine. uses a correlated sub-query on products_description to get the min value language ID for a related products_ID

select p.products_id, p.products_number,p. products_status,p. products_price
       pd.language_id, pd.products_description, pd.products_name
from   products p, 
       products_description pd,
       (Select min(Language_ID) mLID 
        from products_Description ipd 
        where PD.Products_ID=ipd.products_ID 
          and ipd.language_ID = pd.language_ID) A
where  p.products_id  = pd.products_id

Upvotes: 1

Related Questions