[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Retrieving multiple records from Table - A based on Common-Unique Column in Table-B

Hi,
I have TABLE-A with several records which have duplicate column data and TABLE-B with unique Column Data.

I would like to retrieve all the records from TABLE-A which has matching columns in TABLE-B.
using PL/SQL..

Please help!
Yajesh
0
yajesh
Asked:
yajesh
  • 10
  • 10
  • 2
  • +3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Can you provide more info?  Sample data and expected results would help.

For example, ALL columns or just a few?

Select ta.col1, ta.col2
From tableA ta, tableB tb
Where ta.col1=tb.col1;

Or maybe
select col1, col2 from tabA
Intersect
Select col1, col2 from tabB


0
 
jameso99Commented:
PL/SQL:

select * from TableA join TableA on (TableA.column1 = TableB.column1)

Check out this site here for more info on JOIN:
http://www.techonthenet.com/sql/joins.php
0
 
OP_ZaharinCommented:
use WHERE EXISTS to find match record between 2 table:

SELECT * FROM tblnameA a WHERE EXISTS 
(SELECT * FROM tblnameB b WHERE b.column1 = a.column1)

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
yajeshAuthor Commented:
I think I should have given a specific example before.
SELECT
    c1, c2, c3, c4, c5, c6, c7,
FROM  t1
WHERE c7 IN ('1', '2', '3', '4');

Instead of hard-codingg the values in the brackets, I need to make reference to the actual table which contains these unique values. Note Table t1 has several hundred rows with duplicate values as you see in the brackets. That is normal.

After making the change to reference the table (let us say t2 which contains these unique number) I should still be able extract all the rows from t1 except those that do not match to unique values in Table t2.

yaj
0
 
OP_ZaharinCommented:
- try this:

SELECT
    c1, c2, c3, c4, c5, c6, c7,
FROM  t1
WHERE c7 IN (SELECT t2c7 FROM t2);
0
 
OP_ZaharinCommented:
opss typo. take out the comma after c7 on 2nd line:

SELECT
    c1, c2, c3, c4, c5, c6, c7
FROM  t1
WHERE c7 IN (SELECT t2c7 FROM t2);
0
 
yajeshAuthor Commented:
The script does work except on major issue.
It will only display/select the rows that match the first unique value in t2.c7.

I want to be able to display all the roes that matches all the unique numbers in t2.c7


yaj
0
 
yajeshAuthor Commented:
In my example above, with your synatx, which I did try earlier today also, it will only list rows that match '1' as ahown in my hard coded sample above. I want to be able to loacte rows with '2', '3' etc...

yaj
0
 
SharathData EngineerCommented:
Can you provide some sample data from both the tables and expected result?
0
 
OP_ZaharinCommented:
- can you share an example how the data in table1 and table2 being structure? my sql on ID35518110 and  ID35590393 should return many rows not only the first one. you might need to use trim() function on c7 and t2c7 (if there might be blank spaces exists, but then it will not use the indexing).
0
 
OP_ZaharinCommented:
- also re-check that c7 have the same/match data as in the t2c7. you can do by:
 select distinct(c7) from table1;
 select distinct(t2c7) from table2;
0
 
yajeshAuthor Commented:
T2.c7  VARCHAR(3) NOT NULL

T1.c7  VARCHAR2(3)

I did try TRIM also - same result.
Think about the issue. It only reads ROW1 on T2, hence I see all the rows from T1 which matches. It does not go to next ROW in T2. There are 7 rows in T2 and thiusands in T1. It needs to macth each and evry unique value in T2 and extract corresponding rows ifrom T1 with a single SELECT statement.

yaj
0
 
SharathData EngineerCommented:
Did you try the JOIN statement as slightwv mentioned in the first post?
0
 
OP_ZaharinCommented:
- i do understand what you are trying to achieve and i believe both sql statement that i provided should works unless there is something wrong with your data either in T1.C7 or T2.C7
- if you are willing to go further to check by query another record in T2.C7 by hardcoding the value:

SELECT * from T1 WHERE C7 = hardcodeanothervaluethatexistinT2.C7
0
 
yajeshAuthor Commented:
Yes I certainly did. No luck.

Yaj
0
 
OP_ZaharinCommented:
- can you also do the count test and share with us the result:

- 1st count, hardcoded all the value in t2 into the IN statement:

SELECT
    count(*)
FROM  t1
WHERE c7 IN ('1', '2', '3', '4');

- 2nd count reading from t2 table
SELECT
    count(*)
FROM  t1
WHERE c7 IN (SELECT t2c7 FROM t2);

0
 
yajeshAuthor Commented:
3557 Rows for t1 using the hard coded unique values.

250 Rows for the 2nd count, which is correct because it only picked up rows matching with the first row in the t2. It should have read all of the rows in t2 to pikc up the rest of rows from t1.

yaj
0
 
OP_ZaharinCommented:
- if its possible, can you paste the result of select * from t2 here?
- we need to know how the data being populated in that table because the clause IN (SELECT t2c7 FROM t2) should return the whole column data for C7 in t2.

- so does it display:
C7
1
2
3
4
5

OR
C7 | C8 | C9
1 | 2 | 3
0
 
yajeshAuthor Commented:
SELECT * FROM t2  will output  7-rows
 with additional data in other Col2 and Col3 which is not required in the SELECT statement for t1.

Col_1    Col_2   Col_3 etc....
------    --------   ------------
1
2
3
4
5
6
7

With the xamples you have suggested similar to what I tried earlier today will only display rows with value 1 in Table t1. When I hard code it, it shows all of the respective matches from t1.
 
The intent is not to hard code and make it flexible to make changes to t2 if necessary. i.e. Add more rows with other criterias.

yaj
0
 
yajeshAuthor Commented:
Forgot to qualify - Col_1 is actually labelled C7 in t2.

yaj
0
 
OP_ZaharinCommented:
- since the datatype for C7 in both table is varchar. does it contain 1, 2, 3 or some character? if its varies in uppercase and lowercase, that might effect too.
0
 
yajeshAuthor Commented:
The case is matching in both tables. Everything is in upper case.
The actual data is like 'A50",  'B43', etc... ect.. for both tables for column named c7 in both Tables.

yaj
0
 
yajeshAuthor Commented:
Iit is almost 1:30AM here. We will catch up tomorrow. I can stay up for next 10 mins. or so.

Appreciate all your help.

yaj
0
 
OP_ZaharinCommented:
- yeah lets continue tomorrow. i will still look into it and maybe other expert might have other suggestion into this :)
0
 
slightwv (䄆 Netminder) Commented:
Again, please provide some sample data for both tables and expected results.

Sometimes a picture is better than explaining with text.
0
 
awking00Commented:
It has already been requested a few times but, if you would just post some sample data from the two tables and the expected result, it would be most helpful to both you and the experts in providing a workable solution.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 10
  • 10
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now