Is this possible (with SQL)?

ve

Lets say I have two tables (A, , their structure is as follows

A = A.id, A.value1, A.value2, A.value3

B = B.id, B.value1, B.value2

WHERE A.id = B.value1

Basically when I create a row in table B, B.value1 will always be a value found in A.id

So what I want to do is write a query that SELECTS A.id, A.value1, an alias (found) which returns 1 or 0 depending on whether the A.id can be found in B.value1

E.g.

---------------------------------------
A.id        | A.value1        | found |
---------------------------------------
23          | 'value'         | 0     |
24          | 'another val'   | 1     |
25          | 'value_x'       | 1     |
26          | 'value_z'       | 0     |
---------------------------------------

I'm sure this is possible, but have spent ages reading and trying to produce something that works. I resorted to writing a loop in my software that performs a seperate query for each A.id (returned from SELECT A.id, A.value1 from A) and tbh that's just too many calls to the database and will not scale very well. In my mind the solution would be something like this...

SELECT A.id, A.value1, (SELECT {some conditional query using WHERE EXISTS that returns a BOOL}) AS found FROM A, B WHERE A.id = B.value1

I know that's very rough, but I'm sure some of you SQL experts will be able provide a solution in no time.

Any help appreciated, going out of my mind here. Thanks in advance.

feylya

select a.id, a.value1, b.found from tablea a
left join tableb b on b.value1 = a.id;

kayos

SELECT 
	a.id, 
	a.value1, 
	CASE 
		WHEN b.value1 IS NULL THEN 0
		ELSE 1
	END AS Found
FROM 
	tablea AS a
	LEFT OUTER JOIN tableb AS b ON (b.value1 = a.id)

Seeing as tableb does not have a found column then feylya's query will fail. The above should do the job though.

ve

Fair play kayos - that's exactly it

If I could pick your brain once more, as I've been trying to modify it now to do something extra...

Now I would like to get back just the rows where B.value2 = 'somevalue'

Tried sticking in a where clause after what you put in and instantly got an error.

Really appreciate your efforts mate, thank you for introducing me to CASE, I can see a real use for that in the future

dowlinj

Think this is it

SELECT 
	a.id,  
	b.value2
		
	FROM 
	tablea AS a
	inner JOIN tableb AS b ON (b.value1 = a.id) 
       
        WHERE 
        b.value2 = 'somevalue'

kayos

SELECT 
	a.id, 
	a.value1, 
	CASE 
		WHEN b.value1 IS NULL THEN 0
		ELSE 1
	END AS Found
FROM 
	tablea AS a
	LEFT OUTER JOIN tableb AS b ON (b.value1 = a.id AND
						b.value2 = 'somevalue')

This will still list all entries in tablea and only mark the rows where there is an matching row in tableb that has a value2 of 'somevalue' as found.

So if you have a tableA entry with an ID of 1 and a tableB entry with a value1 of 1 but value2 is ‘someothervalue’ it will be marked as not found.
So if you have a tableA entry with an ID of 2 and a tableB entry with a value1 of 2 but value2 is ‘somevalue’ it will be marked as found.

When using Left outer joins your best sticking any clauses in the join rather than the Where.

ve

Many thanks kayos, I have implemented your query and it works perfectly.

Thanks again