Click here to Skip to main content
15,039,714 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear sir,

i need to generate sub query in select query that fetch all record of my main table that is market table. here is code

Market table
SQL
Mkid   price   discount
1      100     2
2      150     0
3      100     0


Product table
XML
<pre lang="SQL">
Mkid   Name   
1      Electronic     
2      sports     
3      Foods   

</pre>


i need to select all rows of market table and name from product table.
so i use this query
SQL
select price, discount,(select name as 'name' from product where MKid=1) from market


so in above sub query i had pass mkid parameter, is there way to fetch name without using Mkid.


so i need all rows of price discount name without passing parameters or mkid in where clause

please help
Posted
Comments
manognya kota 9-May-12 0:59am
   
Hi,

Did you try executing your sql statement -
select price, discount,(select name as 'name' from product where MKid=1) from market??

Because as per my understanding it gives all the rows in the market table for mkid 1,2,3 with a column having name - Electronic.
Is this what you want?

Try this:
SQL
SELECT price, discount, name FROM market 
LEFT JOIN product ON market.MKid = product.MKid
   
Hi,

As per my understanding , if you are looking for a output like,

SQL
price discount name
100     2       electronics
150     0       sports
100     0       foods


Best way is to join the 2 tables.
Hope this helps.
   
Comments
balongi 9-May-12 1:14am
   
which type of join is best
balongi 9-May-12 1:15am
   
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. error come
Try this

SQL
select m.price, m.discount,p.name from market m inner join product p on m.MKid=p.MKid



[EDIT]

see this
http://stackoverflow.com/questions/9393207/cannot-resolve-the-collation-conflict-between-sql-latin1-general-cp1-ci-as-and[^]
   
v2
Comments
balongi 9-May-12 1:15am
   
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. error come
madhusudhan.k 25-May-12 9:22am
   
use left outer join instead of inner join

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900