query

[sinner]

hi,
i faced a problem when i tried to retrive value from two table using a common value.
ie
table1 has emp_id and name,table2 has emp_id and prod..
i tried to use the query
select name,prod from table1,table2 where table1.emp_id=' some value';
The problem was there was redundancy of data in table2 and using distinct key word also i got unwanted values.
ie.
select distinct name,prod from table1,table2 where table1.emp_id=' some value';
i got all the values of prod.
what kind of query i should use to retrive value from both the table to avoid redundant and unwanted data.

[amitpatel66]

Try this:

[code=oracle]

SELECT t1.empid,t1.nam e,t2.prod FROM table1 t1, table2 t2 WHERE t1.empid = t2.empid

[/code]

[debasisdas]

To avoid redundancy of data in JOIN, you need to qualify the field names by the table name.

[sinner]

Thanks for the query its work fine.
i faced a problem when i tried to know the number of products sold by a particular emp_id
ie
select t1.name,count(p rod) from t1,t2 group t2.id;

The error was not a GROUP BY expression.

[sinner]

Thanks for the query its work fine.
i faced a problem when i tried to know the number of products sold by a particular emp_id
ie
select t1.name,count(p rod) from t1,t2 group t2.id;

The error was not a GROUP BY expression.
what can i do for retriving name and product count from the table.

[amitpatel66]

Thanks for the query its work fine.
i faced a problem when i tried to know the number of products sold by a particular emp_id
ie
select t1.name,count(p rod) from t1,t2 group t2.id;

The error was not a GROUP BY expression.
what can i do for retriving name and product count from the table.

Try This:

[code=oracle]

SELECT t1.name,x.cnt Prod_Count FROM
(SELECT t2.id, COUNT(t2.prod) cnt FROM table2 t2 GROUP BY t2.id) x, table1 t1 WHERE t1.id = x.id

[/code]

[sinner]

Try This:

[code=oracle]

SELECT t1.name,x.cnt Prod_Count FROM
(SELECT t2.id, COUNT(t2.prod) cnt FROM table2 t2 GROUP BY t2.id) x, table1 t1 WHERE t1.id = x.id

[/code]

Thanks amitpatel for the code.It is working fine.

[amitpatel66]

Thanks amitpatel for the code.It is working fine.

You are Welcome. Do post back in case of any further issues

[sinner]

You are Welcome. Do post back in case of any further issues

hi,
i used a query to retrive the top product sold by a particular user by the following

query:
select tab1.name,x.cnt post from (SELECT tab2.id,count(t ab2.post) cnt from tab2 group by tab2.id ) x,tab1 where tab1.id = x.id order by post desc;

it works fine.
can i get get more feesable query to retrive the value.

[amitpatel66]

hi,
i used a query to retrive the top product sold by a particular user by the following

query:
select tab1.name,x.cnt post from (SELECT tab2.id,count(t ab2.post) cnt from tab2 group by tab2.id ) x,tab1 where tab1.id = x.id order by post desc;

it works fine.
can i get get more feesable query to retrive the value.

There are many ways of doing it. What you tried is also fine and simple.

[sinner]

There are many ways of doing it. What you tried is also fine and simple.

thank amit .
can you tell me the otherways so that i could get the knowledge of that too.

[amitpatel66]

thank amit .
can you tell me the otherways so that i could get the knowledge of that too.

[code=oracle]

select tab1.name,tab2. id,(SELECT count(*) from tab2 WHERE id = tab2.id) post FROM table1 tab1,table2 tab2 where tab1.id = tab2.id order by post desc;

select tab1.name, x.id,x.cnt FROM
(SELECT id,ROW_NUMBER(P ARTITION BY id ORDER BY COUNT(prod) DESC) cnt FROM table2 GROUP BY id) x, tanle1 tab1
WHERE tab1.id = x.id

[/code]