Getting stored procedure to return rows that meet condition in one table and match another

table A has rows

ID-name-question
----------
20-fred-no
30-john-yes
25-bert-no

table B has rows

ID2-value-ID.match.table.1
--------------------
114-12132-20
115-22101-25

(a record in table A will either have "yes" in the question column or a matching row in table B)

how can I get a stored procedure to return all the rows in table A that either have "yes" as a value in the question" column or a matching row in table B?

points assigned commensurate with speed of response needed


thanks!
JCMcNeilAsked:
Who is Participating?
 
ignacioperezConnect With a Mentor Commented:
I believe what you need is something like this:

Select * from
A left join B  
ON (A.ID = B.IDMATCHTABLE1 AND <rest of matching criteria>)
Where
A.question = 'yes'
or
B.ID is not Null

This way, the left join will bring all of the records from A (matching or not with B) then, with the where clause you choose from there those with 'yes' in question and those with actual matches in B.
0
 
JCMcNeilAuthor Commented:
by the way, if a row in table a has a "yes" in column 3, it will not match a row in table B
0
 
puranik_pCommented:
CREATE PROCEDURE RETURNROWS
AS
SELECT A.*
FROM A, B
WHERE
(
  (A.ID = B.IDMATCHTABLE1)
  OR
  (A.QUESTION = 'YES')
)
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.

 
puranik_pCommented:
> points assigned commensurate with speed of response needed

i suppose, you are increasing the points first and then giving the A grade.
right?
;o)
0
 
JCMcNeilAuthor Commented:
oops, it also has to match 2 values (which is where I ran into trouble) . . .

@ID  and @(another unmentioned column)

this is where it complicated things . . . .
0
 
puranik_pCommented:
> it also has to match 2 values (which is where I ran into >trouble) . . .
>
>@ID  and @(another unmentioned column)
please explain.
0
 
puranik_pCommented:
In case of more where clauses, just add them like...
CREATE PROCEDURE RETURNROWS
AS
SELECT A.*
FROM A, B
WHERE
(
 (A.ID = B.IDMATCHTABLE1)
 OR
 (A.QUESTION = 'YES')
)
AND
(
  FIRSTFIELD = 'somevalue'
  AND
  SECONDFIELD = 'anothervalue'
)

I'm leaving now for the weekend :o)

all the best!
0
 
supunrCommented:
SELECT A.*, B.*
FROM A INNER JOIN B ON A.id = B.id
WHERE (((A.question)="yes"));
0
 
JCMcNeilAuthor Commented:
Still required a bit of modification but this logic got me there- thanks~!
0
All Courses

From novice to tech pro — start learning today.