SQL Quary with Where Clause

[PM312]

I have 3 tables in which one column name is common for all i.e PRNO .I want to write SQL query to select everything from Table1 where PRNO is 500, Table2 where PRNO is 500 , Table3 where PRNO is 500 .

“Select * from Tab1,Tab2,Tab3 WHERE PRNO=500”

Getting Error “Ambiguous column name ‘PRNO’ “

[BitBlt]

You can do it a couple of ways. If the tables all have the same columns and datatypes, you could do it with a UNION operator, like so:

SELECT * from Tab1 where PRNO = 500
UNION ALL
SELECT * from Tab2 where PRNO = 500
UNION ALL
SELECT * from Tab3 where PRNO = 500

The UNION ALL just says, bring back all the rows regardless of duplicates.

If the tables have parent/child relationships, you might consider using JOIN syntax. If you are sure that every table will return rows, use INNER JOIN. Note that the WHERE clause should be qualified to point to the column in the "parent" table, whichever that one is.

SELECT * 
from Tab1
INNER JOIN Tab2
on Tab1.PRNO = Tab2.PRNO
INNER JOIN Tab3
on Tab1.PRNO = Tab3.PRNO
where Tab1.PRNO = 500

If you think some of the tables might NOT have rows, use LEFT JOIN.

SELECT * 
from Tab1
LEFT JOIN Tab2
on Tab1.PRNO = Tab2.PRNO
LEFT JOIN Tab3
on Tab1.PRNO = Tab3.PRNO
where Tab1.PRNO = 500

There are other permutations, but this should cover the basics. Hope it helps! Good luck!

[PM312]

option 3 was most favarouble as there is possibelity of not returning rows from tableTbl_Inv_Details. But getting error as in attached word file. (May i know how to paste screen shot)

Below is my actual code. 3 tables names are

TBL_PR
Tbl_Pr_Bank_Details
tableTbl_Inv_Details

'

Dim CnView As ADODB.Connection
Dim RSView As ADODB.Recordset

Dim ViewSQL As String

Set CnView = New ADODB.Connection
CnView.Provider = DataSource1
CnView.Open

Set RSView = New ADODB.Recordset

ViewSQL = "select * from TBL_PR LEFT JOIN Tbl_Pr_Bank_Details ON TBL_PR.PRNO = Tbl_Pr_Bank_Details.PRNO LEFT JOIN Tbl_Inv_Details on TBL_PR.PRNO WHERE TBL_PR.PRNO = 752 "

RSView.Open ViewSQL, CnView, adOpenDynamic, adLockOptimistic

[BitBlt]

Your join syntax seems to be incomplete. Here is the (apparently) proper SQL statement:

select * 
from TBL_PR 
LEFT JOIN Tbl_Pr_Bank_Details 
ON TBL_PR.PRNO = Tbl_Pr_Bank_Details.PRNO 
LEFT JOIN Tbl_Inv_Details 
on TBL_PR.PRNO = Tbl_Inv_Details.PRNO
WHERE TBL_PR.PRNO = 752

Note that every join has to be from one table/column to another table/column. So, pay special attention to line 6... your statement missed the part about joining the columns.

[PM312]

thanks