How to use JOIN to select data from multiple tables using only one query

[CoolForU]

This is my table

tblName

Pid PName Country
------------------------------------------------------------------
100 Shantosh India
102 Mukesh India
103 Rakesh India


and another table is

tbl item

Tid Pid Item
----------------------------------------------
1 100 scrow
2 100 bolt
3 100 Nutt
4 102 wood
5 102 Nutt


Now i want to result like that

Result by query

Pid PName item
----------------------------------------------------
100 Shantosh scrow
102 Mukesh wood
103 Rakesh Null

[Atli]

Hi, and welcome to TSDN!

You could use JOIN to join your tables. That basicly searches for matching rows using the columns you specify in the ON clause and allows you to select from those rows only.
Specifying a LEFT JOIN will force the query to return all rows from the first table, returning NULL for those columns you selected from the second one and returned no values for that row.

For example:

SELECT n.Pid, n.PName, i.Item
FROM tblName AS n
LEFT JOIN tblItem AS i
  ON n.Pid = i.Pid;
 

P.S. I don't have what I need to test this query atm so please let me know if there are any problems.

[Atli]

I have edited the thread's title to better describe it's contents.
Please read the Posting Guidlines before posting

Moderator

[CoolForU]

hi thanks for reply
i try this but not working.

i want to results like i menssion in my query.

have u got this result.

Thanks

[r035198x]

hi thanks for reply
i try this but not working.

i want to results like i menssion in my query.

have u got this result.

Thanks

What happens when you try that? Does it give an error or does it return the wrong results?

[CoolForU]

What happens when you try that? Does it give an error or does it return the wrong results?

It's not give an error but not give results which i want.
i want to result like i mansion in my thread .

[mwasif]

No one can understand what do you mean by "but not give results which i want.". It is always better to show the result you are getting.

You have 3 records for 'Shantosh' in tblItem. Which result do you want, scrow, bolt or Nutt?