Unfortunately no one can be told what FluxBB is - you have to see it for yourself.
You are not logged in. Please login or register.
FluxBB.org Forums » Programming » SQL join question
Just wondering what would be returned from something like the following:
SELECT a.id, b.id FROM a.table AS a LEFT JOIN b.table AS b on b.id=a.id WHERE [rest of query here...]if the first table, a, had only one column which matched the WHERE clause, but table b had, for example, three columns which matched the a columns id, would you have three rows returned or just one?
Ones mind is just failing to grasp basic logic completely again at the moment. 
Cheers.
if the first table, a, had only one column which matched the WHERE clause
You mean row. 
And the query would return 3 rows if the row in A that matched the WHERE clause joined to 3 rows in B. If it didn't, it would return 1.
if the first table, a, had only one column which matched the WHERE clause
You mean row.
I'll blame it on the mind being in complete go slow mode at the moment.
And the query would return 3 rows if the row in A that matched the WHERE clause joined to 3 rows in B. If it didn't, it would return 1.
Cheers. 
Thought that was the case. In that case, is there any way to make it only return the first matching row from table b rather than all three when using joins?
Edit: Would using something like 'SELECT DISTINCT(a.id)' do it?
Last edited by MattF (2008-07-17 03:34:12)
Distinct isn't really the way to go. It'd probably be better to use GROUP BY.
SELECT a.id, b.id FROM a.table AS a LEFT JOIN b.table AS b on b.id=a.id WHERE [rest of query here...] GROUP BY a.id
PgSQL is an absolute dog where GROUP BY is concerned. I think you have to name every column explicitly when you use it with a join, (or something as quirkily annoying). I like PgSQL, but it does have some extreme annoyances on occasion.
I'm beginning to think it might be simpler to just add an extra column to the first table to achieve what I need.
I'm assuming that you have both tables in same database.
I hope this query will work fine.
SELECT a.id, b.id FROM table1 AS a LEFT JOIN table2 AS b on b.id=a.id WHERE [rest of query here...]
I'm assuming that you have both tables in same database.
I hope this query will work fine.
SELECT a.id, b.id FROM table1 AS a LEFT JOIN table2 AS b on b.id=a.id WHERE [rest of query here...]
Sorry, but you have missed the point of the topic.
I do not know why you would use GROUP BY unless you are using something like SUM or COUNT. You said you just need the first row in b that matches a so why not a subquery with limit 1?
SELECT a.id, (SELECT b.id FROM b WHERE b.id = a.id LIMIT 1) FROM a WHERE ...
FluxBB.org Forums » Programming » SQL join question
Powered by FluxBB
[ Generated in 0.017 seconds, 9 queries executed ]