How to make table 1 join everything in table 2 and select as one group

Databases
1

Hi guys,
I’ve two tables as below:

TableA
id		name
1		John
2		Jane
3		Joe

TableB
id		uid		file			bg		avatar
1		2		path_to_photo	0		0
2		2		path_to_photo	1		0
3		2		path_to_photo	0		1
4		1		path_to_photo	1		1

Assumed I picked Jane from TableA, I want to display the image which she set as background and which one she set as avatar altogether like in facebook profile.

How do I do that.

Note that all is set to display in single block. Can I do it in one query.

Thank you,

2

Here’s my try, it isn’t working.

SELECT
	TableA.id, TableA.name, TableB.uid, TableB.file, TableB.bg, TableB.avatar
FROM
	TableA
RIGHT JOIN
	TableB
ON
	TableA.id = TableB.uid AND (TableB.bg = '1' OR TableB.avatar ='1')
WHERE
	TableA.id = :user_id
GROUP BY TableA.id
3

I don’t understand your query… or maybe I didn’t understand your question. Definitvely, your try is not answering the problem you proposed.

I aslo don’t understand why you need an outer join in this case.

SELECT a.id, a.name, b.bg, b.avatar
FROM TableA as a INNER JOIN TableB as b ON a.id=b.uid
WHERE a.id=2;

You could change the where condition to WHERE a.name=‘Jane’ but you would have more than one row if there’s more than one Jane.

I added the fields id and name to the SELECT clause only so you could visually see that the query works

1 Like
4

molona, you also need file in the SELECT, otherwise he won’t be able to “display the image which she set as background and which one she set as avatar”

plus, you have to filter out the pics that aren’t either background or avatar

SELECT a.id , a.name , b.file , b.bg , b.avatar FROM TableA as a INNER JOIN TableB as b ON b.uid = a.id AND 1 IN ( b.bg , b.avatar ) WHERE a.id = 2

5

You’re right. Forgot to add that particular field. Thanks @r937

SELECT a.id, a.name, b.flie, b.bg, b.avatar
FROM TableA as a INNER JOIN TableB as b ON a.id=b.uid
WHERE a.id=2;
6

[quote=“molona, post:5, topic:112238, full:true”]
You’re right. Forgot to add that particular field. Thanks @r937[/quote]

you forgot the filter!

AND 1 IN ( b.bg , b.avatar )

7

Thanks @r937

It works.

This is invaluable answer. I never knew it can be queried like this one.

However, it fetches two files like: path_to_file1path_to_file2

I have to separate it in two.

8

Hi @molona

I just tried everything I found on the internet, mostly on stackoverflow which thread the OP set as correct answer.

None of them works.

9

they are actually on separate rows, with the same column name (not “1” and “2”)

what’s your application language, php?

10

Yes,

It’s PHP and I use PDO method.

How to separate them without using substr() function.

Thanks,

11

[quote=“ketting00, post:10, topic:112238, full:true”]How to separate them without using substr() function.
[/quote]

um, they are not together, so don’t need separating

they are on separate rows

you need to test the bg and avatar columns in the query results to find out which kind of pic it is

12

OK, I see.

They are two rows.

13

I actually didn’t. I did it on purpose :smile:

I guess I should have added it because he did in his query… but his question didn’t say anything about getting a result only when one or the other where greater than 0… so I decided to be a little bit lazy and that he could add it himself, if he really wanted :smiley:

14

yes, it did – take another look :slight_smile:

" I want to display the image which she set as background and which one she set as avatar "

15

well,

I did say I want something like this:

1099×384 511 KB

And here’s what I tried:

1366×377 219 KB

There’s a bit to do with CSS though.

Thank you all for your help

1 Like
16

Either I need to learn how to read or I should visit my optician straight away lol

I didn’t even see it

17

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.